Create table cd_patient (pat_mrn varchar2(100)) ;
  Create table cd_encount (pat_mrn varchar2(100), encounter_id varchar2(1000));

declare
 

 BEGIN
       INSERT INTO "cd_patient"  C-- DYNAMICALLY GET THE TABLE NAME 
         SELECT C1.PAT_MRN -- DYNAMICALLY GET THE COLUMN NAME
             FROM REMOTE_DB.CD_PATIENT@XXX C1
                 WHERE C1.LOAD_NUMBER > 2;

       INSERT INTO "cd_encount "  C-- DYNAMICALLY GET THE TABLE NAME 
         SELECT C1.PAT_MRN, C1.encounter_id  -- DYNAMICALLY GET THE COLUMN NAME
             FROM REMOTE_DB.cd_encount@XXX C1
                 WHERE C1.LOAD_NUMBER > 2;
 COMMIT;

  END;

DECLARE

 V_tbl          Varchar2(200);

   Cursor C_TBL Is
    SELECT TABLE_NAME
      FROM ALL_TABLES
      WHERE TABLE_NAME LIKE 'CD%';

    Cursor C_TBL_COL (P_TBL VARCHAR)
   Is
    SELECT COLUMN_NAME
      FROM ALL_TAB_COLUMNS
     Where TABLE_NAME LIKE 'CD%'
       AND TABLE_NAME = P_TBL
       ORDER BY COLUMN_ID;
  

BEGIN

For L1 in C_TBL loop
  V_tbl := L1.table_name;


EXECUTE IMMEDIATE 
  'INSERT INTO ' || V_tbl ||
      ' SELECT ' -- NOT SURE HOW TO RETRIEVE THE COLUMNS
           || ' FROM REMOTE_DB.' || V_tbl || 
              ' V1  WHERE V1.LOAD_NUMBER > 2';
          
COMMIT;     

END; 

DECLARE

 V_tbl          Varchar2(200);
 V_col          Varchar2(4000);
 V_SQL          Varchar2(4000);
 
   Cursor C_TBL Is
    SELECT TABLE_NAME
      FROM ALL_TABLES
      WHERE TABLE_NAME LIKE 'CD%';

    Cursor C_TBL_COL (P_TBL VARCHAR)
   Is
    SELECT COLUMN_NAME
      FROM ALL_TAB_COLUMNS
     Where TABLE_NAME LIKE 'CD%'
       AND TABLE_NAME = P_TBL
       AND TABLE_NAME = 'CM_USER'
       ORDER BY COLUMN_ID;

 TYPE V_TT IS TABLE OF C_TBL_COL%ROWTYPE INDEX BY PLS_INTEGER;
   L_TT V_TT;  

BEGIN

For L1 in C_TBL loop
  V_tbl := L1.table_name;
--  DBMS_OUTPUT.PUT_LINE (V_tbl);
OPEN C_TBL_COL (L1.table_name);
V_COL := '';
  
Loop
 FETCH C_TBL_COL BULK COLLECT INTO L_TT LIMIT 500;
  FOR indx IN 1 .. L_TT.COUNT
        LOOP
          V_col := V_col || ',' || L_TT(indx).COLUMN_NAME;
         --  DBMS_OUTPUT.PUT_LINE (V_col);
        END LOOP;
      EXIT WHEN L_TT.COUNT = 0;
    END LOOP;

 CLOSE C_TBL_COL;
 
--   DBMS_OUTPUT.PUT_LINE (LTRIM(V_col,','));
V_SQL := 
  'INSERT INTO ' || V_tbl ||
      ' SELECT ' || LTRIM(V_col,',')
           || ' FROM REMOTE_DB' || V_tbl || 
              '@prd.org V1  WHERE V1.LOAD_NUMBER > 2';

        DBMS_OUTPUT.PUT_LINE (V_SQL);  
COMMIT;     
END LOOP;
END; 

SELECT    'insert into '
       || table_name
       || ' select * from '
       || table_name
       || '@database_link;'
  FROM user_tables;