Blog

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. 🙂

Follow me

Contact us for Training/ Job Support

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.