oracle:sql:sequence:using_a_sequence_in_a_table_column:from_oracle_12c

Table of Contents

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

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;

oracle/sql/sequence/using_a_sequence_in_a_table_column/from_oracle_12c.txt · Last modified: 2021/08/11 11:29 by peter