Oracle: sequence MySequence.currval is not yet defined in this session

oracle sequence
Doug picture Doug · Apr 30, 2009 · Viewed 81.3k times · Source

What does this mean, and how can I get around it?

SELECT MySequence.CURRVAL FROM DUAL;

Result:

ORA-08002: sequence MySequence.CURRVAL is not yet defined in this session

Answer

Tony Andrews picture Tony Andrews · May 1, 2009

mysequence.CURRVAL returns the latest value that was obtained from sequence mysequence in your session, and hence isn't defined until you have obtained a value using mysequence.NEXTVAL at least once in the session. The purpose of CURRVAL is to let you use the sequence value more than once in your code e.g.

insert into parent (parent_id, ...) values (mysequence.NEXTVAL, ...);

insert into child (parent_id, ...) values (mysequence.CURRVAL, ...);

If CURRVAL just returned the last value obtained from the sequence by any session, then it would be useless in the above code, and in fact could lead to data corruption!

Related questions

How do I reset a sequence in Oracle?

In PostgreSQL, I can do something like this: ALTER SEQUENCE serial RESTART WITH 0; Is there an Oracle equivalent?

Read More
How to retrieve the current value of an oracle sequence without increment it?

Is there an SQL instruction to retrieve the value of a sequence that does not increment it. Thanks. EDIT AND CONCLUSION As stated by Justin Cave It's not useful to try to "save" sequence number so select a_seq.nextval …

Read More
How to create an Oracle sequence starting with max value from a table?

Trying to create a sequence in Oracle that starts with the max value from a specific table. Why does this not work? CREATE SEQUENCE transaction_sequence MINVALUE 0 START WITH (SELECT MAX(trans_seq_no) FROM TRANSACTION_LOG) INCREMENT BY 1 CACHE 20;

Read More