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

Congruent

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!

Gerrald

That sounds like an excuse. If you could be sure no other sessions were using the sequence - in theory you can't but in practice you can - then it would be extremely useful as it would be if you wanted to know what the last value returned in any session was - though that isn't a particularly common situation it happens to be the one I was in when I stumbled upon this answer. Going to have to stick to using a variable to store the sequence value, I guess. – Cesta

It is more than an excuse! A currval that was global would be useless unless it could be guaranteed to stay true, which implies serialisation of transactions, which leads to lack of scalability, which is one of the main advantages of sequences. You really do not want currval to work like that! – Gerrald

That's kind of confusing, because isn't nextval so guaranteed? Why can't currval work like nextval? – Cesta

You'd need to think about and understand concurrency in a multi-user system to understand why what you want isn't realistic, or even particularly useful. If you got a "global" currval now, and a millisecond later another session gets the nextval, what use is your "global" currval now? Perhaps you should ask a separate question about how to achieve your requirements, since currval isn't going to change! – Gerrald

It's OK. I have already discovered I need to write extra code to store the value for the last inserted row in a separate variable. I am just asking because it seems odd Oracle would assume everyone is always operating in HA MU environments where this value is meaningless. – Cesta

Answer 2

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

Congruent

Answer 3

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'

Woolworth

Great! I just don't understand why you convert 'Last_number' to char. – Lectureship

Answer 4

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

Customs

Answer 5

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.

Sedate

Since some developers were not using the sequence when inserting rows, the sequence had fallen out of sync. I was trying to get the current value so that I could determine the right increment for an ALTER SEQUENCE. Once I realized I needed a NEXTVAL first, I just used NEXTVAL instead, as it also suited my purposes. – Congruent

Answer 6

I would get around it with

select last_number from all_sequences where sequence_name = 'MySequence';

Hype