ORA-02064: Distributed Operation not Supported

After a short investigation, I did not see this comment:

We can not run a PL/SQL block contains a DDL (Data Definition Language)  command via a db_link 🙂

ORA-02064: distributed operation not supported
ORA-06512: at "FRAUD.FN_FCMS_BTG", line 3
----- PL/SQL Call Stack -----
 object      line  object
 handle    number  name
7000000c984d720        10  anonymous block

On DB A we write such a funciton:

CREATE OR REPLACE FUNCTION FN_FCMS_BTG RETURN NUMBER IS
BEGIN
 EXECUTE IMMEDIATE 'create table aaaaaaa as select * from f1_segment_1';
 RETURN 1;
END;

Then, we call it from DB B:

DECLARE
 I NUMBER;
BEGIN
 I := FN_FCMS_BTG@MY_LINK;
EXCEPTION
 WHEN OTHERS THEN
 DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_STACK ||
 DBMS_UTILITY.FORMAT_CALL_STACK);
END;

NOTE: Since TRUNCATE is a DDL command, we cannot also use it in the same way 😉

Other causes:

ORA-02064:	distributed operation not supported
Cause:	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

Reference: http://ora-02064.ora-code.com/

Advertisement