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();
}
}