title: Insert or Update in an DBLINK as a Loop
body: <p>I have a Row, which i wannt to Insert (or Update if the ID is already used) from another Schema with an DBLINK. This Table has many FKs, so i need to Update or Insert(if doesnt exist) around 20 Tables.</p>
  <p>I tried it by writting all needed Rows from the different Tables from the DBLINK into Variables and then checked if the Targeted Tables had this rows already. Im sure that there is a much easier way to do it. A loop where the tables and their rowtypes are saved in an Collection with execute immdediate for example, but i couldnt find anything.</p>
  <p>This example is like 20 times in my Code and im sure that there is an easier way to do it then writting this over and over.</p>
  <pre><code>PROCEDURE Test_copy (pi_id IN NUMBER) IS
  
  V_REC_Table1   Table1%ROWTYPE;
  V_REC_Table2   Table2%ROWTYPE;
  V_REC_Table3   Table3%ROWTYPE;
  V_REC_Table4   Table4%ROWTYPE;
  V_REC_Table5   Table5%ROWTYPE;
  V_REC_Table6   Table6%ROWTYPE;
  V_REC_Table7   Table7%ROWTYPE;
  V_REC_Table8   Table8%ROWTYPE;
  V_REC_Table9   Table9%ROWTYPE;
  V_REC_Table10  Table10%ROWTYPE;
  
  
  V_REC_Table1_exists   Table1%ROWTYPE;
  V_REC_Table2_exists   Table2%ROWTYPE;
  V_REC_Table3_exists   Table3%ROWTYPE;
  V_REC_Table4_exists   Table4%ROWTYPE;
  V_REC_Table5_exists   Table5%ROWTYPE;
  V_REC_Table6_exists   Table6%ROWTYPE;
  V_REC_Table7_exists   Table7%ROWTYPE;
  V_REC_Table8_exists   Table8%ROWTYPE;
  V_REC_Table9_exists   Table9%ROWTYPE;
  V_REC_Table10_exists  Table10%ROWTYPE;
  v_sql VARCHAR2(2000);
  
  Begin
      v_sql := 'SELECT * FROM Table1@dblink WHERE ID = '||pi_id;
      EXECUTE IMMEDIATE v_sql INTO V_REC_DBLINK_Table1;
  Exception
      when no_data_found then
      Raise_application_Error(-20001, 'ID doesnt exist');
  END;
  
  Begin
      v_sql := 'SELECT * FROM Table2@dblink WHERE ID = '||V_REC_Table1.T2_ID;
      EXECUTE IMMEDIATE v_sql INTO V_REC_DBLINK_Table2;
  Exception
      when no_data_found then
      Raise_application_Error(-20001, 'ID doesnt exist');
  END;
  (and so on)
  .
  .
  .
  BEGIN
          v_sql := 'SELECT * FROM Table1 WHERE ID = '||V_REC_DBLINK_TABLE1.ID;
          EXECUTE IMMEDIATE v_sql INTO V_REC_DBLINK_TABLE1_EXISTS;
          
      UPDATE TABLE1
          SET ID          = V_REC_DBLINK_Table1.ID,
              NAME        = V_REC_DBLINK_Table1.NAME,
              DESCRIPTION = V_REC_DBLINK_Table1.DESCRIPTION
              NOTE        = V_REC_DBLINK_Table1.NOTE
              NUMBER      = V_REC_DBLINK_Table1.NUMBER
              ADDRESS     = V_REC_DBLINK_Table1.ADDRESS
          WHERE ID = V_REC_DBLINK_TABLE1.ID;
          
      EXCEPTION
          when no_data_found then
              INSERT INTO TABLE1
              (ID, NAME, DESCRIPTION, NOTE, NUMBER, ADDRESS)
              Values(V_REC_DBLINK_TABLE1.ID
                    ,V_REC_DBLINK_TABLE1.NAME
                    ,V_REC_DBLINK_TABLE1.DESCRIPTION
                    ,V_REC_DBLINK_TABLE1.NOTE
                    ,V_REC_DBLINK_TABLE1.NUMBER
                    ,V_REC_DBLINK_TABLE1.ADDRESS);
      END;
  
  BEGIN
          v_sql := 'SELECT * FROM Table2 WHERE ID = '||V_REC_DBLINK_TABLE2.ID;
          EXECUTE IMMEDIATE v_sql INTO V_REC_DBLINK_TABLE2_EXISTS;
          
      UPDATE TABLE2
          SET ID          = V_REC_DBLINK_Table2.ID,
              NAME        = V_REC_DBLINK_Table2.NAME,
              DESCRIPTION = V_REC_DBLINK_Table2.DESCRIPTION
          WHERE ID = V_REC_DBLINK_TABLE2.ID;
          
      EXCEPTION
          when no_data_found then
              INSERT INTO TABLE2
              (ID, NAME, DESCRIPTION)
              Values(V_REC_DBLINK_TABLE2.ID
                    ,V_REC_DBLINK_TABLE2.NAME
                    ,V_REC_DBLINK_TABLE2.DESCRIPTION);
      END;
      (and so on for all Tables via DBLINK)
  </code></pre>
  <p>Any suggestions? I just want to do that in a shorter way(maybe via Collection and loop).</p>