Sunday, July 19, 2015

DB: ORA-02064: distributed operation not supported

Recently I faced an error when I tried to execute a remote procedure having transaction control statements(Commit/Rollback) over DB link. 

Error: ORA-02064: distributed operation not supported

Reference note for this error code:
1.One of the following unsupported operations was attempted:1. array execute of a remote update with a subquery that references a dblink, or
2. an update of a long column with bind variable and an update of a second column with a subquery that both references a dblink and a bind variable, or
3. a commit is issued in a coordinated session from an RPC procedure call with OUT parameters or function call


In my case, the reason was the remote procedure was performing insert and commit.

Solution:
Modified the remote procedure to create Autonomous Transaction using PRAGMA AUTONOMOUS_TRANSACTION syntax as shown below.

CREATE OR REPLACE PACKAGE test_pkg AS
    PROCEDURE test_proc (p_param OUT VARCHAR2 );
END test_pkg;

CREATE OR REPLACE PACKAGE BODY test_pkg AS
    PROCEDURE test_proc (p_param OUT VARCHAR2) IS
    l_var NUMBER;
    PRAGMA AUTONOMOUS_TRANSACTION;
    BEGIN
    ...
    --BUSINESS LOGIC
    ...
    EXCEPTION
       WHEN OTHERS THEN
      ..
   END test_proc;
END test_pkg;

http://docs.oracle.com/database/121/LNPLS/autotransaction_pragma.htm

The term "automous transaction" refers to the ability of PL/SQL temporarily suspend the current transaction and begin another, fully independent transaction (which will not be rolled-back if the outer code aborts).  The second transaction is known as an autonomous transaction. The autonomous transaction functions independently from the parent code.

An autonomous transaction has the following characteristics:

> The child code runs independently of its parent
> The child code can commit or rollback & parent resumes
> The parent code can continue without affecting child work

No comments:

Post a Comment

Provide your thoughts !