12c identity column in PL/SQL record type

Identity columns are involved in 12c and this is amazing feature as it simplifies many aspects of program design.
But everything has a drawback. The first one is related to GENERAL ALWAYS default option and is similar to the problem what virtual columns cause when we try to use %ROWTYPE – based approach to insert records into table.

SQL> create table t (x int,
2 y generated always as (x+1))
3 /

Table created.

SQL> declare
2 tr t%rowtype;
3 begin
4 tr.x := 1;
5 insert into t values tr;
6 end;
7 /
declare
…
ORA-54013: INSERT operation disallowed on virtual columns
…

if we specify GENERATED ALWAYS identity column we get the similar problem (at least of the similar nature):

SQL> create table t (x int generated always as identity,
2 y varchar2(10))
3 /

Table created.

SQL> declare
2 tr t%rowtype;
3 begin
4 tr.y := ‘m’;
5 insert into t values tr;
6 end;
7 /
declare
*
…
ORA-32795: cannot insert into a generated always identity column
…

Identity columns have few more options – BY DEFAULT and BY DEFAULT ON NULL.
Let’s look on BY DEFAULT:

SQL> create table t (x int generated by default as identity,
2 y varchar2(10))
3 /

Table created.

SQL> declare
2 tr t%rowtype;
3 begin
4 tr.y := ‘m’;
5 insert into t values tr;
6 end;
7 /
declare
*
…
ORA-01400: cannot insert NULL into (“SCOTT”.”T”.”X”)
…

OK, now we can insert into column, but can’t insert NULL. The last step:

SQL> create table t (x int generated by default on null as identity,
2 y varchar2(10))
3 /

Table created.

SQL> declare
2 tr t%rowtype;
3 begin
4 tr.y := ‘m’;
5 insert into t values tr;
6 end;
7 /

PL/SQL procedure successfully completed.

SQL> select * from t
2 /

X Y
———- ———-
1 m

OK, now we’ve got the desirable result. So if we use BY DEFAULT ON NULL option we can continue to use ROW-based insert syntax in PL/SQL.