Re: execute immediate from a procedure across a link

[Quoted] You were right. 'Commit', expicit or implicit, can not be used in a remote procedure that contains 'OUT' parameters - or functions that [Quoted] return values for that matter. As long as you avoid the combination of these two, anything practical you can think of should work. You can try creating a error logging temp table in the remote db to capture possible error messages, or move the exception handling to the calling routine.

• Jusung Yang

cjundieseastwd_at_powerup.com.au (obakesan) wrote in message news:<bg7jgv$6pb$1_at_kraken.itc.gu.edu.au>...
> HiYa
>
> further information to my problem is that it seems that the problem is related
> to having out parameters in a procedure (or return values in a function)
>
> seems odd that I can do it, with a procedure that returns nothing to the RPC
> caller, but just goes off and does it.
>
> I did notice that it propagates exceptions back up the line, but I'm not sure
> how to manage these, and if I can or can not "raise" an exception at the other
> server end.
>
> anyone?
>
>
> In article <bg59d2$i11$1_at_kraken.itc.gu.edu.au>, cjundieseastwd_at_powerup.com.au
> (obakesan) wrote:
> >HiYa
> >
> >I'm getting an error on executing a procedure across a database link.
> >
> >This program encounted the following error: ORA-02064: distributed operation
> >not supported
> >
> >I don't understand my mistake.
> >
> >I've got 2 databases, in one database I've got the procedure
> >
> >create or replace procedure dump(result out varchar2, perror out number) is
> >begin
> >perror := 1;
> >execute immediate 'truncate table BILLY';
> >exception
> >WHEN OTHERS THEN
> > result := 'This program encounted the following error: ';
> > result := result || SQLERRM;
> > -- rollback;
> > perror := -1;
> >end dump;
> >
> >then on the other database I have
> >
> >create or replace procedure testa(result out varchar2, errorValue out number)
> >is
> >begin
> > sis_dump_at_oldcas(result,errorValue);
> >end testa;
> >
> >
> >begin
> > -- Call the procedure
> > testa(result => :result,
> > errorvalue => :errorvalue);
> >end;
> >
> >it just won't work with that execute immediate in it ....
> >any thoughts?
> >
> >thanks
> >
> >
> >See Ya
> >(when bandwidth gets better ;-)
> >
> >Chris Eastwood
> >
> >we tend to blame others for our problems
> >I think this is something we inherit from our parents
> >
> >please remove undies for reply
> >
>
> See Ya
> (when bandwidth gets better ;-)
>
> Chris Eastwood
>
> we tend to blame others for our problems
> I think this is something we inherit from our parents
>
> please remove undies for reply
Received on Thu Jul 31 2003 - 02:07:19 CEST