ORA-08002
ORA-08002 means that you don't have any sequence value cached in your session, so you can't check the current value (CURRVAL) of the sequence object. Let's see an example of using a sequence.
SQL> conn sh/sh
Connected.
SQL> create sequence cust_no;
Sequence created.
SQL> select cust_no.currval from dual;
select cust_no.currval from dual
*
ERROR at line 1:
ORA-08002: sequence CUST_NO.CURRVAL is not yet defined in this session
As you can see, the newly created sequence did not correctly respond our request. This is because not only it's a new sequence, but also no sequence value is cached in this session.
Solution
You have to initialize or make the pointer move by using the pseudocolumn NEXTVAL of the sequence, then your session will cache the sequence value CURRVAL for you.
SQL> select cust_no.nextval from dual;
NEXTVAL
----------
1
SQL> select cust_no.currval from dual;
CURRVAL
----------
1
The problem is solved.
Sometimes, NEXTVAL of a sequence may be abused by someone or applications, therefore, CURRVAL could be out of our expectations. there're some ways to reset the sequence to the exact number we need.