- 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:
1 2 3 4 5 6 7 8 | 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:
1 2 3 4 5 6 7 | SQL> create sequence myseq start with 1 increment by 1 nocycle Sequence created. SQL> select myseq.nextval from dual; NEXTVAL ---------- 1 |
SESSION 2:
1 2 3 4 5 6 7 8 9 10 11 12 13 | 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:
1 2 3 4 | 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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | 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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | 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 |