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.

About dnikiforov

I'm writing here about everything what is in my interest - Java, Oracle, MySQL and other things. These are just remarks about things I'm working with, and if this helps you, guys, in your work - this makes me happy. ;)
This entry was posted in Uncategorized. Bookmark the permalink.

One Response to 12c identity column in PL/SQL record type

  1. Gayathri Chakravarthy says:

    This helped so very much, I was pulling my hairs on a fix until I stumbled upon your blog post. Thanks again!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s