create database link TIPS
connect to DB_TIPS identified by CSSGDLT
using '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 160.16.56.26)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = tipssc)
)
)';
xjds33 11:54:25
CREATE OR REPLACE PROCEDURE P_ETL_DJXX( -- i_BeginTime DATE, --开始时间
i_EndTime DATE, --终止时间
o_result OUT VARCHAR2 --执行结果:'0' 正确执行; 否则,返回ORACLE错误提示
) AS
v_Name VARCHAR2(40):='P_ETL_DJXX';
v_exe_status INTEGER; -- 执行状态标志
v_KZSJ DATE; -- 下次数据同步起点时间
v_Exe_Info VARCHAR2(255); -- 执行信息
v_Excpet_Info VARCHAR2(255); -- 异常信息
v_StartTime DATE;
v_BeginTime DATE; -- 调整后的开始时间
v_EndTime DATE;BEGIN
--0 前置处理控制信息;
v_Excpet_Info:='代码段0';
v_StartTime:=SYSDATE; -- 执行开始时间
SELECT ZXZT_BJ,QSSJ INTO v_exe_status,v_KZSJ FROM db_zgxt.t_tbxt_KZXX WHERE czmc=v_Name;
IF v_exe_status=1 THEN
o_result:='该过程正在被其他用户调用,请等待释放后执行。';
dbms_output.put_line(o_result);
RETURN;
ELSE--(KZ_SJ=0,-1)
v_BeginTime:=v_KZSJ;
v_EndTime:=i_EndTime;
v_Exe_Info := '该过程正在被调用'||TO_CHAR(v_StartTime,'YYYY-MM-DD HH24:MI:SS');
-- 置控制信息的标志为 '正在执行’
UPDATE db_zgxt.t_tbxt_KZXX SET ZXZT_BJ=1,YCXX=v_Exe_Info WHERE czmc=v_Name;
COMMIT;
END IF;
--1 同步代码表
v_Excpet_Info := '1.1 INSERT t_dj_cc:';
DBMS_OUTPUT.put_line('BeginTime: '||TO_CHAR(SYSDATE,'yyyymmdd hh24:mi:ss')); -- 新增加的 t_fp_nsrfpkc DELETE t_dj_cc NOLOGGING where (CCDJLSH, YX_QSRQ) in (SELECT CCDJLSH, YX_QSRQ FROM djcx_t_dj_cc T WHERE T.LR_SJ>=v_BeginTime AND T.LR_SJ<v_EndTime);
INSERT INTO DB_zgxt.t_dj_cc NOLOGGING SELECT * FROM djcx_t_dj_cc T WHERE t.gljg_dm like '26502%' and T.LR_SJ>=v_BeginTime AND T.LR_SJ<v_EndTime;
DBMS_OUTPUT.put_line('t_dj_cc: '||sql%rowcount||' rows inserted,time: '||TO_CHAR(SYSDATE,'yyyymmdd hh24:mi:ss'));
v_Excpet_Info := '1.1 INSERT t_dj_cc:';
UPDATE db_zgxt.t_tbxt_KZXX SET bz = v_Excpet_Info WHERE czmc=v_Name; v_Excpet_Info := '1.2 INSERT t_dj_ccdjxx:';
DBMS_OUTPUT.put_line('BeginTime: '||TO_CHAR(SYSDATE,'yyyymmdd hh24:mi:ss'));
--3 后置处理控制信息;
v_Excpet_Info:='代码段3';
v_Exe_Info := '--同步成功--';
DBMS_OUTPUT.put_line(v_Excpet_Info||TO_CHAR(SYSDATE,'yyyy-mm-dd hh24:mi:ss'));
v_Exe_Info := v_Exe_Info||v_Excpet_Info;
-- 置控制信息为成功,填充最大的执行成功时间
UPDATE db_zgxt.t_tbxt_KZXX SET ZXZT_BJ=0,
KZ_SJ=v_EndTime,
QSSJ=v_EndTime,
ZZSJ=v_EndTime+1,
YCXX=v_Exe_Info
WHERE czmc=v_Name;
--执行成功
o_result:='0';
COMMIT;
EXCEPTION
WHEN OTHERS THEN
o_result := SUBSTR(SQLERRM,1,255);
v_Excpet_Info:='EXCEPTION : '||o_result||' ; LOCATION: '||v_Excpet_Info;
ROLLBACK;
UPDATE db_zgxt.t_tbxt_KZXX SET ZXZT_BJ=-1,ycxx=v_Excpet_Info
WHERE czmc=v_Name;
COMMIT;
END;
错误日志如下Error: PL/SQL: ORA-04052: 在查找远程对象 [email protected] 时出错
ORA-00604: 递归 SQL 层 1 出现错误
ORA-03106: 致命的双工通信协议错误
ORA-02063: 紧接着line(源于CSK4825)
在网上查了很多关于这个错误解决方法,都没有用2个数据库的版本是一致的
单独执行SELECT CCDJLSH, YX_QSRQ FROM djcx_t_dj_cc 是可以查询处数据的 djcx_t_dj_cc是个同义词
这个错误到底是什么原因导致的呀
connect to DB_TIPS identified by CSSGDLT
using '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 160.16.56.26)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = tipssc)
)
)';
xjds33 11:54:25
CREATE OR REPLACE PROCEDURE P_ETL_DJXX( -- i_BeginTime DATE, --开始时间
i_EndTime DATE, --终止时间
o_result OUT VARCHAR2 --执行结果:'0' 正确执行; 否则,返回ORACLE错误提示
) AS
v_Name VARCHAR2(40):='P_ETL_DJXX';
v_exe_status INTEGER; -- 执行状态标志
v_KZSJ DATE; -- 下次数据同步起点时间
v_Exe_Info VARCHAR2(255); -- 执行信息
v_Excpet_Info VARCHAR2(255); -- 异常信息
v_StartTime DATE;
v_BeginTime DATE; -- 调整后的开始时间
v_EndTime DATE;BEGIN
--0 前置处理控制信息;
v_Excpet_Info:='代码段0';
v_StartTime:=SYSDATE; -- 执行开始时间
SELECT ZXZT_BJ,QSSJ INTO v_exe_status,v_KZSJ FROM db_zgxt.t_tbxt_KZXX WHERE czmc=v_Name;
IF v_exe_status=1 THEN
o_result:='该过程正在被其他用户调用,请等待释放后执行。';
dbms_output.put_line(o_result);
RETURN;
ELSE--(KZ_SJ=0,-1)
v_BeginTime:=v_KZSJ;
v_EndTime:=i_EndTime;
v_Exe_Info := '该过程正在被调用'||TO_CHAR(v_StartTime,'YYYY-MM-DD HH24:MI:SS');
-- 置控制信息的标志为 '正在执行’
UPDATE db_zgxt.t_tbxt_KZXX SET ZXZT_BJ=1,YCXX=v_Exe_Info WHERE czmc=v_Name;
COMMIT;
END IF;
--1 同步代码表
v_Excpet_Info := '1.1 INSERT t_dj_cc:';
DBMS_OUTPUT.put_line('BeginTime: '||TO_CHAR(SYSDATE,'yyyymmdd hh24:mi:ss')); -- 新增加的 t_fp_nsrfpkc DELETE t_dj_cc NOLOGGING where (CCDJLSH, YX_QSRQ) in (SELECT CCDJLSH, YX_QSRQ FROM djcx_t_dj_cc T WHERE T.LR_SJ>=v_BeginTime AND T.LR_SJ<v_EndTime);
INSERT INTO DB_zgxt.t_dj_cc NOLOGGING SELECT * FROM djcx_t_dj_cc T WHERE t.gljg_dm like '26502%' and T.LR_SJ>=v_BeginTime AND T.LR_SJ<v_EndTime;
DBMS_OUTPUT.put_line('t_dj_cc: '||sql%rowcount||' rows inserted,time: '||TO_CHAR(SYSDATE,'yyyymmdd hh24:mi:ss'));
v_Excpet_Info := '1.1 INSERT t_dj_cc:';
UPDATE db_zgxt.t_tbxt_KZXX SET bz = v_Excpet_Info WHERE czmc=v_Name; v_Excpet_Info := '1.2 INSERT t_dj_ccdjxx:';
DBMS_OUTPUT.put_line('BeginTime: '||TO_CHAR(SYSDATE,'yyyymmdd hh24:mi:ss'));
--3 后置处理控制信息;
v_Excpet_Info:='代码段3';
v_Exe_Info := '--同步成功--';
DBMS_OUTPUT.put_line(v_Excpet_Info||TO_CHAR(SYSDATE,'yyyy-mm-dd hh24:mi:ss'));
v_Exe_Info := v_Exe_Info||v_Excpet_Info;
-- 置控制信息为成功,填充最大的执行成功时间
UPDATE db_zgxt.t_tbxt_KZXX SET ZXZT_BJ=0,
KZ_SJ=v_EndTime,
QSSJ=v_EndTime,
ZZSJ=v_EndTime+1,
YCXX=v_Exe_Info
WHERE czmc=v_Name;
--执行成功
o_result:='0';
COMMIT;
EXCEPTION
WHEN OTHERS THEN
o_result := SUBSTR(SQLERRM,1,255);
v_Excpet_Info:='EXCEPTION : '||o_result||' ; LOCATION: '||v_Excpet_Info;
ROLLBACK;
UPDATE db_zgxt.t_tbxt_KZXX SET ZXZT_BJ=-1,ycxx=v_Excpet_Info
WHERE czmc=v_Name;
COMMIT;
END;
错误日志如下Error: PL/SQL: ORA-04052: 在查找远程对象 [email protected] 时出错
ORA-00604: 递归 SQL 层 1 出现错误
ORA-03106: 致命的双工通信协议错误
ORA-02063: 紧接着line(源于CSK4825)
在网上查了很多关于这个错误解决方法,都没有用2个数据库的版本是一致的
单独执行SELECT CCDJLSH, YX_QSRQ FROM djcx_t_dj_cc 是可以查询处数据的 djcx_t_dj_cc是个同义词
这个错误到底是什么原因导致的呀
Cause: An error has occurred when trying to look up a remote object.
Action: Fix the error. Make sure the remote database system has run KGLR.SQL to create necessary views used for querying/looking up objects stored in the database. 在dblink指向的库上以sys用户执行脚本\oracle\product\10.2.0\db_1\RDBMS\ADMIN\catrpc.sql
Creates internal views for RPC
These views are needed only for databases with the procedural option
that are accessed by remote databases