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

From: Randolf Geist <mahrah_at_web.de>
Date: Sun, 29 Mar 2009 03:10:34 -0700 (PDT)
Message-ID: <0c9dd6be-f79c-4b57-bffe-f8dfe6505a19_at_g19g2000yql.googlegroups.com>

On Mar 26, 2:29 pm, ciapecki <ciape..._at_gmail.com> 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)?

Chris,

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.

Regards,
Randolf

Oracle related stuff blog:
http://oracle-randolf.blogspot.com/

SQLTools++ for Oracle (Open source Oracle GUI for Windows): http://www.sqltools-plusplus.org:7676/
http://sourceforge.net/projects/sqlt-pp/ Received on Sun Mar 29 2009 - 05:10:34 CDT