C#调用oracle中带数组的存储过程

--存储过程

CREATE OR REPLACE PACKAGE pg_test IS
TYPE idArrayPg is table of integer index by BINARY_INTEGER;

 PROCEDURE p_test(ids   IN idArrayPg
     );

END pg_test;

CREATE OR REPLACE PACKAGE BODY pg_test IS
 PROCEDURE p_test(ids   IN idArrayPg
     ) AS
  l_index number;
 BEGIN
  l_index:= ids.first;--使用first方法
       loop
                     dbms_output.put_line(l_index ||':'|| ids(l_index));
       exit when l_index = ids.LAST;
              l_index :=ids.next(l_index);
       end loop;
 END p_test;

END pg_test;

 

--pl/sql测试程序

declare
  ids pg_test.idarraypg;
begin
  ids(1) := 1;
    ids(2) := 2;
  ids(3) := 3;

pg_test.p_test(ids => ids);
end;

 

//C#中调用

cmd = new OracleCommand("pg_test.p_test", con);
cmd.CommandType = CommandType.StoredProcedure;

OracleParameter param1 = cmd.Parameters.Add("param1", OracleDbType.Int32);
param1.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
param1.Direction = ParameterDirection.Input;

param1.Value = new Int[3]{1,2,3};

param1.Size = 3;
cmd.ExecuteNonQuery(); 

上述代码经测试,运行正确.

注:

在项目开发过程中,曾试图将数组定义为:

TYPE idArrayPg is table of integer;

在C#中调用存在问题,但在JAVA中调用正常.至于如何在C#中调用定义的数组不带index by BINARY_INTEGER,目前还未找到解决方案.