Since 12c we have had the cool IDENTITY column feature which removes the tedium of creating sequences, triggers and the like to maintain a simple surrogate column (typically a primary key).
SQL> create table t ( x int generated always as identity , y int);
Table created.
SQL> insert into t (y) values (10);
1 row created.
SQL> select * from t;
X Y
---------- ----------
1 10
Behind the scenes nothing has changed. We are still using a sequence to generate the values but it is just happening “automagically” for us.
SQL> select *
2 from user_tab_identity_cols
3 where table_name ='T'
4 @pr
==============================
TABLE_NAME : T
COLUMN_NAME : X
GENERATION_TYPE : ALWAYS
SEQUENCE_NAME : ISEQ$_254289
IDENTITY_OPTIONS : START WITH: 1,
INCREMENT BY: 1,
MAX_VALUE: 9999999999999999999999999999,
MIN_VALUE: 1,
CYCLE_FLAG: N,
CACHE_SIZE: 20,
ORDER_FLAG: N,
SCALE_FLAG: N,
EXTEND_FLAG: N,
SESSION_FLAG: N,
KEEP_VALUE: N
PL/SQL procedure successfully completed.
However, notice that in the INSERT above I omitted the identity column. Because the database is now controlling the column, you do not get to reference it during an insert when defined as above.
SQL> create table t ( x int generated always as identity , y int);
Table created.
SQL> insert into t values (1,10);
insert into t values (1,10)
*
ERROR at line 1:
ORA-32795: cannot insert into a generated always identity column
This is normally not any cause for concern. However, one place where you might get caught out is using the nifty PL/SQL row clause for DML. With any currently supported version of the Oracle database, PL/SQL allows you to use a shortcut to insert an entire row of data using a %ROWTYPE variable.
SQL> create table t ( x int, y int );
Table created.
SQL> declare
2 r t%rowtype;
3 begin
4 r.x := 1;
5 r.y := 10;
6 insert into t values r;
7 end;
8 /
PL/SQL procedure successfully completed.
Because the smallest “unit of work” for an insert is a row, this syntax is a nice convenience feature of PL/SQL. This is not a true SQL feature as such. Behind the scenes at execution time the INSERT is expanded out to the all of the required columns and bind variables for each of the %ROWTYPE attributes.
This unfortunately creates a conflict with an IDENTITY column because as we just observed, you are not allowed to reference the column in your INSERT, and hence we get the following error:
SQL> create table t ( x int generated always as identity , y int);
Table created.
SQL> declare
2 r t%rowtype;
3 begin
4 r.y := 10;
5 insert into t values r;
6 end;
7 /
declare
*
ERROR at line 1:
ORA-32795: cannot insert into a generated always identity column
ORA-06512: at line 5
The hint here is the “ALWAYS” part of the syntax. As the text suggests, we are always going to generate the value, and as such you are always not allowed to provide it.
Another option for identity columns is “DEFAULT” instead of “ALWAYS”. Using this will indeed allow the column to be now referenced via a %ROWTYPE variable, but unfortunately, because we are explicitly binding each of the individual variables to the INSERT, we’ll get a NOT NULL violation.
SQL> create table t ( x int generated by default as identity, y int);
Table created.
SQL> declare
2 r t%rowtype;
3 begin
4 r.y := 10;
5 insert into t values r;
6 end;
7 /
declare
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("MCDONAC"."T"."X")
ORA-06512: at line 5
Thus if you want to keep the ease and convenience of using %ROWTYPE variables, but you also want to use an identity column, you will need to opt for the DEFAULT ON NULL syntax to ensure you do not get any nasty consequences.
SQL> create table t ( x int generated by default on null as identity , y int);
Table created.
SQL> declare
2 r t%rowtype;
3 begin
4 r.y := 10;
5 insert into t values r;
6 end;
7 /
PL/SQL procedure successfully completed.
Learning is not a spectator sport 
Got some thoughts? Leave a comment