oracle merge into 报 ora-02064错误

今天在用Oracle的merge into同步数据的时候，遇到了ora-02064：distributed operation not supported，这个错误的描述是说merge into不支持分布式事务操作。

CREATE OR REPLACE PROCEDURE PROC_BLACKLIST_DATA_SYNC(v_date VARCHAR2) AS /******************************************************************************     NAME:     PROC_BLACKLIST_DATA_SYNC     PURPOSE:  黑名单数据同步     PARAMETERS:     Name        Description     ---------       ------------------------------------------------------------------     v_date    需要同步的数据日期     REVISIONS:     Ver        Date        Author           Description     ---------  ----------  ---------------  ------------------------------------     1.0        2016-04-08     jay     1. Created this procedure.  ******************************************************************************/  v_errmsg varchar2(255);  CURSOR C_TEMPBLACKLIST IS --黑名单表    SELECT DISTINCT IRFCNUM      FROM SAPDT_WAKBL     WHERE ISDO IS NULL       AND LOGDATE BETWEEN           TO_DATE(V_DATE || '00:00:00', 'yyyy-mm-ddhh24:mi:ss') AND           TO_DATE(V_DATE || '23:59:59', 'yyyy-mm-ddhh24:mi:ss')     ORDER BY IRFCNUM;    C_TEMPBLACKLIST_REC C_TEMPBLACKLIST%ROWTYPE;--PRAGMA AUTONOMOUS_TRANSACTION;BEGINFOR C_TEMPBLACKLIST_REC IN C_TEMPBLACKLIST LOOP MERGE INTO SAP_WAKBLL@POSDB A  USING (SELECT * FROM SAPDT_WAKBL  WHERE (ISDO = 0 or isdo is null)       AND IRFCNUM = C_TEMPBLACKLIST_REC.IRFCNUM       AND LOGDATE BETWEEN           to_date(v_date || '00:00:00', 'yyyy-mm-ddhh24:mi:ss') AND           to_date(v_date || '23:59:59', 'yyyy-mm-ddhh24:mi:ss')  ) B  on (A.AKTNR = B.AKTNR AND A.AKTYP = B.AKTYP AND A.OBTYP = B.OBTYP AND A.VALUE = B.VALUE)   --SAP_WAKBLL@POSDB只有4个字段，如果都已经匹配则没有其它字段可以更新

  WHEN NOT MATCHED THEN    INSERT    VALUES    (  B.AKTNR,       B.AKTYP,       B.OBTYP,       B.VALUE, B.IRFCNUM);        UPDATE SAPDT_WAKBL T SET T.ISDO = 1 WHERE (ISDO = 0 or isdo is null)         AND IRFCNUM = C_TEMPBLACKLIST_REC.IRFCNUM       AND LOGDATE BETWEEN           to_date(v_date || '00:00:00', 'yyyy-mm-ddhh24:mi:ss') AND           to_date(v_date || '23:59:59', 'yyyy-mm-ddhh24:mi:ss');         COMMIT;END LOOP;EXCEPTION  WHEN OTHERS THEN    V_ERRMSG := TRIM(SQLERRM);    ROLLBACK;    RAISE;END;<div> </div>

 

百度了下网上说是10g的一个bug，

http://space.itpub.net/637736/viewspace-312612

再查询了oracle的官方文档却说支持这种操作： 

You can specify this clause(这里是说match_insert_clause) by itself or with the merge_update_clause. If you

specify both, then they can be in either order.

解决的办法是：加上标红的语句加一个无实际意义的merge_update_clause,严格按照oracle的标准语法走

 

CREATE OR REPLACE PROCEDURE PROC_BLACKLIST_DATA_SYNC(v_date VARCHAR2) AS /******************************************************************************     NAME:     PROC_BLACKLIST_DATA_SYNC     PURPOSE:  黑名单数据同步     PARAMETERS:     Name        Description     ---------       ------------------------------------------------------------------     v_date    需要同步的数据日期     REVISIONS:     Ver        Date        Author           Description     ---------  ----------  ---------------  ------------------------------------     1.0        2016-04-08     jay     1. Created this procedure.  ******************************************************************************/  v_errmsg varchar2(255);  CURSOR C_TEMPBLACKLIST IS --黑名单表    SELECT DISTINCT IRFCNUM      FROM SAPDT_WAKBL     WHERE ISDO IS NULL       AND LOGDATE BETWEEN           TO_DATE(V_DATE || '00:00:00', 'yyyy-mm-ddhh24:mi:ss') AND           TO_DATE(V_DATE || '23:59:59', 'yyyy-mm-ddhh24:mi:ss')     ORDER BY IRFCNUM;    C_TEMPBLACKLIST_REC C_TEMPBLACKLIST%ROWTYPE;--PRAGMA AUTONOMOUS_TRANSACTION;BEGINFOR C_TEMPBLACKLIST_REC IN C_TEMPBLACKLIST LOOP MERGE INTO SAP_WAKBLL@POSDB A  USING (SELECT * FROM SAPDT_WAKBL  WHERE (ISDO = 0 or isdo is null)       AND IRFCNUM = C_TEMPBLACKLIST_REC.IRFCNUM       AND LOGDATE BETWEEN           to_date(v_date || '00:00:00', 'yyyy-mm-ddhh24:mi:ss') AND           to_date(v_date || '23:59:59', 'yyyy-mm-ddhh24:mi:ss')  ) B  on (A.AKTNR = B.AKTNR AND A.AKTYP = B.AKTYP AND A.OBTYP = B.OBTYP AND A.VALUE = B.VALUE)   --SAP_WAKBLL@POSDB只有4个字段，如果都已经匹配则没有其它字段可以更新 <span style="color:#ff0000;"> </span><span style="color:#ff0000;"><strong>WHEN MATCHED THEN UPDATE SET A.IRFCNUM = B.IRFCNUM</strong></span>    WHEN NOT MATCHED THEN    INSERT    VALUES    (  B.AKTNR,       B.AKTYP,       B.OBTYP,       B.VALUE, B.IRFCNUM);        UPDATE SAPDT_WAKBL T SET T.ISDO = 1 WHERE (ISDO = 0 or isdo is null)         AND IRFCNUM = C_TEMPBLACKLIST_REC.IRFCNUM       AND LOGDATE BETWEEN           to_date(v_date || '00:00:00', 'yyyy-mm-ddhh24:mi:ss') AND           to_date(v_date || '23:59:59', 'yyyy-mm-ddhh24:mi:ss');         COMMIT;END LOOP;EXCEPTION  WHEN OTHERS THEN    V_ERRMSG := TRIM(SQLERRM);    ROLLBACK;    RAISE;END;