Thursday, October 30, 2008

Commit from a Procedure/Function Executed Through a Database Link, ORA-02064: distributed operation not supported

It is not supported to issue a commit from a procedure with OUT parameters or a function with return value, when the procedure/function is executed through a database link.

Here is a test case, working fine:
1-) At remote database create following procedure:

create or replace procedure erkan_trunc
is
begin
insert into XXX values(1,1,'erkan saka'); -- Create a transaction at remote db
EXECUTE IMMEDIATE 'TRUNCATE TABLE ERKAN.XXX'; -- This is a DDL! which also autocommits
end;
/

2-) At source database create following procedure

create or replace procedure erkan_dene
is
begin
insert into erkan.A values(1); -- Create a transaction at local (source) db
erkan_trunc@{db_link}; -- remote procedure call
rollback; -- End local transaction
end;
/

3-) Working fine:

SQL>exec erkan_dene

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.03

Here is an other test case with ORA-02064: distributed operation not supported:
1-) At remote database create following procedure:

create or replace function erkan_trunc2
RETURN NUMBER  -- This is the reason causing ORA-02064
is
begin
insert into XXX values(1,1,'erkan saka');
EXECUTE IMMEDIATE 'TRUNCATE TABLE ERKAN.XXX';
RETURN (0);
end;
/


2-) At source run following PL/SQL

SQL>declare
  2     de_return number;
  3  begin
  4     de_return := erkan_trunc2@{db_link};
  5             dbms_output.put_line('de_return='||de_return);
  6  end;
  7  /
declare
*
ERROR at line 1:
ORA-02064: distributed operation not supported
ORA-06512: at "ERKAN.ERKAN_TRUNC2", line 6
ORA-06512: at line 4


Elapsed: 00:00:00.01



Tuesday, October 7, 2008

ORA-02064: distributed operation not supported

If a commit/rollback or autocommit(DDL) operation is executed within a procedure or function which is called remotely via database link then ORA-02064 may occur. Exact explanation is:
It is not supported to issue a commit from a procedure with OUT parameters or a function with return value, when the procedure/function is executed through a database link.
I tested following two senarios. First works fine while second fails with ORA-02064:

Senario 1-)

* Create following procedure at remote database:
create or replace procedure erkan_trunc
is
begin
insert into XXX values(1,1,'erkan'); -- in order to create a local transaction at remote database
EXECUTE IMMEDIATE 'TRUNCATE TABLE ERKAN.XXX'; -- autocommits
end;
/

* Create following procedure at source database:
create or replace procedure erkan_dene
is
begin
insert into erkan.A values(1); --in order to create a local transaction in source database
BEGIN
erkan_trunc@remote_tns;
EXCEPTION
       WHEN OTHERS
       THEN
dbms_output.put_line('Error while truncate: ' || SUBSTR (SQLERRM, 1, 100););
END;
rollback;
end;
/

* Works fine:
SQL>exec erkan_dene
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.03

if i hold lock on table XXX at remote database...
SQL>exec erkan_dene
Error while truncate: ORA-00054: resource busy and acquire with NOWAIT specified
ORA-06512: at "ERKAN.ERKAN_TRUNC", line 5

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.03

Senario 2-)

* Create following function at remote database:
create or replace function erkan_trunc2
RETURN NUMBER
is
begin
insert into XXX values(1,1,'erkan');
EXECUTE IMMEDIATE 'TRUNCATE TABLE ERKAN.XXX';
RETURN (0);
end;
/

* Run following pl/sql script at source database:
SQL>declare
  2     de_return number;
  3  begin
  4     de_return := erkan_trunc2@remote_tns;
  5             dbms_output.put_line('de_return='||de_return);
  6  end;
  7  /
declare
*
ERROR at line 1:
ORA-02064: distributed operation not supported
ORA-06512: at "ERKAN.ERKAN_TRUNC2", line 6
ORA-06512: at line 4

Elapsed: 00:00:00.01