版本:Oracle 11.2.0.4.0
操作系统:linux redhat 6.4
场景:
本地数据库MSTRPT调用远程数据库ADM存储过程,报错,执行脚本如下:
declare
v_return number;
begin
-- Call the procedure
sp_fct_loan('201-0-01', v_return);
end;
本地调用时报如下错误:
原因分析:
本地调用远端过程,远端过程要求不能有事务,事务必须本地控制,比如远端过程有ddl,commit,rollback什么的都是不被允许的,如果需要commit必须将commit放置在主过程中,否则就会出错。
问题解决:
1.在远程数据库的存储过程中不进行commit、rollback操作,也就是采用分布式事务管理的办法。
将Commit 去掉
2.使用Oracle自治事务。oralce自治事务的声明方法PRAGMA AUTONOMOUS_TRANSACTION,对BEGIN……END块是有效的。
CREATE OR REPLACE PROCEDURE SP_FCT_LOAN(V_DATE IN VARCHAR2,V_RETURN OUT NUMBER)
AS
PRAGMA AUTONOMOUS_TRANSACTION;
3.不要返回标志,根据过程是否成功执行,在本地做判断。
补充:
ORACLE中的自治事务
关于自治事务可以通过以下实验可以验证。
建立一个表:
create table msg (msg varchar2(120));
SQL> declare
2 cnt number := -1; --} Global variables
3 procedure local is
4 begin
5 select count(*) into cnt from msg;
6 dbms_output.put_line('local: # of rows is '||cnt);
7
8 insert into msg values ('New Record');
9 commit;
10 end;
11 begin
12 delete from msg ;
13 commit;
14 insert into msg values ('Row 1');
15 local;
16 select count(*) into cnt from msg;
17 dbms_output.put_line('main: # of rows is '||cnt);
18 rollback;
19
20 local;
21 insert into msg values ('Row 2');
22 commit;
23
24 local;
25 select count(*) into cnt from msg;
26 dbms_output.put_line('main: # of rows is '||cnt);
27 end;
28 /
local: # of rows is 1 -> 子程序local中可以’看到’主匿名块中的uncommitted记录
main: # of rows is 2 -> 主匿名块可以’看到’2条记录(它们都是被local commit掉的)
local: # of rows is 2 -> 子程序local首先’看到’2条记录,然后又commit了第三条记录
local: # of rows is 4 -> 子程序local又’看到’了新增加的记录(它们都是被local commit掉的),然后又commit了第五条记录
main: # of rows is 5 -> 主匿名块最后’看到’了所有的记录.
PL/SQL procedure successfully completed
SQL>
SQL> declare
2 cnt number := -1; --} Global variables
3 procedure local is
4 pragma AUTONOMOUS_TRANSACTION;
5 begin
6 select count(*) into cnt from msg;
7 dbms_output.put_line('local: # of rows is '||cnt);
8
9 insert into msg values ('New Record');
10 commit;
11 end;
12 begin
13 delete from msg ;
14 commit;
15 insert into msg values ('Row 1');
16 local;
17 select count(*) into cnt from msg;
18 dbms_output.put_line('main: # of rows is '||cnt);
19 rollback;
20
21 local;
22 insert into msg values ('Row 2');
23 commit;
24
25 local;
26 select count(*) into cnt from msg;
27 dbms_output.put_line('main: # of rows is '||cnt);
28 end;
29 /
local: # of rows is 0 -> 子程序local中无法可以’看到’主匿名块中的uncommitted记录 (因为它是独立的)
main: # of rows is 2 -> 主匿名块可以’看到’2条记录,但只有一条是被commited.
local: # of rows is 1 -> 子程序local中可以’看到’它前一次commit的记录,但是主匿名块中的记录已经被提前rollback了
local: # of rows is 3 -> 子程序local 中可以’看到’3条记录包括主匿名块commit的记录
main: # of rows is 4 ->主匿名块最后’看到’了所有的记录.
PL/SQL procedure successfully completed
SQL>