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.
- Oracle 12c create table doc: https://docs.oracle.com/database/121/SQLRF/statements_7002.htm#CJAHJHJC
- Ask Tom – 12c new features: http://www.oracle.com/technetwork/issue-archive/2013/13-sep/o53asktom-1999186.html
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.
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.