Insert bulk records in to remote database (dblink)

Question

I want to insert huge records from different tables in to a destination remote table 'Audition_Detail' using DBLINK - @FMATLINK. I have used Bulk collect, but its throwing errors. I have gone through some links too:

Overcoming the restriction on bulk inserts over a database link

PLS-00394: Wrong number of values in the INTO list of a fetch statement

The code is as follows:

DECLARE
    TYPE FETCH_ARRAY IS TABLE OF AUDITION_DETAIL@FMATLINK%ROWTYPE;   
    A_DATA FETCH_ARRAY;

    CURSOR A_CUR IS
        --------------------------------------------------------Address1--------------------------------------------------------------------------

        SELECT A.PARTY_SITE_NUMBER FMAT_FMATID, B.ZADDRESSFMATID F4F_FMATID,
        C.ADDRESS1 FMAT_VALUE, B.STREET F4F_VALUE , 'ADDRESS1' 
        FROM APPS.HZ_PARTY_SITES@FMATLINK A , f4f_corporateaccount B , APPS.HZ_LOCATIONS@FMATLINK C
        WHERE 1=1
        AND B.ROLECODETEXT = 'Site Account'
        AND A.PARTY_SITE_NUMBER = B.ZADDRESSFMATID
        AND A.STATUS = 'A'

        UNION ALL 
        ------------------------------------------------------Address2-----------------------------------------------------------------------------

        SELECT A.PARTY_SITE_NUMBER FMAT_FMATID, B.ZADDRESSFMATID F4F_FMATID,
        C.ADDRESS2 FMAT_VALUE, B.addressline1 F4F_VALUE , 'ADDRESS2'  
        FROM APPS.HZ_PARTY_SITES@FMATLINK A , f4f_corporateaccount B , APPS.HZ_LOCATIONS@FMATLINK C
        WHERE 1=1
        AND B.ROLECODETEXT = 'Site Account'
        AND A.PARTY_SITE_NUMBER = B.ZADDRESSFMATID
        AND A.STATUS = 'A'


BEGIN   
    OPEN A_CUR;
    LOOP                      
        FETCH A_CUR BULK COLLECT INTO A_DATA LIMIT 20; 
            FORALL IN 1..A_DATA.COUNT     
            INSERT INTO AUDITION_DETAIL@FMATLINK VALUES A_DATA(i);

        EXIT WHEN A_CUR%NOTFOUND;
    END LOOP;
    CLOSE A_CUR;
    COMMIT;
END;

Error report -

ORA-06550: line 39, column 3:

PLS-00394: wrong number of values in the INTO list of a FETCH statement

ORA-06550: line 39, column 3:

PL/SQL: SQL Statement ignored

ORA-06550: line 40, column 4:

PLS-00739: FORALL INSERT/UPDATE/DELETE not supported on remote tables 06550. 00000 - "line %s, column %s:\n%s"

*Cause: Usually a PL/SQL compilation error. *Action:

Answer 1

The error message seems clear enough :

FORALL INSERT/UPDATE/DELETE not supported on remote tables.

Indeed you link to another question which explains that this is an implementation restriction. PL/SQL does not allow us to use FORALL statements across database links and that is that.

Fortunately you don't need to use bulk collect and FORALL in your code. A simple INSERT INTO .... SELECT statement should see you right:

INSERT INTO AUDITION_DETAIL@FMATLINK 
    SELECT A.PARTY_SITE_NUMBER FMAT_FMATID, B.ZADDRESSFMATID F4F_FMATID,
    C.ADDRESS1 FMAT_VALUE, B.STREET F4F_VALUE , 'ADDRESS1' 
    FROM APPS.HZ_PARTY_SITES@FMATLINK A , f4f_corporateaccount B , APPS.HZ_LOCATIONS@FMATLINK C
    WHERE 1=1
    AND B.ROLECODETEXT = 'Site Account'
    AND A.PARTY_SITE_NUMBER = B.ZADDRESSFMATID
    AND A.STATUS = 'A'

    UNION ALL 
    ------------------------------------------------------Address2-----------------------------------------------------------------------------

    SELECT A.PARTY_SITE_NUMBER FMAT_FMATID, B.ZADDRESSFMATID F4F_FMATID,
    C.ADDRESS2 FMAT_VALUE, B.addressline1 F4F_VALUE , 'ADDRESS2'  
    FROM APPS.HZ_PARTY_SITES@FMATLINK A , f4f_corporateaccount B , APPS.HZ_LOCATIONS@FMATLINK C
    WHERE 1=1
    AND B.ROLECODETEXT = 'Site Account'
    AND A.PARTY_SITE_NUMBER = B.ZADDRESSFMATID
    AND A.STATUS = 'A'

---

Your code doesn't use the explicit ANSI 92 join syntax and you have scrunched up the code so it's hard to read. Consequently it is easy to miss that you haven't written a join condition for APPS.HZ_LOCATIONS@FMATLINK C. So both subqueries will produce a Cartesian Product for all records in C. You probably don't want this.

SELECT A.PARTY_SITE_NUMBER as FMAT_FMATID
        , B.ZADDRESSFMATID as F4F_FMATID
        , C.ADDRESS2 as FMAT_VALUE
        , B.addressline1 as F4F_VALUE
        , 'ADDRESS2'  
FROM APPS.HZ_PARTY_SITES@FMATLINK A
      inner join  f4f_corporateaccount B on A.PARTY_SITE_NUMBER = B.ZADDRESSFMATID
      inner join  APPS.HZ_LOCATIONS@FMATLINK C on ** something goes here **
WHERE  B.ROLECODETEXT = 'Site Account'
AND A.STATUS = 'A'

Easier to understand, easy to spot the missing join. Readability is a feature.