使用批量收集将批量记录插入到远程数据库(dblink)中 [英] Insert bulk records in to remote database (dblink) using Bulk Collect

问题描述

我想使用DBLINK-@FMATLINK将来自不同表的大量记录插入到目标远程表'Audition_Detail'中.我使用了批量收集，但是它引发了错误.我也浏览了一些链接:

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:

>克服对数据库中批量插入的限制链接

PLS -00394:提取语句的INTO列表中的值数量错误

代码如下:

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;

错误报告-

ORA-06550:第39行，第3列:

ORA-06550: line 39, column 3:

PLS-00394:FETCH的INTO列表中的值数量错误 声明

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

ORA-06550:第39行，第3列:

ORA-06550: line 39, column 3:

PL/SQL:忽略了SQL语句

PL/SQL: SQL Statement ignored

ORA-06550:第40行，第4列:

ORA-06550: line 40, column 4:

PLS-00739:远程表不支持FORALL INSERT/UPDATE/DELETE 06550. 00000-％s行，％s列:\ n％s"

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

*原因:通常是PL/SQL编译错误. *动作:

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

推荐答案

错误消息似乎足够清楚:

The error message seems clear enough :

在远程表上不支持的全部插入/更新/删除操作.

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

实际上，您链接到另一个问题，该问题解释说这是一个实现限制. PL/SQL不允许我们在数据库链接之间使用FORALL语句，就是这样.

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.

幸运的是，您无需在代码中使用批量收集和FORALL.一个简单的INSERT INTO .... SELECT语句应该可以正确显示:

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'

---

您的代码没有使用显式的ANSI 92连接语法，并且您已经压缩了代码，因此很难阅读.因此，很容易错过尚未为APPS.HZ_LOCATIONS@FMATLINK C编写连接条件的情况.因此，两个子查询都将为C中的所有记录生成笛卡尔乘积.您可能不想要这个.

---

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.

这篇关于使用批量收集将批量记录插入到远程数据库(dblink)中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！