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
--
Regards, Frank van Bortel
Received on Tue Nov 18 2003 - 15:58:27 CST
