Oracle Database Error ORA-02074 ORA-02074 cannot string in a distributed transaction 问题 A commit or rollback was attempted from session other than the parent of a distributed transaction. 解决 Only commit or rollback from the parent session.
BEGIN ----步骤4关闭DBLINK V_JOB_ID := 8010002000904; V_JOB_NAME := '步骤4关闭DBLINK'; V_JOB_BT := SYSTIMESTAMP; V_STMT := 'ALTER SESSION CLOSE DATABASE LINK SZDW'; EXECUTE IMMEDIATE V_STMT; V_JOB_ROWS := SQL%ROWCOUNT; --步骤监控 RAHEAP.AU_ETL_STEP_MONTH_LOG(IN_STAT_MONTH,V_SP_JOB_ID, -1,V_JOB_ID, 4, V_JOB_BT,SYSTIMESTAMP, V_JOB_NAME, V_JOB_ROWS, '---OK----',NULL,NULL,NULL,V_STMT); EXCEPTION WHEN DBLINK_NOT_OPEN THEN V_SQLCODE := SQLCODE; V_SQLMSG := SQLERRM; V_JOB_ROWS := -1; RAHEAP.AU_ETL_STEP_MONTH_LOG(IN_STAT_MONTH,V_SP_JOB_ID, -1, V_JOB_ID,2,V_JOB_BT,SYSTIMESTAMP,V_JOB_NAME, V_JOB_ROWS,V_SQLMSG,2,V_SQLCODE, NULL, 'DBLINK NOT OPEN'); WHEN DBLINK_NO_OPEN THEN V_SQLCODE := SQLCODE; V_SQLMSG := SQLERRM; V_JOB_ROWS := -1; RAHEAP.AU_ETL_STEP_MONTH_LOG(IN_STAT_MONTH,V_SP_JOB_ID,-1,V_JOB_ID,2,V_JOB_BT,SYSTIMESTAMP, V_JOB_NAME, V_JOB_ROWS,V_SQLMSG, 2,V_SQLCODE,NULL,'DBLINK NOT OPEN'); COMMIT; END; ----步骤4向表RAHEBS.PROD_BO_ORDER插入数据 V_JOB_ID := 8010002000904; V_JOB_NAME := '步骤4向表RAHEBS.PROD_BO_ORDER插入数据'; V_JOB_BT := SYSTIMESTAMP; V_STMT := 'INSERT INTO RAHEBS.PROD_BO_ORDER (GENERATE_DATE, BO_ID, PROD_ID, OL_ID, BO_ACTION_TYPE_CD, BO_ACTION_TYPE_NAME, COMPLETE_DT, SS_STAFF_ID, SS_STAFF_NAME, SALE_STAFF_NUMBER, STAFF_NAME, CHANNEL_ID, CHANNEL_NAME, CHANNEL1_ID, CHANNEL1_NAME) SELECT GENERATE_DATE, BO_ID, PROD_ID, OL_ID, BO_ACTION_TYPE_CD, BO_ACTION_TYPE_NAME, COMPLETE_DT, SS_STAFF_ID, SS_STAFF_NAME, SALE_STAFF_NUMBER, STAFF_NAME, CHANNEL_ID, CHANNEL_NAME, CHANNEL1_ID, CHANNEL1_NAME FROM FACT_CO_BO_LIST_'|| TO_CHAR(IN_STAT_MONTH)||'@SZDW FACT_T WHERE FACT_T.BO_ACTION_TYPE_NAME NOT IN(''服务信息变动'',''退订'',''订购'')'; EXECUTE IMMEDIATE V_STMT; V_JOB_ROWS := SQL%ROWCOUNT; --步骤监控 RAHEAP.AU_ETL_STEP_MONTH_LOG(IN_STAT_MONTH,V_SP_JOB_ID,-1,V_JOB_ID,4,V_JOB_BT, SYSTIMESTAMP,V_JOB_NAME,V_JOB_ROWS,'---OK----',NULL,NULL,NULL, V_STMT); COMMIT; execute immediate 'alter session close database link szdw'; --session回收 COMMIT;
ORA-02074 cannot string in a distributed transaction
问题
A commit or rollback was attempted from session other than the parent of a distributed transaction.
解决
Only commit or rollback from the parent session.
----步骤4关闭DBLINK
V_JOB_ID := 8010002000904;
V_JOB_NAME := '步骤4关闭DBLINK';
V_JOB_BT := SYSTIMESTAMP;
V_STMT := 'ALTER SESSION CLOSE DATABASE LINK SZDW';
EXECUTE IMMEDIATE V_STMT; V_JOB_ROWS := SQL%ROWCOUNT;
--步骤监控
RAHEAP.AU_ETL_STEP_MONTH_LOG(IN_STAT_MONTH,V_SP_JOB_ID, -1,V_JOB_ID, 4, V_JOB_BT,SYSTIMESTAMP,
V_JOB_NAME, V_JOB_ROWS, '---OK----',NULL,NULL,NULL,V_STMT);
EXCEPTION
WHEN DBLINK_NOT_OPEN THEN
V_SQLCODE := SQLCODE;
V_SQLMSG := SQLERRM;
V_JOB_ROWS := -1;
RAHEAP.AU_ETL_STEP_MONTH_LOG(IN_STAT_MONTH,V_SP_JOB_ID, -1, V_JOB_ID,2,V_JOB_BT,SYSTIMESTAMP,V_JOB_NAME, V_JOB_ROWS,V_SQLMSG,2,V_SQLCODE, NULL, 'DBLINK NOT OPEN'); WHEN DBLINK_NO_OPEN THEN
V_SQLCODE := SQLCODE;
V_SQLMSG := SQLERRM;
V_JOB_ROWS := -1;
RAHEAP.AU_ETL_STEP_MONTH_LOG(IN_STAT_MONTH,V_SP_JOB_ID,-1,V_JOB_ID,2,V_JOB_BT,SYSTIMESTAMP,
V_JOB_NAME, V_JOB_ROWS,V_SQLMSG, 2,V_SQLCODE,NULL,'DBLINK NOT OPEN');
COMMIT;
END; ----步骤4向表RAHEBS.PROD_BO_ORDER插入数据
V_JOB_ID := 8010002000904;
V_JOB_NAME := '步骤4向表RAHEBS.PROD_BO_ORDER插入数据';
V_JOB_BT := SYSTIMESTAMP;
V_STMT := 'INSERT INTO RAHEBS.PROD_BO_ORDER
(GENERATE_DATE,
BO_ID,
PROD_ID,
OL_ID,
BO_ACTION_TYPE_CD,
BO_ACTION_TYPE_NAME,
COMPLETE_DT,
SS_STAFF_ID,
SS_STAFF_NAME,
SALE_STAFF_NUMBER,
STAFF_NAME,
CHANNEL_ID,
CHANNEL_NAME,
CHANNEL1_ID,
CHANNEL1_NAME)
SELECT GENERATE_DATE,
BO_ID,
PROD_ID,
OL_ID,
BO_ACTION_TYPE_CD,
BO_ACTION_TYPE_NAME,
COMPLETE_DT,
SS_STAFF_ID,
SS_STAFF_NAME,
SALE_STAFF_NUMBER,
STAFF_NAME,
CHANNEL_ID,
CHANNEL_NAME,
CHANNEL1_ID,
CHANNEL1_NAME
FROM FACT_CO_BO_LIST_'|| TO_CHAR(IN_STAT_MONTH)||'@SZDW FACT_T
WHERE FACT_T.BO_ACTION_TYPE_NAME NOT IN(''服务信息变动'',''退订'',''订购'')';
EXECUTE IMMEDIATE V_STMT;
V_JOB_ROWS := SQL%ROWCOUNT;
--步骤监控
RAHEAP.AU_ETL_STEP_MONTH_LOG(IN_STAT_MONTH,V_SP_JOB_ID,-1,V_JOB_ID,4,V_JOB_BT, SYSTIMESTAMP,V_JOB_NAME,V_JOB_ROWS,'---OK----',NULL,NULL,NULL, V_STMT); COMMIT;
execute immediate 'alter session close database link szdw'; --session回收
COMMIT;