Remote Function Call Oracle: Internal Error

From: Kid A <paulkist_at_hotmail.com>
Date: 17 Nov 2003 17:05:03 -0800
Message-ID: <5327b5de.0311171705.74ab1d8a@posting.google.com>

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
Received on Mon Nov 17 2003 - 19:05:03 CST