Oracle 19c SQL

  1. Home
  2. Docs
  3. Oracle 19c SQL
  4. 10 Sequences
  5. 10.2 Working with Sequences

10.2 Working with Sequences

Accessing Sequences

Oracle provides CURRVAL and NEXTVAL pseudo columns which operate on Sequence schema object to access sequence values in SQL statements.

NEXTVAL and CURRVAL can be used with SQL statements such as SELECT, INSERT, or UPDATE.

  • CURRVAL pseudo column returns the current value of the sequence.
  • NEXTVAL pseudo column increments the sequence and returns the new value.

Limitation with CURRVAL

To get the current sequence value in your session, use seq_name.CURRVAL. CURRVAL can only be used if seq_name.NEXTVAL has been referenced in the current user session (in the current or a previous transaction). Have a look…

CREATE SEQUENCE TestSeq01
INCREMENT BY 1
START WITH 0
MINVALUE 0
MAXVALUE 10
NOCACHE
NOCYCLE;

Sequence created.


--Error! Accessing Sequence value using CURRVAL before NEXTVAL
SELECT TestSeq01.CURRVAL Curr FROM DUAL;

ERROR at line 1:
ORA-08002: sequence TESTSEQ01.CURRVAL is not yet defined in this session


--Accessing Sequence value using NEXTVAL
SELECT TestSeq01.NEXTVAL Next FROM DUAL;

      NEXT
----------
	 0


--Accessing Sequence value using CURRVAL
SELECT TestSeq01.CURRVAL FROM DUAL;

   CURRVAL
----------
	 0

CURRVAL can be referenced as many times as necessary, including multiple times within the same statement.

--Create a sequence that generates odd numbers
CREATE SEQUENCE TestSeq02
INCREMENT BY 2
START WITH 1;

Sequence created.


--CURRVAL and NEXTVAL can be accessed in same SQL
SELECT TestSeq02.NEXTVAL Next, TestSeq02.CURRVAL Curr 
  FROM DUAL;

      NEXT	 CURR
---------- ----------
	 1	    1


SELECT TestSeq02.NEXTVAL Next FROM DUAL;

      NEXT
----------
	 3


SELECT TestSeq02.CURRVAL Curr FROM DUAL;

      CURR
----------
	 3


--Multiple NEXTVAL in same SQL produce the same value
SELECT TestSeq02.NEXTVAL Next1, TestSeq02.NEXTVAL Next2 
  FROM DUAL;

     NEXT1	NEXT2
---------- ----------
	 5	    5


--Multiple CURRVAL in same SQL produce the same value
SELECT TestSeq02.CURRVAL Curr1, TestSeq02.CURRVAL Curr2
  FROM DUAL;

     CURR1	CURR2
---------- ----------
	 5	    5


SELECT TestSeq02.NEXTVAL Next, TestSeq02.CURRVAL Curr 
  FROM DUAL;

      NEXT	 CURR
---------- ----------
	 7	    7

Uses of NEXTVAL and CURRVAL

CURRVAL and NEXTVAL can be used in the following places:

  • The SELECT list of a SELECT statement
  • VALUES clause of INSERT statements
  • The SET clause of an UPDATE statement

Restrictions of NEXTVAL and CURRVAL

CURRVAL and NEXTVAL cannot be used in these places:

  • A subquery
  • A view query or materialized view query
  • A SELECT statement with the DISTINCT operator
  • A SELECT statement with a GROUP BY or ORDER BY clause
  • A SELECT statement that is combined with another SELECT statement with the UNION, INTERSECT, or MINUS set operator
  • The WHERE clause of a SELECT statement
  • DEFAULT value of a column in a CREATE TABLE or ALTER TABLE statement
  • The condition of a CHECK constraint

Some common errors while creating Sequence.

CREATE SEQUENCE Seq_Test_01
INCREMENT BY 1.5;

ERROR at line 1:
ORA-04001: sequence parameter INCREMENT must be an integer
CREATE SEQUENCE Seq_Test_01
MAXVALUE 5
MINVALUE 10;

ERROR at line 1:
ORA-04004: MINVALUE must be less than MAXVALUE
CREATE SEQUENCE Seq_Test_01
START WITH 1
MINVALUE 10;

ERROR at line 1:
ORA-04006: START WITH cannot be less than MINVALUE
Was this article helpful to you? Yes No

How can we help?