进来项目设计到与sqlserver2000进行接口查询数据时遇到此问题oracle采用透明网关,配置等都没问题,但在执行过程时出现了如题问题
过程如下:CREATE OR REPLACE PROCEDURE PROC_ACCOUNT_PUB
AS
CURSOR CUR_ACCOUNT_PUB IS SELECT ACCOUNT_DT,METER_ADDR,BILL_DT,ACCOUNT_BALANCE,DEAL_FLAG,INPUT_DT
FROM T_CUS_BALANCE@TEST WHERE DEAL_FLAG=0 ORDER BY METER_ADDR,ACCOUNT_DT;
--R_ACCOUNT_PUB CUR_ACCOUNT_PUB%ROWTYPE;
V_COLL_OBJ_ID NUMBER(16);
V_CONS_ID NUMBER(16);
V_MP_ID NUMBER(16);
V_ACCOUNT_DT VARCHAR2(16);
V_METER_ADDR VARCHAR2(32);
V_BILL_DT VARCHAR2(16);
V_ACCOUNT_BALANCE NUMBER(10,2);
V_DEAL_FLAG NUMBER(1);
V_INPUT_DT VARCHAR2(16);
BEGIN
OPEN CUR_ACCOUNT_PUB;
LOOP
FETCH CUR_ACCOUNT_PUB INTO V_ACCOUNT_DT,V_METER_ADDR,V_BILL_DT,V_ACCOUNT_BALANCE,V_DEAL_FLAG,V_INPUT_DT;
EXIT WHEN CUR_ACCOUNT_PUB%NOTFOUND;
SELECT RCO.COLL_OBJ_ID INTO V_COLL_OBJ_ID FROM TRUN_COLL_OBJ RCO,TCUS_METER CM WHERE CM.METER_ID=RCO.METER_ID AND CM.METER_ASSET=V_METER_ADDR;
SELECT COMR.MP_ID INTO V_MP_ID FROM TCUS_OBJ_MP_RELA COMR WHERE COMR.COLL_OBJ_ID=V_COLL_OBJ_ID;
SELECT RCO.CONS_ID INTO V_CONS_ID FROM TRUN_COLL_OBJ RCO,TCUS_METER CM
WHERE CM.METER_ID=RCO.METER_ID AND CM.METER_ASSET=V_METER_ADDR;
UPDATE TPAY_ACCOUNT TA SET TA.BALANCE=V_ACCOUNT_BALANCE WHERE TA.CONS_ID=V_CONS_ID;
MERGE INTO TCALC_MP_BALANCE_TIME CMBT USING DUAL ON (CMBT.COLL_OBJ_ID=V_COLL_OBJ_ID)
WHEN NOT MATCHED THEN
INSERT(COLL_OBJ_ID,MP_ID,BALANCE_DATE,CALC_TIME,END_TIME,BEGIN_TIME,VERSION)
VALUES(V_COLL_OBJ_ID,V_MP_ID,TO_DATE(V_ACCOUNT_DT,'YYYY-MM-DD HH24:MI:SS'),
TO_DATE(V_BILL_DT,'YYYY-MM-DD HH24:MI:SS'),TO_DATE(V_BILL_DT,'YYYY-MM-DD HH24:MI:SS'),
ADD_MONTHS(TO_DATE(V_BILL_DT,'YYYY-MM-DD HH24:MI:SS'), -1),1)
WHEN MATCHED THEN
UPDATE SET BALANCE_DATE=TO_DATE(V_ACCOUNT_DT,'YYYY-MM-DD HH24:MI:SS'),
CALC_TIME=TO_DATE(V_BILL_DT,'YYYY-MM-DD HH24:MI:SS'),
END_TIME=TO_DATE(V_BILL_DT,'YYYY-MM-DD HH24:MI:SS')
WHERE COLL_OBJ_ID=V_COLL_OBJ_ID;
(1)--UPDATE T_CUS_BALANCE@TEST SET DEAL_FLAG=1 WHERE METER_ADDR=V_METER_ADDR AND ACCOUNT_DT=V_ACCOUNT_DT;
(1)--COMMIT;
END LOOP;
CLOSE CUR_ACCOUNT_PUB;
COMMIT;
UPDATE T_CUS_BALANCE@TEST SET DEAL_FLAG=1;
COMMIT;
END;过程尝试查询远程数据库数据为本地数据进行更新并同时删除或更改远程数据库内容
如果采用1方式,则会报错
ORA-02054: 事务处理 9.40.128827 有问题
ORA-28500: 连接 ORACLE 到非 Oracle 系统时返回此信息:
[Transparent gateway for MSSQL]trying all tdps, ignoring failure
此时只能通过commit force 或rollback force进行清除如果采用目前方式则难以保证事物的一致性,可能出现修改了还没有处理的数据请教高手怎么避免这种错误又能保证事物的一致性
过程如下:CREATE OR REPLACE PROCEDURE PROC_ACCOUNT_PUB
AS
CURSOR CUR_ACCOUNT_PUB IS SELECT ACCOUNT_DT,METER_ADDR,BILL_DT,ACCOUNT_BALANCE,DEAL_FLAG,INPUT_DT
FROM T_CUS_BALANCE@TEST WHERE DEAL_FLAG=0 ORDER BY METER_ADDR,ACCOUNT_DT;
--R_ACCOUNT_PUB CUR_ACCOUNT_PUB%ROWTYPE;
V_COLL_OBJ_ID NUMBER(16);
V_CONS_ID NUMBER(16);
V_MP_ID NUMBER(16);
V_ACCOUNT_DT VARCHAR2(16);
V_METER_ADDR VARCHAR2(32);
V_BILL_DT VARCHAR2(16);
V_ACCOUNT_BALANCE NUMBER(10,2);
V_DEAL_FLAG NUMBER(1);
V_INPUT_DT VARCHAR2(16);
BEGIN
OPEN CUR_ACCOUNT_PUB;
LOOP
FETCH CUR_ACCOUNT_PUB INTO V_ACCOUNT_DT,V_METER_ADDR,V_BILL_DT,V_ACCOUNT_BALANCE,V_DEAL_FLAG,V_INPUT_DT;
EXIT WHEN CUR_ACCOUNT_PUB%NOTFOUND;
SELECT RCO.COLL_OBJ_ID INTO V_COLL_OBJ_ID FROM TRUN_COLL_OBJ RCO,TCUS_METER CM WHERE CM.METER_ID=RCO.METER_ID AND CM.METER_ASSET=V_METER_ADDR;
SELECT COMR.MP_ID INTO V_MP_ID FROM TCUS_OBJ_MP_RELA COMR WHERE COMR.COLL_OBJ_ID=V_COLL_OBJ_ID;
SELECT RCO.CONS_ID INTO V_CONS_ID FROM TRUN_COLL_OBJ RCO,TCUS_METER CM
WHERE CM.METER_ID=RCO.METER_ID AND CM.METER_ASSET=V_METER_ADDR;
UPDATE TPAY_ACCOUNT TA SET TA.BALANCE=V_ACCOUNT_BALANCE WHERE TA.CONS_ID=V_CONS_ID;
MERGE INTO TCALC_MP_BALANCE_TIME CMBT USING DUAL ON (CMBT.COLL_OBJ_ID=V_COLL_OBJ_ID)
WHEN NOT MATCHED THEN
INSERT(COLL_OBJ_ID,MP_ID,BALANCE_DATE,CALC_TIME,END_TIME,BEGIN_TIME,VERSION)
VALUES(V_COLL_OBJ_ID,V_MP_ID,TO_DATE(V_ACCOUNT_DT,'YYYY-MM-DD HH24:MI:SS'),
TO_DATE(V_BILL_DT,'YYYY-MM-DD HH24:MI:SS'),TO_DATE(V_BILL_DT,'YYYY-MM-DD HH24:MI:SS'),
ADD_MONTHS(TO_DATE(V_BILL_DT,'YYYY-MM-DD HH24:MI:SS'), -1),1)
WHEN MATCHED THEN
UPDATE SET BALANCE_DATE=TO_DATE(V_ACCOUNT_DT,'YYYY-MM-DD HH24:MI:SS'),
CALC_TIME=TO_DATE(V_BILL_DT,'YYYY-MM-DD HH24:MI:SS'),
END_TIME=TO_DATE(V_BILL_DT,'YYYY-MM-DD HH24:MI:SS')
WHERE COLL_OBJ_ID=V_COLL_OBJ_ID;
(1)--UPDATE T_CUS_BALANCE@TEST SET DEAL_FLAG=1 WHERE METER_ADDR=V_METER_ADDR AND ACCOUNT_DT=V_ACCOUNT_DT;
(1)--COMMIT;
END LOOP;
CLOSE CUR_ACCOUNT_PUB;
COMMIT;
UPDATE T_CUS_BALANCE@TEST SET DEAL_FLAG=1;
COMMIT;
END;过程尝试查询远程数据库数据为本地数据进行更新并同时删除或更改远程数据库内容
如果采用1方式,则会报错
ORA-02054: 事务处理 9.40.128827 有问题
ORA-28500: 连接 ORACLE 到非 Oracle 系统时返回此信息:
[Transparent gateway for MSSQL]trying all tdps, ignoring failure
此时只能通过commit force 或rollback force进行清除如果采用目前方式则难以保证事物的一致性,可能出现修改了还没有处理的数据请教高手怎么避免这种错误又能保证事物的一致性
出现事务错误是无法避免的,这在数据库操作中是很常见的事情。首先对远端执行UPDATE,当失败的时候就进入下一条记录的处理就可以了,因为SQL没通成功执行,所以也不需要对事务进行commit或者rollback。