Retuning statement with dblink gives error

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>

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.
>