C#调用Oracle的存储过程，其参数为数组类型

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

-------------------
Create or replace package packtest
as
 type string_array is table of varchar2(21) index by binary_integer;
 type int_array is table of number(4) index by binary_integer;
 procedure test(v_string   in   string_array,
                       v_int       out int_array);
end packtet;
/
--------------------------------------------------------------------------
Create or replace package body packtest
as
 procedure test(v_string in string_array,
                       v_int     out int_array)
 as
    cursor c is select num from test;
    v_content  varchar2(21);
 begin
    open c;
    for i in 1.. 3
    loop
      fetch c into v_int;
      v_content:=v_string(i);
    end loop;
    close c;
end test;
end packtest;
/
-------------------------------------------------------------------------
public void BindAssocArray()
{
...
OracleCommand cmd = new OracleCommand();
cmd.CommandType=CommandType.StoredProcedure;
cmd.CommandText="packtest.test";
//
OracleParameter Param1 = cmd.Parameters.Add(...);
OracleParameter Param2 = cmd.Parameters.Add(...);
//
Param1.Direction = ParameterDirection.Input;
Param2.Direction = ParameterDirection.Output;

// Specify that we are binding PL/SQL Associative Array
Param1.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
Param2.CollectionType = OracleCollectionType.PLSQLAssociativeArray;

// Setup the values for PL/SQL Associative Array
Param1.Value = new string[3]{"First Element",
"Second Element ",
"Third Element "};
Param3.Value = null;
// Specify the maximum number of elements in the PL/SQL Associative
Array
Param1.Size = 3;
Param2.Size = 3;
//
Param2.DbType=DbType.Int32;
// Setup the ArrayBindSize for Param1
Param1.ArrayBindSize = new int[3]{13, 14, 13};
// Setup the ArrayBindStatus for Param1
Param1.ArrayBindStatus = new OracleParameterStatus[3]{
OracleParameterStatus.Success,
OracleParameterStatus.Success,
OracleParameterStatus.Success};
// Setup the ArrayBindSize for Param2
Param2.ArrayBindSize = new int[3]{20, 20, 20};

// execute the cmd
cmd.ExecuteNonQuery();
//print out the parameter's values
int[] matrixint=(int[])Param2.Value;
......

- Show quoted text -

}

转载于:https://www.cnblogs.com/NRabbit/archive/2009/07/10/1736181.html