ORA-02064: Distributed Operation not Supported

ORA-02064 may occur may occur when A commit(also rollback) or autocommit(like DDL) operation is executed within a procedure or function called remotely via database link.Examples are follows

Example-1 : commit issued on remote database

On database A:
create table serdar.bbb ( n number);

create or replace FUNCTION serdar.func_distributed_commit RETURN NUMBER IS
BEGIN
insert into bbb values (1);
commit;
RETURN 1;
END;
/

grant execute on serdar.func_distributed_commit to linkuser ;

Then, we call it from Database B:

DECLARE
rc NUMBER;
BEGIN
rc := serdar.func_distributed_commit@link.databaseA;
END;
/

ORA-02064: distributed operation not supported
ORA-06512: at "SERDAR.FUNC_DISTRIBUTED_COMMIT", line 4
ORA-06512: at line 4


Example-2 : DML issued on remote database

On database A:
CREATE OR REPLACE FUNCTION serdar.func_distributed RETURN NUMBER IS
BEGIN
EXECUTE IMMEDIATE 'create table serdar.bbb as select * from dba_tables';
RETURN 1;
END;
/

grant execute on serdar.func_distributed to linkuser;


Then, we call it from Database B:

DECLARE
rc NUMBER;
BEGIN
rc := serdar.func_distributed@link.databaseA;
END;
/
ORA-02064: distributed operation not supported
ORA-06512: at "SERDAR.FUNC_DISTRIBUTED", line 3
ORA-06512: at line 4


Other causes and reference note
Other causes:
ORA-02064:distributed operation not supported
Cause:
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.
Action:simplify remote update statement

Workaround:
As a workaround Pragma AUTONOMOUS_TRANSACTION can be used in the called function or procedure in the remote site package.