Table of Contents

Oracle - SQL - Sequence - Using a Sequence in a Table Column - From Oracle 12c

From Oracle 12c, a Sequence can be associated with a Table Column via the Identity Column.

Create a table using the identity column for the id column

CREATE TABLE test(
  id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title VARCHAR2(255) NOT NULL
);

NOTE: This creates a Sequence that is associated with the id column of the table.

  • Because the Sequence is automatically generated for the id column, the name of the sequence may differ each time.
  • Oracle uses the sys.idnseq$ to store the link between the table and the sequence.

Return the association of the Tble and the Sequence

SELECT a.name AS TABLE_NAME, 
       b.name AS sequence_name
FROM sys.idnseq$ c
     JOIN obj$ a ON c.obj# = a.obj#
     JOIN obj$ b ON c.seqobj# = b.obj#
WHERE a.name = 'TEST';

Test

Insert some rows into the test Table

INSERT INTO test(title)
VALUES('This is a test');
 
INSERT INTO test(title)
VALUES('This is another test');

Query data from the test Table

SELECT id, title
FROM test;