Oracle 调用具有varchar数组的远程存储过程，如参数所示

我有以下场景:

1-两个数据库，源目标，通过dblink连接。
2-在目标数据库上，我有一个在数据库源上调用另一个过程的过程
3-数据库源上的过程具有一个IN参数，该参数是一个数组

我首先尝试定义自己的类型，例如:

-

CREATE TYPE r_object_ids_t as TABLE OF VARCHAR2(20);

-

但是我收到了以下错误:
-

ORA-30626: function/procedure parameters of remote object types are not supported

-

这里清楚地解释了这个错误: 使用远程数据库的用户定义类型的限制:http://docs.oracle.com/cd/B19306_01/appdev.102/b14260/adobjbas.htm#sthref190

为了超越这个错误，我尝试使用DBMS_UTILITY.NAME_ARRAY。

一个简单的代码片段:


源数据库:
--------------------------------

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;

--------------------------------


目标数据库:
--------------------------------

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;

--------------------------------

但是现在我收到以下错误:
-

Error(456,1): PLS-00306: wrong number or types of arguments in call to 'REPORT_FILE'

-

有没有任何方法允许我调用接收varchar的数组作为输入参数的远程过程？

提前谢谢。

您需要在远程站点使用DBMS_UTILITY.NAME_ARRAY声明my_ids!

首先在远程数据库上创建对象:

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

然后在本地站点上，my_ids变为 “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