修改“C#调用Oracle的存储过程，其参数为数组类型”中的Package

[mr.tr...@gmail.com]

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

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(i);
v_content:=v_string(i);
end loop;
close c;
end test;
end packtest;
/

[mr.tr...@gmail.com]

调用：
using Oracle.DataAccess;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
...
private void button4_Click(object sender, System.EventArgs e)
{
string connectionstring=string.Concat
(
"Password=123",
";User ID=user",
";Data Source=oradata"
);
OracleConnection con=new OracleConnection(connectionstring);
try
{

con.Open();
OracleCommand cmd=con.CreateCommand();
//
//
//
cmd.CommandType=CommandType.StoredProcedure;
cmd.CommandText="packtest.test";
//
//定义Parameter，其中，Size是数组中的元素数量
//
OracleParameter Param1 = new
OracleParameter(@"p1",OracleDbType.Varchar2,3);
OracleParameter Param2 = new
OracleParameter(@"p2",OracleDbType.Int32,3);
//
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 "};
Param2.Value = null;
// Specify the maximum number of elements in the PL/SQL Associative

//Array
//

// 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
//对于Int类型的Parameter，可以不设置Size，因为本身Int定义了自身大小
//
// Param2.ArrayBindSize = new int[3]{1, 2, 1};
cmd.Parameters.Add(Param1);
cmd.Parameters.Add(Param2);

// execute the cmd
cmd.ExecuteNonQuery();
//print out the parameter's values
int[] matrixint=(int[])Param2.Value;
foreach(int t in matrixint)
{
ShowMessage(t.ToString());
}

// con.Close();

}
catch(Exception ex)
{
MessageBox.Show(string.Concat(
ex.Message,
"\r\n",
ex.Source.ToString(),
"\r\n",
ex.TargetSite.ToString(),
"\r\n",
ex.StackTrace.ToString()

));
}
finally
{
con.Close();
}
}