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