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.
--
Regards, Frank van Bortel
Received on Wed Nov 19 2003 - 14:31:55 CST
