Wiki

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== 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 =====

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

<WRAP info>
**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.
</WRAP>

----

===== Return the association of the Tble and the Sequence =====

<code sql>
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';  
</code>

----

===== Test =====

==== Insert some rows into the test Table ====

<code sql>
INSERT INTO test(title)
VALUES('This is a test');

INSERT INTO test(title)
VALUES('This is another test');
</code>

----

==== Query data from the test Table ====

<code sql>
SELECT id, title
FROM test;
</code>