Re: Remote Function Call Oracle: Internal Error

Frank et al,

It really is bizzarre. It just started working. I changed nothing. A few days later, I compiled and i got the same unknown error. But a day later it worked again, and has been for the last week. Maybe because the remote instance of Oracle is 8i and my local one is 9i (release 2) there is some sort of instability in the connection.

Anyway, thanks again.

-PK

Frank <fbortel_at_nescape.net> wrote in message news:<bpgjgc$gm4$1_at_news3.tilbu1.nb.home.nl>...
> Kid A wrote:
>
> > That was my first approach, but as you can see here, i'm not using the
> > cursor across the db link. I am calling the remote function which
> > takes the results and places them in a table of records. The table is
> > then passed across the db link. Isn't this possible? I mean it is
> > just a collection.
> >
> > Frank <fbortel_at_nescape.net> wrote in message news:<bpe46m$b85$2_at_news3.tilbu1.nb.home.nl>...
> >
> >>Kid A wrote:
> >>
> >>
> >>>I am writing stored proecdures on Oracle 9 (release 1 I believe). I am
> >>>having difficulty creating a stored procedure that makes a remote
> >>>function call to a database on a different host.
> >>>
> >>>On the remote database I declare the following package:
> >>>
> >>>----------
> >>>
> >>>create or replace package remote_package
> >>>as
> >>>cursor cursor_a is select * from my_table;
> >>>type my_table_tab is table of cursor_a%ROWTYPE;
> >>>
> >>>----------
> >>>
> >>>I declare the following function
> >>>
> >>>---------
> >>>create or replace function my_function return
> >>>remote_package.my_table_tab
> >>>
> >>>as
> >>>indx NUMBER;
> >>>
> >>>BEGIN
> >>>open remote_package.cursor_a;
> >>>indx := 0;
> >>>for rec_a in remote_package.cursor_a
> >>>loop
> >>>my_table_tab(indx) := rec_a;
> >>>indx := indx + 1;
> >>>end loop;
> >>>
> >>>END;
> >>>-----------------
> >>>
> >>>
> >>>On my local database, I have the following procedure written:
> >>>
> >>>
> >>>-------------------------
> >>>create or replace procedure call_remote
> >>>
> >>>as
> >>>
> >>>a_table remote_package.my_table_tab_at_REMOTE_HOST
> >>>
> >>>begin
> >>>a_table := my_function_at_REMOTE_HOST;
> >>>end;
> >>>--------------------------------
> >>>
> >>>When I run the local SQL statement to create the procedure, I get the
> >>>following error:
> >>>
> >>>PLS-00801: INTERNAL ERROR[1401]
> >>>
> >>>I made sure that:
> >>>@REMOTE_HOST is valid
> >>>When I deploy the package and functions locally, everything works
> >>>perfectly.
> >>>
> >>>For some reason calling the package and function that is passing back
> >>>a table of records is just not working and I am not sure why. Any
> >>>help would be greatly appreciated, thanks!
> >>>
> >>>-PK
> >>
> >>You cannot use ref cursors over db links
>
> Too fast - dunno where I got the ref cursor stuff from - apologies!
> 1) Why not use a locally defined cursor, reading from a remote table?
> 2) Suggest you try to replace your local "a_table" definition with
> hard-coded values, as I doubt the PL/SQL engine is able to read
> the remote dd.
> Not in the position to test the scenario.
Received on Tue Nov 25 2003 - 09:28:18 CST