- 10 Nov, 2021
- 0 Comments
- 2 Mins Read
ORA-08002: sequence string.CURRVAL is not yet defined in this session
ORA-08002: sequence string.CURRVAL is not yet defined in this session
Cause: sequence CURRVAL has been selected before sequence NEXTVAL
Action: select NEXTVAL from the sequence before selecting CURRVAL
ORA-08002 occurs when you try to get CURRVAL of a sequence, before requesting its NEXTVAL in the session. ORA-08002 can be reproduced as following:
SQL> create sequence myseq start with 1 increment by 1 nocycle;
Sequence created.
SQL> select myseq.currval from dual;
select myseq.currval from dual
*
ERROR at line 1:
ORA-08002: sequence MYSEQ.CURRVAL is not yet defined in this session
Explaination of ORA-08002: As per definition CURRVAL returns sequence last value requested by the "current session". ORA-08002 here means that the current session never issued MYSEQ.NEXTVAL, so there is no last value of the sequence in the session.
The exception of ORA-08002 can not be explained completely without explaining 2 main aspects of sequences
1) NEXTVAL, CURRVAL and SESSION
2) user_sequences.last_number and sequence cache
1) NEXTVAL and CURRVAL and SESSION
To understand NEXTVAL and CURRVAL of sequences and SESSION, lets play with a sequence in two session. The steps we are going to follow here is
a) Create a sequence
b) In Session 1 generate sequence.nextval
c) In Session 2 generate sequence.nextval 1000 times
d) Check the sequence.CURRVAL in session one.
SESSION 1:
SQL> create sequence myseq start with 1 increment by 1 nocycle
Sequence created.
SQL> select myseq.nextval from dual;
NEXTVAL
----------
1
SESSION 2:
SQL> declare x number;
2 begin
3 for i in 1..1000 loop
4 select myseq.nextval into x from dual;
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> select myseq.currval from dual;
CURRVAL
----------
1001
SESSION 1:
SQL> select myseq.currval from dual;
CURRVAL
----------
1
So it became obviously, that the 'currval' is stored in the session's pga/uga memory and not in the data dictionary. That is the reason that in Session 1 myseq.currval is still 1 however in session 2 myseq was moved 1000 times.
2) USER_SEQUENCES.LAST_NUMBER AND SEQUENCE CACHE
We can use user_sequences.last_number to get what nextval would have returned, only if the sequence was created with NOCACHE. As in following example NEXTVAL is 1001 but user_sequences.LAST_NUMBER is 1021 because the sequence was not created with NOCACHE option. The value in user_sequences.last_number jumps by the cache size and is usually not what is for NEXTVAL.
SQL> create sequence myseq start with 1 increment by 1 nocycle;
Sequence created.
SQL> declare x number;
2 begin
3 for i in 1..1000 loop
4 select myseq.nextval into x from dual;
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> select myseq.nextval from dual;
NEXTVAL
----------
1001
SQL> select last_number
2 from user_sequences
3 where sequence_name = 'MYSEQ';
LAST_NUMBER
-----------
1021
Lets try the same example of above with the sequence with NOCACHE.
SQL> drop sequence myseq;
Sequence dropped.
SQL> create sequence myseq start with 1 increment by 1 nocache nocycle;
Sequence created.
SQL> declare x number;
2 begin
3 for i in 1..1000 loop
4 select myseq.nextval into x from dual;
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> select myseq.nextval from dual;
NEXTVAL
----------
1001
SQL> select last_number
2 from user_sequences
3 where sequence_name = 'MYSEQ';
LAST_NUMBER
-----------
1002