ORA-08002: sequence MYSEQUENCE.CURRVAL is not yet defined in this session

ORA-08002: sequence MYSEQUENCE.CURRVAL is not yet defined in this session error occurs If you call the CURRVAL of a sequence that doesn’t have a last value. The NEXTVAL of the sequence will be used to set the sequence’s last value. The sequence CURRVAL returns the sequence’s last value. It throws an error if the sequence’s last value isn’t available. The sequence NEXTVAL creates and sets the sequence’s last value. The last value will not be available if the CURRVAL is called before the sequence’s NEXTVAL. As a result, an error ORA-08002: sequence MYSEQUENCE.CURRVAL is not yet defined in this session will be thrown.

CURRVAL returns the last sequence value requested by the current session. MYSEQ.NEXTVAL was not executed in the current session, and there is no sequence last value available in the session. Oracle throws the ORA-08002 error if the last value is not available when the CURRVAL is called.

There is no last value in the newly created sequence. The sequence has yet to be defined using the last value in this session. If the next value in the sequence is invoked, the sequence last value will be generated, and the last value of the sequence will exist. To retrieve the last value, call the sequence’s NEXTVAL in the newly created sequence before calling the sequence’s CURRVAL.



The Problem

The next sequence value will be returned by the sequence NEXTVAL command, and the next sequence value will be saved as the sequence’s last value. If the sequence’s CURRVAL is called, the sequence’s last value is returned. The sequence could not find the last value if the last value was not available in the sequence when the CURRVAL was called. The error “ORA-08002: sequence MYSEQUENCE.CURRVAL is not yet defined in this session” will be thrown by Oracle.

create sequence mysequence start with 1 increment by 1 nocycle;

select mysequence.currval from dual;

Error

In the example above, a sequence called mysequence is created with a starting value of 1 and is increased by 1. The CURRVAL is called immediately after the sequence is created. The last value of the sequence is not set in the sequence. Oracle will throw an error ORA-08002: sequence MYSEQUENCE.CURRVAL is not yet defined in this session if the CURRVAL is called.

ORA-08002: sequence MYSEQUENCE.CURRVAL is not yet defined in this session
08002. 00000 -  "sequence %s.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


Solution 1

In the newly created sequence, the sequence’s NEXTVAL should be used. The NEXTVAL command identifies the sequence’s next possible value and sets it as the sequence’s last value. If you execute the CURRVAL command after that, the database will return the sequence’s last value. If NEXTVAL is called before the CURRVAL in the sequence, the error “ORA-08002: sequence MYSEQUENCE. CURRVAL is not yet defined in this session” will be resolved.

create sequence mysequence start with 1 increment by 1 nocycle;

select mysequence.nextval from dual;

select mysequence.currval from dual;

Output

select mysequence.nextval from dual; --> returns 1

select mysequence.currval from dual; --> returns 1


Solution 2

The error “ORA-08002: sequence MYSEQUENCE.CURRVAL is not yet defined in this session” occurs when CURRVAL is called without calling the NEXTVAL of the sequence. The query below returns information on the database sequence, including its name, schema, last value, minimum value, maximum value and etc. You may find the error in the database sequence and make the required changes. You may need to have the proper permissions to run the query below. If you don’t have adequate permission to run this query, contact your database administrator.

SELECT * FROM all_objects WHERE object_name = 'MYSEQUENCE';



Leave a Reply