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
Received on Tue Nov 18 2003 - 21:44:15 CST
