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

Question

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 1

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!

Answer 2

It turns out that you can't use CURRVAL until you have used NEXTVAL at least once in your session.