Re: execute immediate from a procedure across a link

[Quoted] "obakesan" <cjundieseastwd_at_powerup.com.au> wrote in message news:bg59d2$i11$1_at_kraken.itc.gu.edu.au...
> 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
>

Chris

You haven't stated which Oracle Version you are running. [Quoted] In Oracle 8i and I believe in 9i it is illegal to execute "COMMIT" remotely. As a DDL statement, there is an implicit commit in 'truncate table BILLY'. 2 suggestions :
You could submit a job to do this and wait for it to complete/error. You could issue a 'delete *'.
[Quoted] Maybe somebody else has a better suggestion.

HTH eric

--
Remove the dross to contact me directly