execute immediate from a procedure across a link

#1

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@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

#2

HiYa

In article <KkrVa.140$Kx1.1812 (AT) newsfep4-glfd (DOT) server.ntli.net>, "Eric Parker"
<eric.parkerthedross (AT) virgin (DOT) net> wrote:

Quote:

"obakesan" <cjundieseastwd (AT) powerup (DOT) com.au> wrote in message
news:bg59d2$i11$1 (AT) kraken (DOT) 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@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

Quote:

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

Quote:

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

#3

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 (DOT) itc.gu.edu.au>, cjundieseastwd (AT) powerup (DOT) com.au
(obakesan) wrote:

Quote:

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@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

#4

You were right. 'Commit', expicit or implicit, can not be used in a
remote procedure that contains 'OUT' parameters - or functions that
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 (DOT) com.au (obakesan) wrote in message news:<bg7jgv$6pb$1 (AT) kraken (DOT) itc.gu.edu.au>...

Quote:

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 (DOT) itc.gu.edu.au>, cjundieseastwd (AT) powerup (DOT) 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@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

#5

HiYa

In article <130ba93a.0307301607.459d8edc (AT) posting (DOT) google.com>,
JusungYang (AT) yahoo (DOT) com (Jusung Yang) wrote:

Quote:

You were right. 'Commit', expicit or implicit, can not be used in a
remote procedure that contains 'OUT' parameters - or functions that

its the implicit part that licked me for a bit, as I initially removed the
reference to it ... and same.

seems like an oversight in RPC to me.

Quote:

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.

the logging table seems to be the best route, but I didn't really wanna do
that :-)

xie xie

Quote:

- Jusung Yang

cjundieseastwd (AT) powerup (DOT) com.au (obakesan) wrote in message
news:<bg7jgv$6pb$1 (AT) kraken (DOT) 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 (DOT) itc.gu.edu.au>, cjundieseastwd (AT) powerup (DOT) 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@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

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

execute immediate from a procedure across a link

comp.databases.oracle.tools comp.databases.oracle.tools

execute immediate from a procedure across a link - 07-29-2003 , 02:56 AM

Re: execute immediate from a procedure across a link - 07-29-2003 , 06:24 PM

Re: execute immediate from a procedure across a link - 07-30-2003 , 12:02 AM

Re: execute immediate from a procedure across a link - 07-30-2003 , 07:07 PM

Re: execute immediate from a procedure across a link - 07-30-2003 , 11:00 PM