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

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!


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


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'

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.


select * from user_sequences     where sequence_name='SEQ_V_WORKORDER_RECNO';

in the above query SEQ_V_WORKORDER_RECNO my sequence name replace it by your sequence name