Akdora’s Blog

Programming, Oracle, Life, Fun

ORA-02064: Distributed Operation not Supported September 11, 2009

Filed under: PL/SQL — Akdora @ 11:06 am
Tags: , , , , ,

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/

 

6 Responses to “ORA-02064: Distributed Operation not Supported”

  1. Ranjith Says:

    Any workaround you have for this? I have some IN parameters to passed to remote function

  2. if you have the grants by using dbms_job or dbms_scheduler scheduling a remote job to do the ddl can be a workaround.

  3. dizi Says:

    very good thaks

  4. John Says:

    Thanks for council, I will try to apply at myself.

  5. ali Says:

    very good thaks

  6. bilgisayar Says:

    I read your post. and I really like your style of blogging. I added it to my favorites website list and will be checking back soon.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s