今天在用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;
BEGIN
FOR 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,
再查询了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;
BEGIN
FOR 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;