I have created a table with generated identity column .script below CREATE TABLE "TABLESAMPLE" ( "DESCRIPTION" VARCHAR2(2 BYTE), "TID" NUMBER(5,0) GENERATED ALWAYS AS IDENTITY MINVALUE 1 MAXVALUE 99 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE NOKEEP NOSCALE NOT NULL ENABLE ) SEGMENT CREATION DEFERRED
i am inserting data to the db from a remote database using dblink like below. Here when i return the id i get the below error on (RETURNING )
Insert into TABLESAMPLE@dblink1(DESCRIPTION) values('1') RETURNING tid INTO v_tid_return;
*Cause: RETURNING clause is currently not supported for object type
columns, LONG columns, remote tables, INSERT with subquery,
and INSTEAD OF Triggers.
I am using oracle sql developer to run the script. can you please help
As it says:
Cause: RETURNING clause is currently not supported for object type columns, LONG columns, remote tables, INSERT with subquery, and INSTEAD OF Triggers.
Table over a database link is a remote table.
Mike is user accessed via database link:
SQL> connect mike/lion@pdb1
Connected.
SQL> create table tablesample
2 (description varchar2(2),
3 tid number generated always as identity
4 );
Table created.
SQL> insert into tablesample(description) values ('LF');
1 row created.
SQL> select * from tablesample;
DE TID
-- ----------
LF 1
Back to scott (it contains database link to user mike):
SQL> connect scott/tiger@pdb1
Connected.
SQL> set serveroutput on
SQL> declare
2 l_tid number;
3 begin
4 insert into tablesample@dbl_mike (description) values ('XY')
5 returning tid into l_tid;
6
7 dbms_output.put_line('Returned value = ' || l_tid);
8 end;
9 /
declare
*
ERROR at line 1:
ORA-22816: unsupported feature with RETURNING clause
ORA-06512: at line 4
Would a synonym in my own schema help? Unfortunately, not:
SQL> create synonym scott_tablesample for tablesample@dbl_mike;
Synonym created.
SQL> declare
2 l_tid number;
3 begin
4 insert into scott_tablesample (description) values ('XY')
5 returning tid into l_tid;
6 dbms_output.put_line('Returned value = ' || l_tid);
7 end;
8 /
declare
*
ERROR at line 1:
ORA-22816: unsupported feature with RETURNING clause
ORA-06512: at line 4
What to do? Nothing much, regarding the returning clause. If you recreated the table so that the ID isn't generated always (i.e. you're allowed to insert your own values):
SQL> connect mike/lion@pdb1
Connected.
SQL> drop table tablesample;
Table dropped.
SQL> create table tablesample
2 (description varchar2(2),
3 tid number generated by default on null as identity
4 );
Table created.
SQL> insert into tablesample(description) values ('AB');
1 row created.
SQL> select * From tablesample;
DE TID
-- ----------
AB 1
Find which sequence is being used for the identity column:
SQL> select data_default from user_tab_columns where table_name = 'TABLESAMPLE';
DATA_DEFAULT
--------------------------------------------------------------------------------
"MIKE"."ISEQ$$_99985".nextval
Connected as scott, create a synonym to the sequence and use it in insert statement:
SQL> connect scott/tiger@pdb1
Connected.
SQL> create synonym mike_seq for iseq$$_99985@dbl_mike;
Synonym created.
Insert:
SQL> declare
2 l_seq number;
3 begin
4 l_seq := mike_seq.nextval;
5 insert into tablesample@dbl_mike (description, tid)
6 values ('MN', l_seq);
7 dbms_output.put_line('TID = ' || l_seq);
8 end;
9 /
PL/SQL procedure successfully completed.
SQL> select * from tablesample@dbl_mike;
DE TID
-- ----------
MN 2 --> here it is
AB 1
SQL>
[EDIT]
If you want to get sequence name used for identity column, query
SQL> select sequence_name
2 from user_tab_identity_cols
3 where table_name = 'TABLESAMPLE';
SEQUENCE_NAME
--------------------------------------------------------------------------------
ISEQ$$_99985
SQL>
[EDIT #2]
If you want to use sequence name in PL/SQL procedure, you'll need dynamic SQL. Apart from that, currval won't work if sequence hasn't been initialized yet in this session:
SQL> show user
USER is "SCOTT"
SQL> set serveroutput on
SQL> declare
2 v_seq_name varchar2(20);
3 v_str varchar2(200);
4 v_val number;
5 begin
6 select sequence_name
7 into v_seq_name
8 from user_tab_identity_cols@dbl_mike
9 where table_name = 'TABLESAMPLE';
10
11 v_str := 'select ' || v_seq_name || '.currval@dbl_mike from dual';
12 execute immediate v_str into v_val;
13 dbms_output.put_line('Value = ' || v_val);
14 end;
15 /
declare
*
ERROR at line 1:
ORA-08002: sequence ISEQ$$_99985.CURRVAL is not yet defined in this session
ORA-02063: preceding line from DBL_MIKE
ORA-06512: at line 12
Therefore, use nextval:
SQL> declare
2 v_seq_name varchar2(20);
3 v_str varchar2(200);
4 v_val number;
5 begin
6 select sequence_name
7 into v_seq_name
8 from user_tab_identity_cols@dbl_mike
9 where table_name = 'TABLESAMPLE';
10
11 v_str := 'select ' || v_seq_name || '.nextval@dbl_mike from dual';
12 execute immediate v_str into v_val;
13 dbms_output.put_line('Value = ' || v_val);
14 end;
15 /
Value = 7
PL/SQL procedure successfully completed.
SQL>