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.

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;
/

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