Re: execute immediate from a procedure across a link

From: obakesan <cjundieseastwd_at_powerup.com.au>
Date: Tue, 29 Jul 2003 23:24:11 GMT
Message-ID: <bg6vmt$ba3$1_at_kraken.itc.gu.edu.au>


HiYa

In article <KkrVa.140$Kx1.1812_at_newsfep4-glfd.server.ntli.net>, "Eric Parker" <eric.parkerthedross_at_virgin.net> wrote:
>"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.

oops ... (baka gomene)

On the server which runs procedure testa I'm using oracle 9i

and the server which contains the dump prodecure is oracle 8i

>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 *'.

the delete * resulted in running out of rollback

>Maybe somebody else has a better suggestion.

thanks anyway :-)

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 Wed Jul 30 2003 - 01:24:11 CEST

Original text of this message