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
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.
My example works for me :). I used Oracle 12C on windows. On which OS did you test it ?
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?
There is no such thing in 10G
Tomasz