How to fetch data from multiple Database Links and insert it into specific tables using LOOP

Question

I do have Oracle DataBase which has multiple DB_LINKS.

These DB_Links are stored in local (DB_LINKS_TBL)table.

The target is to synchronize the remote data with local machine.

I did the task using LOOP and REF_CURSOR insert but cannot insert REF_CURSOR into Table.

DB_LINKS_TBL Table:

CREATE TABLE DB_LINKS_TBL(DB_Link_Name VARCHAR2(50));
INSERT INTO DB_LINKS_TBL VALUES ('CAIRO_DB');
INSERT INTO DB_LINKS_TBL VALUES ('ALEX_DB');

PROCEDURE TO Synchronize data from CAIRO_DB , ALEX_DB:

DECLARE
  CURSOR C_DB_LINK IS
         SELECT DB_Link_Name  FROM DB_LINKS_TBL ;
  L_Returnvalue SYS_REFCURSOR;
  L_Sql         VARCHAR2(10000);
BEGIN
  --------------------#
  FOR db IN   C_DB_LINK LOOP
      ----------------
      L_Sql := 'SELECT Emp_Id, Emp_Name
                FROM   EMPS
                WHERE NOT EXISTS ( SELECT ''X''
                                   FROM   EMPS@'||db.DB_Link_Name||'  D
                                   WHERE  D.Emp_Id = S.Emp_Id) ';
      OPEN L_Returnvalue FOR L_Sql ;
      ----------------
      INSERT INTO EMPS   VALUES (L_Returnvalue);      
      COMMIT;
      ----------------
  END LOOP;
  --------------------#
END;

create an record type and use that instead of ref cursor. – OldProgrammer Aug 08 '19 at 16:32 — OldProgrammer, Aug 08 '19 at 16:32

score 1 · Answer 1 · answered Aug 08 '19 at 17:09

I wouldn't use a SYS_REFCURSOR for this. Just do the INSERT directly

  L_Sql := 'INSERT INTO emps( emp_id, emp_name )
            SELECT Emp_Id, Emp_Name
            FROM   EMPS
            WHERE NOT EXISTS ( SELECT ''X''
                               FROM   EMPS@'||db.DB_Link_Name||'  D
                               WHERE  D.Emp_Id = S.Emp_Id) ';
  execute immediate l_sql;

Just taking a step back, though, are you certain that it makes sense architecturally to do this? Oracle offers things like materialized views to replicate data between environments and has functionality for multi-master replication if individual rows can be modified on multiple source systems. Rolling your own custom replication solution rarely makes sense.

How to fetch data from multiple Database Links and insert it into specific tables using LOOP

1 Answers1