ORA-08002 means a statement read a sequence's CURRVAL before the current session called NEXTVAL on it. CURRVAL is not the sequence's latest value in the database: it is the value this session got from its own last NEXTVAL, so a new session has none. The fix is to call NEXTVAL first, or to get the generated value another way, such as RETURNING INTO.
Reproduce the Error
Example:
create sequence demo_seq; select demo_seq.currval from dual;
Output:
Sequence DEMO_SEQ created. Error starting at line : 2 In command - select demo_seq.currval from dual Error at Command Line : 2 Column : 8 Error report - SQL Error: ORA-08002: Sequence DEMO_SEQ.CURRVAL is not yet defined in this session.
Fix: Call NEXTVAL First
Example:
select demo_seq.nextval from dual; -- NEXTVAL first, in this session select demo_seq.currval from dual; -- now CURRVAL returns the same value
Output:
NEXTVAL
__________
1
CURRVAL
__________
1After NEXTVAL, CURRVAL returns the same number for the rest of the session, until the next NEXTVAL. A typical use is inserting a parent row with NEXTVAL and its child rows with CURRVAL.
Common Causes
- A new connection, for example from a connection pool, reads CURRVAL that another session's NEXTVAL produced.
- The value was generated by an identity column or a column default, and the code then reads CURRVAL of an unrelated sequence.
- Code meant to look at the sequence's position; use USER_SEQUENCES.LAST_NUMBER for that, not CURRVAL.
Things to Know
- INSERT ... RETURNING id INTO a variable gives the generated key without CURRVAL, and works for identity columns too.
- Sequence values are not rolled back, so gaps are normal.
Related Guides
Conclusion
ORA-08002 means the session has not called NEXTVAL on the sequence yet. Call NEXTVAL before CURRVAL in the same session, or capture generated keys with RETURNING INTO.
