How to insert AUTOGENERATED VALUE FROM ONE TABLE TO ANOTHER

Question

I am having a master table where i will have some configuration parameters for the application. For eg a table which stores all available languages.

     CREATE TABLE "LANGUAGE" 
       ("ID" NUMBER(5,0)  NOT NULL ENABLE, 
        "CODE" NVARCHAR2(5), 
        "DESCRIPTION" NVARCHAR2(50), 
        "IS_ACTIVE" NUMBER(1,0) DEFAULT 1)

Here id is a primary key and auto generated field. Also i am having a dependent table in another database say Customer_Language
 CREATE TABLE "Customer_Language" 
   (    "ID" NUMBER(5,0) NOT NULL ENABLE, 
    "CODE" NVARCHAR2(3), 
    "DESCRIPTION" VARCHAR2(50 BYTE), 
    "IS_ACTIVE" NUMBER(1,0)
   ) here id is not a auto generated field. Similarly i have around 4 tables with same structure. And all ids are not a autogenerated column. 
here is the script to insert via the dblink
 declare
      v_seq_name varchar2(20);
      v_str      varchar2(200);
      v_val      number;
    begin
      select sequence_name
      into v_seq_name
      from user_tab_identity_cols@Dblink1
      where table_name = 'S695_LANGUAGE';
 
    v_str := 'select ' || v_seq_name || '.nextval@MAR0695T_S695MST from dual';
     dbms_output.put_line('v_str = ' || v_str);
      execute immediate v_str into v_val;
       dbms_output.put_line('Value = ' || v_val);
 insert into Language@Dblink1 (CODE,DESCRIPTION,IS_ACTIVE) values ('NL11','NEDERLANDS','1');
 
 insert into customer_LANGUAGE@Dblink2 (id,CODE,DESCRIPTION,IS_ACTIVE) values (v_val,'NL11','NEDERLANDS','1');
 

    dbms_output.put_line('Value = ' || v_val);
    commit;
   end;
Here is the script to insert via the dblink and here i get the error

00000 - "distributed update operation failed; rollback required" *Cause: a failure during distributed update operation may not have rolled back all effects of the operation. Since some sites may be inconsistent, the transaction must roll back to savepoint or entirely *Action: rollback to a savepoint or rollback transaction and resubmit I want to get the ID value from the master table and need to insert that value to the dependent tables in other databases. I use dblink for remote database communication. How can i achieve tis

you can't all values must be n the table – nbk Jun 16 '23 at 20:55 — nbk, Jun 16 '23 at 20:55

Littlefoot · Accepted Answer · 2023-06-21T07:34:07.070

From your code, there's no evidence that language.id is automatically generated so I - while creating that table - set it to be an identity column.

If possible, get rid of double quotes for any kind of identifiers while working with Oracle; no benefit, only problems with that.

SQL> CREATE TABLE "LANGUAGE" (
  2      "ID"          NUMBER(5, 0) generated always as identity NOT NULL ENABLE,
  3      "CODE"        NVARCHAR2(5),
  4      "DESCRIPTION" NVARCHAR2(50),
  5      "IS_ACTIVE"   NUMBER(1, 0) DEFAULT 1
  6  );

Table created.

SQL> CREATE TABLE "Customer_Language" (
  2      "ID"          NUMBER(5, 0) NOT NULL ENABLE,
  3      "CODE"        NVARCHAR2(3),
  4      "DESCRIPTION" VARCHAR2(50 BYTE),
  5      "IS_ACTIVE"   NUMBER(1, 0)
  6  );

Table created.

In order to use the same ID value, one option is to switch to PL/SQL and use the returning clause:

SQL> declare
  2    l_id number;
  3  begin
  4    insert into LANGUAGE (CODE,DESCRIPTION,IS_ACTIVE) values ('HI','Hindi','1')
  5    returning id into l_id;
  6
  7    insert into "Customer_Language" (id, code, description, is_active)
  8    values (l_id, 'HI', 'Hindi', '1');
  9  end;
 10  /

PL/SQL procedure successfully completed.

Result:

SQL> select * from language;

        ID CODE  DESCRIPTION                                         IS_ACTIVE
---------- ----- -------------------------------------------------- ----------
         1 HI    Hindi                                                       1

SQL> select * from "Customer_Language";

        ID COD DESCRIPTION                                         IS_ACTIVE
---------- --- -------------------------------------------------- ----------
         1 HI  Hindi                                                       1

SQL>

As you can see, both ID columns are the same.

