Retuning statement with dblink gives error

Question

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

The error is pretty self-explanitory: you can't use the `RETURNING` clause on a remote table over a database link. — pmdba, Jun 07 '23 at 19:07

Littlefoot · Accepted Answer · 2023-06-08T13:30:19.513

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>

this is good. Can i get only the seq name here in this select data_default from user_tab_columns where table_name = 'TABLESAMPLE'; ....that means i need to extract only SEQ$$_99985. Because after insert i can get the currval using seq name — coder11 b, Jun 07 '23 at 21:15
Query USER_TAB_IDENTITY_COLS (see example in edited answer). — Littlefoot, Jun 08 '23 at 05:44
DECLARE v_seq_name VARCHAR2(20); BEGIN select sequence_name INTO v_seq_name from user_tab_identity_cols@dblink1 where table_name = 'table1'; select v_seq_name.currval@dblink1 into v_rid_return from dual; END; — coder11 b, Jun 08 '23 at 11:09
i tried to get the sequence name in a variable and using that variable i tried to get the currval ... but this doesnt work for me — coder11 b, Jun 08 '23 at 11:10

score 0 · Answer 2 · answered Jun 07 '23 at 22:06

As error says, it is not supported. However, you may use dbms_sql package over DB link to execute remote SQL code.

SQL> declare
  2      l_id int;
  3      l_value constant int := 100;
  4  begin
  5      insert into t@test2_dblink(val)
  6      values(l_value)
  7      returning id into l_id;
  8  
  9      dbms_output.put_line(l_id);
 10  end;
 11  /

Error starting at line : 5 in command -
declare
    l_id int;
    l_value constant int := 100;
begin
    insert into t@test2_dblink(val)
    values(l_value)
    returning id into l_id;

    dbms_output.put_line(l_id);
end;
Error report -
ORA-22816: с фразой RETURNING это средство не поддерживается
ORA-06512: на  line 5
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.

Table is empty.

SQL> select *
  2  from t@test2_dblink
  3  /

0 rows selected.

SQL> declare
  2      l_id int;
  3      l_value constant int := 100;
  4      l_cursor int;
  5      l_stmt varchar2(1000);
  6      l_rowcount int;
  7  begin
  8      l_stmt := q'{insert into t(val)
  9      values(:value)
 10      returning id into :id}';
 11  
 12      l_cursor := dbms_sql.open_cursor@test2_dblink();
 13      dbms_sql.parse@test2_dblink(l_cursor, l_stmt, dbms_sql.native);
 14  
 15      dbms_sql.bind_variable@test2_dblink(l_cursor, 'value', l_value);
 16      dbms_sql.bind_variable@test2_dblink(l_cursor, 'id', l_id);
 17      l_rowcount := dbms_sql.execute@test2_dblink(l_cursor);
 18      dbms_sql.variable_value@test2_dblink(l_cursor, 'id', l_id);
 19      dbms_sql.close_cursor@test2_dblink(l_cursor);
 20  
 21  
 22      dbms_output.put_line('ID is: ' || l_id);
 23      commit;
 24  end;
 25  /

ID is: 3


PL/SQL procedure successfully completed.

Value was inserted successfully.

SQL> select *
  2  from t@test2_dblink
  3  /

        ID        VAL
---------- ----------
         3        100

1 row selected.

Retuning statement with dblink gives error

2 Answers2