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

Question

The title kind of says it all. 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

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

Answer 2

Doug,

The real question is why you need the currval when you haven't used a nextval in your session? You can look at the column LAST_NUMBER of the USER/ALL/DBA_SEQUENCES view, but think of concurrency issues when you start to use that.

Regards, Rob.

Answer 3

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 4

use this

select sequence_name, to_char(min_value) min_value, to_char(max_value) max_value, increment_by, cycle_flag, order_flag, cache_size, to_char(Last_number) last_number from user_sequences where sequence_name='MYSEQUENCE'

HainKurt...

Answer 5

Thanks Kurt, it worked!