Total Pageviews

August 27, 2016

8/27/2016 02:40:00 PM

In previous releases of the Oracle database, there was no direct equivalent of the Auto Number or Identity functionality of other database engines. Instead, this behavior had to be implemented using a combination of sequences and triggers. Oracle 12c introduces two alternatives to this by providing identity columns and the ability to use sequence pseudo columns as default values. This article will focus on the use of identity columns.

SQL> create table test2 (col1 number generated always as identity);
SQL> create table test3 (col1 number generated always as identity (start with 1000
increment by 10));
SQL> insert into test2 values (1);
insert into test2 values (1)
*
ERROR at line 1:
ORA-32795: cannot insert into a generated always identity column
SQL> create table test6 (col1 number, col2 number generated by default as identity);
SQL> insert into test6 values (9,9);
SQL> insert into test6values (10,default);
SQL> insert into test6 (col1) values (11)
SQL> select * from t9;
COL1 COL2
---------- ----------
9 9
10 2
11 3
 
Related Posts Plugin for WordPress, Blogger...