12c Autopopulating Column with Sequence Value

In yesterday’s blog post, I wrote about creating a table with an IDENTITY value.  But the Oracle 12c new feature that I like better is allowing a sequence to be the number generator for the default value of a column. To see what I mean, look at this example:

SQL> create sequence test_seq;
Sequence created.
SQL> create table test_tab (id number default test_seq.NEXTVAL, val varchar2(20));
Table created.

As you can see, the ID column of my test table has a DEFAULT clause which uses the NEXTVAL of the sequence I created. Now let’s insert some data into the table.

SQL> insert into test_tab (val) values ('first row');
1 row created.
SQL> insert into test_tab (val) values ('second row');
It is available in different strength and show great  cheap cialis pills effectiveness without showing any serious side-effects. Many senior citizens http://downtownsault.org/dragons-eye-tattoo/ buy viagra from india face the problems of swallowing the hard pills which do not go down the food and acquires essential nutrients. Her low back sample viagra pills  pain and headaches gradually improved. Three First Aid Items Pet Owners Should Have On HandAccording to 1800PetMeds.com, Benadryl, aspirin and bandage supplies are good items to have ready in case of injuries: 1. vardenafil tablets india 1 row created.
SQL> commit;
Commit complete.
SQL> select * from test_tab;
ID VAL
---------- --------------------
1 first row
2 second row

As you can see, the Oracle sequence was used to populate the values. At first glance, this would make life easier for the data modeler because one would not need to go the traditional route of coding a trigger to generate the NEXTVAL and assigning this value to the column. However, this autopopulation of the column value with the sequence only works if you do not specify a value, if you let it default. If you explicitly state the value, this method will not use the sequence. A trigger, if coded properly, would force the use of the sequence’s next value for the column.