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

begin
a_table := my_function@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

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@REMOTE_HOST
>
> begin
> a_table := my_function@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

--
Regards, Frank van Bortel

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@nescape.net> wrote in message news:<bpe46m$b85$2@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@REMOTE_HOST
> >
> > begin
> > a_table := my_function@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

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@nescape.net> wrote in message news:<bpe46m$b85$2@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@REMOTE_HOST
>>>
>>>begin
>>>a_table := my_function@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.
--
Regards, Frank van Bortel

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@nescape.net> wrote in message news:<bpgjgc$gm4$1@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@nescape.net> wrote in message news:<bpe46m$b85$2@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@REMOTE_HOST
> >>>
> >>>begin
> >>>a_table := my_function@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.