I have the following scenario:
1 – Two databases, SOURCE e TARGET, connected by a dblink.
2 – On TARGET database, i have a procedure that invokes another procedure on database SOURCE
3 – The procedure on database SOURCE has an IN parameter that is an ARRAY
I first tried to define my own types, something like :
-----------------------------------------------
CREATE TYPE r_object_ids_t as TABLE OF VARCHAR2(20);
-----------------------------------------------
But i received the following error:
-----------------------------------------------
ORA-30626: function/procedure parameters of remote object types are not supported
-----------------------------------------------
This error is clearly explained here: Restriction on Using User-Defined Types with a Remote Database :
http://docs.oracle.com/cd/B19306_01/appdev.102/b14260/adobjbas.htm#sthref190 In order to surpass this error, i tried to use DBMS_UTILITY.NAME_ARRAY.
A simple snippet of the code:
SOURCE database:
------------------------------------------------
PROCEDURE REPORT_FILE(my_ids IN DBMS_UTILITY.NAME_ARRAY)
AS
i BINARY_INTEGER :=1;
BEGIN
FORALL i IN my_ids.FIRST..my_ids.LAST
UPDATE MY_TABLE SET MY_COLUMN=1 WHERE MY_ID = my_ids(i);
END REPORT_FILE;
------------------------------------------------
TARGET database:
------------------------------------------------
PROCEDURE VERIFY_FILE_REPORT
AS
i BINARY_INTEGER :=1;
my_ids DBMS_UTILITY.NAME_ARRAY;
BEGIN
SELECT MY_ID BULK COLLECT INTO my_ids ...
SOURCE_PACKAGE.REPORT_FILE@ORCL.SOURCE(my_ids);
END VERIFY_FILE_REPORT;
------------------------------------------------
But now i receive the following error:
-----------------------------------------------
Error(456,1): PLS-00306: wrong number or types of arguments in call to 'REPORT_FILE'
-----------------------------------------------
Is there any approach that allows me to invoke a remote procedure that receives an array of varchar's as an Input parameter?
Thanks in advance.
You need to declare my_ids using DBMS_UTILITY.NAME_ARRAY at the remote site!
First create the objects on the remote DB:
create table t (
x int
);
create or replace procedure report_file ( my_ids in dbms_utility.name_array ) as
i binary_integer := 1;
begin
forall i in my_ids.first..my_ids.last
insert into t values (my_ids(i));
end report_file;
/
Then on the local site my_ids becomes "dbms_utility.name_array@remote":
select count(*) from t@db122;
COUNT(*)
0
create or replace procedure verify_file_report as
i binary_integer := 1;
my_ids dbms_utility.name_array@db122;
begin
select rownum
bulk collect into my_ids
from dual
connect by level <= 10;
report_file@db122 ( my_ids );
end verify_file_report;
/
exec verify_file_report;
select count(*) from t@db122;
COUNT(*)
10