Oracle 19c SQL

  1. Home
  2. Docs
  3. Oracle 19c SQL
  4. 10 Sequences
  5. 10.3 Sequence in DML

10.3 Sequence in DML

A sequence number is generated independently of a transaction is committed or rolled back. It is possible that individual sequence numbers will appear to be skipped because they were generated and used in a transaction that ultimately rolled back.

Sequence numbers are generated independently of tables so they can be used for more than one table.

The NEXTVAL and CURRVAL pseudo columns are used in DML in following clauses:

  • VALUES clause of INSERT statements
  • The SET clause of an UPDATE statement

Let us understand how sequences can be helpful in DML. NEXTVAL can be used to generate unique values for primary key columns.

CREATE SEQUENCE Seq_Test01;

Sequence created.


CREATE TABLE Test01
(
 TestID   NUMBER(5)
     CONSTRAINT Test01_SampID_PK PRIMARY KEY,
 Testname VARCHAR2(10),
 TestDate DATE
);

Table created.

Insert to table where primary key values are generated through sequences.

INSERT INTO Test01 VALUES(Seq_Test01.NEXTVAL,'TEST-01','01-JAN-2020');

1 row created.


SELECT Seq_Test01.CURRVAL FROM DUAL;

   CURRVAL
----------
         1

INSERT INTO Test01 VALUES(Seq_Test01.NEXTVAL,'TEST-02','01-JAN-2020');

1 row created.

INSERT INTO Test01 VALUES(Seq_Test01.NEXTVAL,'TEST-03','01-JAN-2020');

1 row created.

INSERT INTO Test01 VALUES(Seq_Test01.NEXTVAL,'TEST-04','01-JAN-2020');

1 row created.

INSERT INTO Test01 VALUES(Seq_Test01.NEXTVAL,'TEST-04','01-JAN-2020');

1 row created.


SELECT * FROM Test01;

    TESTID TESTNAME   TESTDATE
---------- ---------- ---------
         1 TEST-01    01-JAN-20
         2 TEST-02    01-JAN-20
         3 TEST-03    01-JAN-20
         4 TEST-04    01-JAN-20
         5 TEST-04    01-JAN-20

Using NEXTVAL and CURRVAL in SET clause in update statement. Sequences CURRVAL and NEXTVAL are allowed only in SET clause in an UPDATE statement. Please see here the list of allowed clauses for CURRVAL and NEXTVAL.

--Error! NEXTVAL/CURRVAL not allowed in WHERE clause
UPDATE Test01
   SET TestID = 100
 WHERE TestID = Seq_Test01.CURRVAL;

ERROR at line 3:
ORA-02287: sequence number not allowed here
--NEXTVAL/CURRVAL in UPDATE statement SET clause
UPDATE Test01
   SET TestID = Seq_Test01.NEXTVAL
 WHERE TestID = 5;

1 row updated.


--NEXTVAL/CURRVAL expression in UPDATE statement SET clause
UPDATE Test01
   SET TestID = Seq_Test01.NEXTVAL + 100
 WHERE TestID = 4;

1 row updated.


SELECT * FROM Test01;

    TESTID TESTNAME   TESTDATE
---------- ---------- ---------
         1 TEST-01    01-JAN-20
         2 TEST-02    01-JAN-20
         3 TEST-03    01-JAN-20
       108 TEST-04    01-JAN-20
         6 TEST-04    01-JAN-20

Sequence value in transaction

Sequence values are incremented or decremented independent of the transaction is committed or rolled back. That means, NEXTVAL values are always incremented/decremented whether a transaction s committed or rolled back.

SELECT Seq_Test01.CURRVAL Curr FROM DUAL;

      CURR
----------
         8


--Error! Value supplied exceeds the length for 2nd column 
INSERT INTO Test01 
VALUES(Seq_Test01.NEXTVAL,'TEST-TEST-04', SYSDATE);

ERROR at line 2:
ORA-12899: value too large for column "SCOTT"."TEST01"."TESTNAME" (actual: 12, maximum: 10)


SELECT Seq_Test01.CURRVAL Curr FROM DUAL;

      CURR
----------
         9


--Error! Value supplied exceeds the length for TestID
UPDATE Test01
   SET TestID = Seq_Test01.NEXTVAL + 100000
 WHERE TestID = 2;

ERROR at line 2:
ORA-01438: value larger than specified precision allowed for this column


SELECT Seq_Test01.CURRVAL Curr FROM DUAL;

      CURR
----------
        10

NEXTVAL number is generated only if the SQL statement is syntacticly correct. Watch…

SELECT Seq_Test01.CURRVAL Curr FROM DUAL;

      CURR
----------
        10

--Error! CURRVAL/NEXTVAL not allowed in WHERE clause
UPDATE Test01
   SET TestID = 1000
 WHERE TestID = Seq_Test01.NEXTVAL;

ERROR at line 3:
ORA-02287: sequence number not allowed here


SELECT Seq_Test01.CURRVAL Curr FROM DUAL;

      CURR
----------
        10

Things to remember

  • Sequences are mainly used to automatically generate primary key values.
  • If a transaction is rolled back, the sequence numbers generated through NEXTVAL are lost permanently and can not be recovered again.
  • It is completely fine for tables to have gaps in primary keys which are generated through Sequences, as sequence numbers sometimes will be skipped because they are lost from failed transactions.
  • Sequence numbers are generated independently of tables so they can be used for more than one table.
Was this article helpful to you? Yes No

How can we help?