Geeks With Blogs
Jeff Krebsbach

Reminder to myself -

SQL Server has nice index columns directly tied to their tables.

Oracle has sequences that are islands to themselves.

select seq_name.currval from dual;

select seq_name.nextval from dual;

currval - return current number at top of sequence

nextval - increment sequence by 1, return new number

 

therefore - to create functionality in oracle similar to an index column -

OPTION A) - Create insert trigger:

CREATE OR REPLACE TRIGGER dept_bir 
BEFORE INSERT ON departments 
FOR EACH ROW
WHEN (new.id IS NULL)
BEGIN
  SELECT dept_seq.NEXTVAL
  INTO   :new.id
  FROM   dual;
END;
This will handle creating a unique identity, but will not necessarily inform process
flow of identity without additional logic.

OPTION B) - Select indentity into temp variable, insert whole item into tab


****

When attemptint to query currval, the below error was being thrown -

SELECT seq_name.currval from dual;

ERROR : TABLE OR VIEW DOES NOT EXIST
*** 

Although Oracle sys tables may have access to the sequences, that isn't to say the Oracle user may have access to those sequences - verify permissions when the system can't see object that are being reported in the object explorer.

Posted on Wednesday, April 7, 2010 8:48 PM | Back to top


Comments on this post: Oracle Sequences

No comments posted yet.
Your comment:
 (will show your gravatar)


Copyright © jkrebsbach | Powered by: GeeksWithBlogs.net