Select Sequence.currval times wrong ORA-08002: sequence xxxx has not been defined in this process

Scenario Description:

1. We create a table with the table named S_depart

--Create tablecreate table S_depart (  departid    INTEGER not NULL,  departname  NVARCHAR2 (+) NOT NULL,  departorder INTEGER default 0)

2. Create a sequence sequence with the name s_depart_s

CREATE SEQUENCE s_depart_s MINVALUE 1MAXVALUE 9999999999999999999999START with 1INCREMENT by 1NOCACHE;

3. Below we insert a record into the table

Insert into S_depart (departid,departname,departorder) VALUES (s_depart_s.currval, ' 12345 ', 1);

at this point, the following error occurs:

Error ORA-08002: Sequence XXXX has not been defined in this process

Error Analysis: The currval of the sequence sequence must first be initialized with Nextval, and later it can be used currval.

Workaround: Initialize with Nextval first:

<pre name= "code" class= "SQL" >insert into S_depart (departid,departname,departorder) VALUES (S_depart_s.nextval, ' 12345 ', 1);

At this point we can query the value of the sequence sequence s_depart_s:

SELECT S_depart_s.currval from dual;

From what we can see the current value of the s_depart_s sequence is 2

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

Select Sequence.currval times wrong ORA-08002: sequence xxxx has not been defined in this process