identity column issue with DB links

Introduction

In oracle 12c we have some new features regarding the usage of sequences especially for primary key columns. The usage of default values and identity columns.

Both are very performant which has been shown several times already. While default values are really nice to use, identity columns have an essential drawback.

If one inserts into a remote table, then it is not possible to get the generated PK value.

Links

Problem demonstration

Assume we have two databases A_local and B_remote. On database A_local we have a database link (called DBLINK) pointing to some schema on database B_remote.

We now setup two tables on the remote database.

create sequence demo_seq;
create table demo_remoteinsert1 
   (  id number default demo_seq.nextval not null primary key
    , txt varchar2(100) not null
    );
    
create table demo_remoteinsert2 
   (  id number generated as identity primary key
    , txt varchar2(100) not null
    );

Note that table 1 uses a default value based upon the sequence DEMO_SEQ. Table 2 uses an identity column. The identity column behind the scenes is implemented using a default value and a sequence. I will show this later below.

On the local database A_local we set up some synonyms and views to have an easy access to those remote obejcts.

create synonym remote_demo_Seq for demo_seq@dblink;
create view v_demo_remoteinsert1 
  as select * from demo_remoteinsert1@dblink;
create view v_demo_remoteinsert2 
  as select * from demo_remoteinsert2@dblink;

Now we want to do an insert in the A_Local database.

set serveroutput on
declare
  v_id v_demo_remoteinsert1.id%type;
begin
  insert into v_demo_remoteinsert1 (txt)
  values ('abc')
  returning id into v_id;
  dbms_output.put_line('new ID='||v_id);
end;
/
ORA-22816: unsupported feature with RETURNING clause
ORA-06512: at line 4
22816. 00000 -  "unsupported feature with RETURNING clause"
*Cause:    RETURNING clause is currently not supported for object type
           columns, LONG columns, remote tables, INSERT with subquery,
           and INSTEAD OF Triggers.
*Action:   Use separate select statement to get the values.

It is not possible to do an insert and fetch the generated ID values using the returning clause.

Fortunately there is a simple workaround. Fetch first from the sequence, then do the insert using the sequence value.

set serveroutput on
declare
  v_id v_demo_remoteinsert1.id%type;
begin
  v_id := remote_demo_seq.nextval;  
  insert into v_demo_remoteinsert1 (id, txt)
  values (v_id, 'abc');
  dbms_output.put_line('new ID='||v_id);
end;
/
PL/SQL procedure successfully completed.

new ID=1

Now lets try the same using table 2. Remember table 2 has column ID generated as identity.

If we try the returning clause we get the same error as above.

set serveroutput on
declare
  v_id v_demo_remoteinsert2.id%type;
begin
  insert into v_demo_remoteinsert2 (id, txt)
  values (v_id, 'abc')
  returning id into v_id;
  dbms_output.put_line('new ID='||v_id);
end;
/
ORA-22816: unsupported feature with RETURNING clause
22816. 00000 -  "unsupported feature with RETURNING clause"
*Cause:    RETURNING clause is currently not supported for object type
           columns, LONG columns, remote tables, INSERT with subquery,
           and INSTEAD OF Triggers.
*Action:   Use separate select statement to get the values.

The workaround would be fetch from the sequence first, then do the insert.
In this case we did not directly create a sequence object. However oracle created such an object and we can find out which sequence it is. The generated sequence will be named “ISEQ$_xxxx” where xxx is a generated number. In my example it was ISEQ$$_1988882.

The correct way to find which sequence object was generated is to look at the default value for the column.

select column_name, data_default, identity_column
from user_tab_columns 
where table_name = 'DEMO_REMOTEINSERT2';
COLUMN_NAME	DATA_DEFAULT	IDENTITY_COLUMN
ID	"B_REMOTE"."ISEQ$$_1988882".nextval	YES
TXT		NO

Using that sequence name we can try to do a remote insert in A_local.

