不信你可以改下DBLink那句,这样估计就没问题了 CREATE OR REPLACE PROCEDURE sp_tst_job_for_dblink AS cnt NUMBER; sss varchar2(400); BEGIN --SELECT COUNT(*) INTO cnt FROM test22@tst_db; cnt :=0; INSERT INTO test(slog1,slog2) VALUES(to_char(cnt),to_char(SYSDATE,'yyyy-mm-dd hh24:mi:ss')||'==>sp_tst_job_for_dblink'); COMMIT; EXCEPTION WHEN OTHERS THEN sss:=SQLERRM; INSERT INTO test(slog1,slog2) VALUES(sss,to_char(SYSDATE,'yyyy-mm-dd hh24:mi:ss')||'==>sp_tst_job_for_dblink'); COMMIT; END sp_tst_job_for_dblink;
你把test22@tst_db建个同义词先,在procedure中使用同义词看会有这个问题吗? create test22 for test22@tst_db; CREATE OR REPLACE PROCEDURE sp_tst_job_for_dblink AS cnt NUMBER; sss varchar2(400); BEGIN SELECT COUNT(*) INTO cnt FROM test22; INSERT INTO test(slog1,slog2) VALUES(to_char(cnt),to_char(SYSDATE,'yyyy-mm-dd hh24:mi:ss')||'==>sp_tst_job_for_dblink'); COMMIT; EXCEPTION WHEN OTHERS THEN sss:=SQLERRM; INSERT INTO test(slog1,slog2) VALUES(sss,to_char(SYSDATE,'yyyy-mm-dd hh24:mi:ss')||'==>sp_tst_job_for_dblink'); COMMIT; END sp_tst_job_for_dblink;
create synonym test22 for test22@tst_db;
oracle是10g的。现在用同义词测试可以了,但是现在问题又来了,在过程外面建同义词可以用,在过程里面动态创建同义词又不行,过程不报错,直接“PL/SQL procedure successfully completed.”,但是过程里面的插入语句没执行的。 过程外创建同义词: CREATE SYNONYM SYN_TST FOR test22@tst_db; 过程里动态创建同义词: SELECT COUNT(*) INTO cnt FROM user_synonyms WHERE synonym_name='SYN_TST'; IF cnt=1 THEN EXECUTE IMMEDIATE 'DROP SYNONYM SYN_TST'; END IF; EXECUTE IMMEDIATE 'CREATE SYNONYM SYN_TST FOR test22@tst_db'; 真的是莫名其妙啊
CREATE OR REPLACE PROCEDURE sp_tst_job_for_dblink
AS
cnt NUMBER;
sss varchar2(400);
BEGIN
--SELECT COUNT(*) INTO cnt FROM test22@tst_db;
cnt :=0;
INSERT INTO test(slog1,slog2) VALUES(to_char(cnt),to_char(SYSDATE,'yyyy-mm-dd hh24:mi:ss')||'==>sp_tst_job_for_dblink');
COMMIT;
EXCEPTION
WHEN OTHERS THEN
sss:=SQLERRM;
INSERT INTO test(slog1,slog2) VALUES(sss,to_char(SYSDATE,'yyyy-mm-dd hh24:mi:ss')||'==>sp_tst_job_for_dblink');
COMMIT;
END sp_tst_job_for_dblink;
create test22 for test22@tst_db;
CREATE OR REPLACE PROCEDURE sp_tst_job_for_dblink
AS
cnt NUMBER;
sss varchar2(400);
BEGIN
SELECT COUNT(*) INTO cnt FROM test22;
INSERT INTO test(slog1,slog2) VALUES(to_char(cnt),to_char(SYSDATE,'yyyy-mm-dd hh24:mi:ss')||'==>sp_tst_job_for_dblink');
COMMIT;
EXCEPTION
WHEN OTHERS THEN
sss:=SQLERRM;
INSERT INTO test(slog1,slog2) VALUES(sss,to_char(SYSDATE,'yyyy-mm-dd hh24:mi:ss')||'==>sp_tst_job_for_dblink');
COMMIT;
END sp_tst_job_for_dblink;
过程外创建同义词:
CREATE SYNONYM SYN_TST FOR test22@tst_db;
过程里动态创建同义词:
SELECT COUNT(*) INTO cnt FROM user_synonyms WHERE synonym_name='SYN_TST';
IF cnt=1 THEN
EXECUTE IMMEDIATE 'DROP SYNONYM SYN_TST';
END IF;
EXECUTE IMMEDIATE 'CREATE SYNONYM SYN_TST FOR test22@tst_db';
真的是莫名其妙啊
lz是在测试什么吗?