c#oracle数组超出,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;

......

}