create synonym remote_demo_seq2 for ISEQ$$_1988882@dblink;

set serveroutput on
declare
  v_id v_demo_remoteinsert2.id%type;
begin
  v_id := remote_demo_seq2.nextval;  
  insert into v_demo_remoteinsert2 (id, txt)
  values (v_id, 'abc');
  dbms_output.put_line('new ID='||v_id);
end;
/
Error report -
ORA-32795: cannot insert into a generated always identity column
32795.0000 -  "cannot insert into a generated always identity column"
*Cause:    An attempt was made to insert a value into an identity column
           created with GENERATED ALWAYS keywords.
*Action:   A generated always identity column cannot be directly inserted.
           Instead, the associated sequence generator must provide the value.

Well we did use the associated sequence generator to provide the value, however oracle doesn’t know this and still creates an error.

workarounds

How to solve this problem?

There is no perfect solution. However depending on the environment I see two possible ways to circumvent the problem.

Workaround 1) Create a plsql api to do the insert

This will work only if you are allowed to create remote procedures and functions. Also there might be some obstacles to implement this in a very robust way. For example there should be almost no additional overhead if the table structure changes (added columns etc.).

This plsql api could also solve the issue why we need this ID returned in the first place. For example if we want to insert data into some child tables then we need the ID value to be used as a FK for the child table. The api could do it all in one go.

Workaround 2) Do a select afterwards using an unique identifier

Most tables should have a PK populated by a meaningless technical sequence value. Most tables will also have a UK based upon some meaningful business value. If such an UK exists then we can do a second select on the table using the UK values to fetch the PK value. It is cumbersome and not as performant but it works if the data model is well designed.

Workaround 3) Fetch currval in the same session

I personally dislike the usage of currval. This is the first example where I found it to be usable.

set serveroutput on
declare
  v_id v_demo_remoteinsert2.id%type;
begin
  insert into v_demo_remoteinsert2 (txt)
  values ('abc');
  select remote_demo_seq2.currval into v_id from dual;  
  dbms_output.put_line('new ID='||v_id);
end;
/
PL/SQL procedure successfully completed.

new ID=2

Note that this is not the recommended action based upon oracles own error description.

Additionally I am not 100% sure that CURRVAL will always return the correct value and that it will always return a value for our session when used via DB link.

This is from the oracle documentation:

When you create a sequence, you can define its initial value and the increment between its values. The first reference to NEXTVAL returns the initial value of the sequence. Subsequent references to NEXTVAL increment the sequence value by the defined increment and return the new value. Any reference to CURRVAL always returns the current value of the sequence, which is the value returned by the last reference to NEXTVAL.

Before you use CURRVAL for a sequence in your session, you must first initialize the sequence with NEXTVAL. Refer to CREATE SEQUENCE for information on sequences.

Since we need to call nextval in our session first to initialize currval it should work. However I did not find an offical oracle document specifially explaining this for usage over database links.

It is hard to setup a testcase where another session inserts some values at the same time and thereby manipulating the sequence cache. I tried, but cound’t break the example. So with all caution, it could be an easy workaround to setup.

Solution: Let oracle implement the returning clause also via database links!

I created an database idea for this. Vote up here to increase the chance that this is implemented in a future version.

Advertisements

2 thoughts on “identity column issue with DB links

  1. Your workaround should also work for your second table with the identity column if you create it using “generated BY DEFAULT [ON NULL] as identity”. Without it your identity column value is ALWAYS generated, and you are not allowed to specify it in your column list, even if you use the correct sequence to generate it.

    • Yes you are right. “generated by default on null as identity” will work. It even allows to insert other keys, so the behaviour seems to be identical. I still prefer the default value solution because it gives a little more control over the sequence (nameing and dropping considerations). And it is the same logic that I use for setting audit columns like inserted_date. Some very crude tests showed that IDENTITY is even a tiny tiny bit faster than the DEFAULT value version.

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