Oracle 遭遇ORA-02064 ：不支持分布式操作 解决

原文链接：https://ask.hellobi.com/blog/Zeehom/4347
作者：要选就选S型-日光之下并无新事

版本：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> 

                        <div class="meta clearfix">
                            <div style="position: relative;"
                                 class="aw-article-vote pull-left">
                                <a style="font-size: 18px;" href="javascript:;"
                                   class="agree"
                                   onclick="AWS.User.article_vote($(this), 4347, 1);"><i
                                        class="icon icon-agree"></i> 推荐 <b>0</b></a>



                            </div>