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

So as the sequence was created with NOCACHE option, user_sequences.last_number is returning what nextval would have returned.

Hope you have enjoyed is article and it was beneficial for you. :)


Related Posts:
- Sequence Behavior with Multitable Insert All
- Auto Increment Column Performance Enhancement with each Oracle Version
- Setting Sequence Value to a Specific Number
- Oracle Auto Increment Column - Sequence as Default Value
- Alpha Numeric Counter Or Sequence

- One Time Immediate Job In Oracle