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