Select sequence. currval reported error ORA-08002: sequence XXXX is not defined in this process, sequencecurrval

Select sequence. currval reported error ORA-08002: sequence XXXX is not defined in this process, sequencecurrval

Scenario Description:

1. Create a table named S_DEPART

-- Create tablecreate table S_DEPART(  departid    INTEGER not null,  departname  NVARCHAR2(40) not null,  departorder INTEGER default 0)

2. Create a sequence named S_DEPART_S

CREATE SEQUENCE S_DEPART_S MINVALUE 1MAXVALUE 9999999999999999999999START WITH 1INCREMENT BY 1NOCACHE;

3. Insert a record to the table below

insert into S_Depart(departid,Departname,Departorder)VALUES(S_DEPART_S.CURRVAL,'12345',1);

The following error occurs:

Error ORA-08002: sequence XXXX not defined in this process

Error analysis: The CURRVAL of the sequence must be initialized with NEXTVAL, and then CURRVAL can be used.

Solution: First initialize with NEXTVAL:

<pre name="code" class="sql">insert into S_Depart(departid,Departname,Departorder)VALUES(S_DEPART_S.NEXTVAL,'12345',1);

In this case, we can query the value of the sequence S_Depart_s:

SELECT S_DEPART_S.CURRVAL FROM dual;

We can see that the current value of the S_Depart_s sequence is 2.

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.