[EDIT, related to your comments]

I currently don't have access to database that supports identity columns - this is Oracle 11g. I tried to simulate what you did, and code you posted (with some changes, obviously) works OK.

There are two users: scott who will run PL/SQL code you posted and fetch/insert data over a database link; that DB link points to another user called mike.

First, connected as mike, I'm preparing environment:

SQL> connect mike/pwd@orcl
Connected.

SQL> create table user_tab_identity_cols as
  2  select 'S695_LANGUAGE' table_name, 'SEQ_LANG' sequence_name from dual;

Table created.

SQL> create table language (code varchar2(10), description varchar2(15), is_active varchar2(1));

Table created.

SQL> create sequence seq_lang;

Sequence created.

SQL>

Connected as scott, I'm running your code:

SQL> SET SERVEROUTPUT ON
SQL> CREATE TABLE customer_language
  2  (
  3     id            NUMBER,
  4     code          VARCHAR2 (10),
  5     description   VARCHAR2 (15),
  6     is_active     VARCHAR2 (1)
  7  );

Table created.

Procedure:

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 = 'S695_LANGUAGE';
 10
 11     v_str := 'select ' || v_seq_name || '.nextval@dbl_mike from dual';
 12     DBMS_OUTPUT.put_line ('v_str = ' || v_str);
 13
 14     EXECUTE IMMEDIATE v_str
 15        INTO v_val;
 16
 17     DBMS_OUTPUT.put_line ('Value = ' || v_val);
 18
 19     INSERT INTO Language@dbl_mike (CODE, DESCRIPTION, IS_ACTIVE)
 20          VALUES ('NL11', 'NEDERLANDS', '1');
 21
 22     INSERT INTO customer_LANGUAGE (id,
 23                                    CODE,
 24                                    DESCRIPTION,
 25                                    IS_ACTIVE)
 26          VALUES (v_val,
 27                  'NL11',
 28                  'NEDERLANDS',
 29                  '1');
 30
 31
 32     DBMS_OUTPUT.put_line ('Value = ' || v_val);
 33     COMMIT;
 34  END;
 35  /
v_str = select SEQ_LANG.nextval@dbl_mike from dual
Value = 4
Value = 4

PL/SQL procedure successfully completed.

Result: table over database link:

SQL> SELECT * FROM language@dbl_mike;

CODE       DESCRIPTION     I
---------- --------------- -
NL11       NEDERLANDS      1

Local table:

SQL> SELECT * FROM customer_language;

        ID CODE       DESCRIPTION     I
---------- ---------- --------------- -
         1 NL11       NEDERLANDS      1

SQL>

So: maybe it is about database version you use (check My Oracle Support for possible bug & solution (or patch)). Also, is there any database trigger involved? Because, there's no update statement in code you posted, while Oracle complains about "distributed update operation failed". Did you try to rollback before running that PL/SQL block (just in case)?

when i use the same with dblink i get error unsupported error with returning clause — coder11 b, Jun 21 '23 at 03:48
RETURNING clause isn't supported over database link, so you'll have to use a workaround: create (fetch) ID value from e.g. a sequence into a local variable, use it in INSERT and then return it to caller. — Littlefoot, Jun 21 '23 at 05:08
distributed update operation failed; rollback required" i tried the same but ii get this error...a failure during distributed update operation may not have rolled back all effects of the operation. Since some sites may be inconsistent, the transaction must roll back to savepoint or entirely — coder11 b, Jun 21 '23 at 06:34
select sequence_name into v_seq_name from user_tab_identity_cols@Dblink1 where table_name = 'S695_LANGUAGE'; v_str := 'select ' || v_seq_name || '.nextval@MAR0695T_S695MST from dual'; dbms_output.put_line('v_str = ' || v_str); execute immediate v_str into v_val; dbms_output.put_line('Value = ' || v_val); insert into Language@Dblink1 (CODE,DESCRIPTION,IS_ACTIVE) values ('NL11','NEDERLANDS','1'); insert into customer_LANGUAGE@Dblink2 (id,CODE,DESCRIPTION,IS_ACTIVE) values (v_val,'NL11','NEDERLANDS','1'); — coder11 b, Jun 21 '23 at 06:34
Code within a comment is difficult to read (as you can see). Edit the original question and post formatted code - all of it! - so that we'd see what you did. — Littlefoot, Jun 21 '23 at 06:38

How to insert AUTOGENERATED VALUE FROM ONE TABLE TO ANOTHER

1 Answers1