Re: calling a function over db link that creates a table in target db

From: Randolf Geist <>
Date: Sun, 29 Mar 2009 03:10:34 -0700 (PDT)
Message-ID: <>

On Mar 26, 2:29 pm, ciapecki <> wrote:
> 10gR2
> on target db I have following function:
> error when creating gcd_countries-ORA-02064: distributed operation not
> supported-CREATE TABLE table_a as select * from other_user.table_a
> PL/SQL procedure successfully completed.
> The exception part was called, which means CREATE TABLE failed.
> I found a workaround with a wrapper procedure on target DB:
> when I call it from source like this:
> SQL> call prepare_table_proc_at_dblink_to_targetuser
> ('other_user.table_a');
> Call completed.
> And the table_a is created on target DB.
> This is not very DRY, since the wrapper, does nothing more but calls
> the function with exactly the same parameter.
> Is it possible to achieve the goal without that wrapper procedure
> (prepare_table_proc)?


I haven't tested it myself, but if you check the error message:

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.

You'll notice that point 3 above might be the culprit: Your remote procedure does perform a COMMIT but returns a value since it is a function call. So the most obvious way to avoid the error might simply be to turn your function into a procedure, which is what you've done indirectly by using your remote wrapper procedure.


Oracle related stuff blog:

SQLTools++ for Oracle (Open source Oracle GUI for Windows): Received on Sun Mar 29 2009 - 05:10:34 CDT

Original text of this message