Re: calling a function over db link that creates a table in target db
Date: Sun, 29 Mar 2009 03:10:34 -0700 (PDT)
On Mar 26, 2:29 pm, ciapecki <ciape..._at_gmail.com> wrote:
> 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
> 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
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:
- array execute of a remote update with a subquery that references a dblink, or
- 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
- 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: