Default Values for Columns Based on Oracle Sequences Oracle Database 12C release 1 (12.1)

In Oracle 12c DEFAULT values for columns can directly refer to Oracle sequences.

  • DEFAULT sequence.NEXTVAL
  • DEFAULT sequence.CURRVAL
create sequence test_seq;
create synonym test_seq1 for test_seq;

create table test_tbl
(id  number default test_seq.currval,
 id1 number default test_seq.nextval,
 id2 number default test_seq1.currval,
 id3 number);

insert into test_tbl(id3) values(10);
insert into test_tbl(id3) values(20);
insert into test_tbl(id3) values(30);

select * from test_tbl;
        ID        ID1        ID2        ID3
---------- ---------- ---------- ----------
         1          1          1         10 
         2          2          2         20 
         3          3          3         30

Still you can insert NULL and explicit values for such columns

insert into test_tbl values(default, NULL, 5, 40);

select * from test_tbl where id3=40;

        ID        ID1        ID2        ID3
---------- ---------- ---------- ----------
         3                     5         40

Facts to remember

  • If sequence is not fully qualified by specifying the sequence owner then sequence owner is the user who issues the CREATE TABLE statement
  • Synonyms on sequences undergo a full name resolution and are stored as the fully qualified sequence in the data dictionary. It is true for public and private synonyms
declare
  v_id number;
begin  
 v_id := dbms_metadata.session_transform;

 dbms_metadata.set_transform_param(v_id, 'STORAGE', FALSE);
 dbms_metadata.set_transform_param(v_id, 'SEGMENT_ATTRIBUTES', FALSE);
end;
/

select dbms_metadata.get_ddl('TABLE', 'TEST_TBL') from dual;

  CREATE TABLE "TOMASZ"."TEST_TBL" 
   (    "ID" NUMBER DEFAULT "TOMASZ"."TEST_SEQ"."CURRVAL", 
    "ID1" NUMBER DEFAULT "TOMASZ"."TEST_SEQ"."NEXTVAL", 
    "ID2" NUMBER DEFAULT "TOMASZ"."TEST_SEQ"."CURRVAL", 
    "ID3" NUMBER
   )

Have a fun 🙂

Tomasz

4 thoughts on “Default Values for Columns Based on Oracle Sequences Oracle Database 12C release 1 (12.1)

  1. one issue I hit – using your example, you get sequence TEST_SEQ1.CURRVAL is not yet defined in this session. I assume you selected the nextval from the sequences before the first insert command? To get yoor example to work, I cretaed the indexes with ‘start with 0 minvalue 0’ and then did 1 select nextval from both. that initialized currval to zero and then the inserts worked.

  2. Is there anything similar in Oracle 10g?

    i like “auto_increment” as used in mysql for example.

    In 10g, which we use at our university, we use sequence plus trigger.

    Is there any easier way to implement auto increment columns?

Leave a Reply

Your email address will not be published. Required fields are marked *