This example binds three OracleParameter
objects as PL/SQL Associative Arrays: Param1
as an In
parameter, Param2
as an InputOutput
parameter, and Param3
as an Output
parameter.
PL/SQL Package: MYPACK
/* Setup the tables and required PL/SQL: connect scott/tiger@oracle CREATE TABLE T1(COL1 number, COL2 varchar2(20)); CREATE or replace PACKAGE MYPACK AS TYPE AssocArrayVarchar2_t is table of VARCHAR(20) index by BINARY_INTEGER; PROCEDURE TestVarchar2( Param1 IN AssocArrayVarchar2_t, Param2 IN OUT AssocArrayVarchar2_t, Param3 OUT AssocArrayVarchar2_t); END MYPACK; / CREATE or REPLACE package body MYPACK as PROCEDURE TestVarchar2( Param1 IN AssocArrayVarchar2_t, Param2 IN OUT AssocArrayVarchar2_t, Param3 OUT AssocArrayVarchar2_t) IS i integer; BEGIN -- copy a few elements from Param2 to Param1\n Param3(1) := Param2(1); Param3(2) := NULL; Param3(3) := Param2(3); -- copy all elements from Param1 to Param2\n Param2(1) := Param1(1); Param2(2) := Param1(2); Param2(3) := Param1(3); -- insert some values to db\n FOR i IN 1..3 LOOP insert into T1 values(i,Param2(i)); END LOOP; END TestVarchar2; END MYPACK; / */ // C# using System; using System.Data; using Oracle.DataAccess.Client; class AssociativeArraySample { static void Main() { OracleConnection con = new OracleConnection(); con.ConnectionString = "User Id=scott;Password=tiger;Data Source=oracle"; con.Open(); Console.WriteLine("Connected to Oracle" + con.ServerVersion); OracleCommand cmd = new OracleCommand( "begin MyPack.TestVarchar2(:1, :2, :3); end;", con); OracleParameter Param1 = cmd.Parameters.Add("1", OracleDbType.Varchar2); OracleParameter Param2 = cmd.Parameters.Add("2", OracleDbType.Varchar2); OracleParameter Param3 = cmd.Parameters.Add("3", OracleDbType.Varchar2); Param1.Direction = ParameterDirection.Input; Param2.Direction = ParameterDirection.InputOutput; Param3.Direction = ParameterDirection.Output; // Specify that we are binding PL/SQL Associative Array Param1.CollectionType = OracleCollectionType.PLSQLAssociativeArray; Param2.CollectionType = OracleCollectionType.PLSQLAssociativeArray; Param3.CollectionType = OracleCollectionType.PLSQLAssociativeArray; // Setup the values for PL/SQL Associative Array Param1.Value = new string[3] { "First Element", "Second Element ", "Third Element " }; Param2.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; Param3.Size = 3; // 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 }; // Setup the ArrayBindSize for Param3 Param3.ArrayBindSize = new int[3] { 20, 20, 20 }; // execute the cmd cmd.ExecuteNonQuery(); //print out the parameter's values Console.WriteLine("parameter values after executing the PL/SQL block"); for (int i = 0; i < 3; i++) Console.WriteLine("Param2[{0}] = {1} ", i, (cmd.Parameters[1].Value as Array).GetValue(i)); for (int i = 0; i < 3; i++) Console.WriteLine("Param3[{0}] = {1} ", i, (cmd.Parameters[2].Value as Array).GetValue(i)); // Close and Dispose OracleConnection object con.Close(); con.Dispose(); Console.WriteLine("Disconnected"); } }