Passing parameter to Oracle
-
Please , can any one confirm that a table type or collection type parameter can be passed to stored procedure in oracle using ADO.NET ? I , browsed several forum and site but without success Thanks love2code
You need to use, ODP.Net , Data Provider for Oracle supplied by Oracle. Assuming you have that here is the example from Oracle /********* Example Start *************/ using System; using System.Data; using System.Text; using System.Reflection; using Oracle.DataAccess.Client; using Oracle.DataAccess.Types; namespace ODPSample { class AssocArray { static void Main(string[] args) { Console.WriteLine("Demo: PL/SQL Associative Array"); // Connect string connectStr = "User Id=scott;Password=tiger;Data Source=oracle"; // Setup the Tables for sample Setup(connectStr); OracleConnection connection = new OracleConnection(connectStr); OracleCommand cmd = new OracleCommand("begin MyPack.TestVarchar2(:1, :2, :3);end;", connection); OracleParameter param1 = cmd.Parameters.Add("param1", OracleDbType.Varchar2); OracleParameter param2 = cmd.Parameters.Add("param2", OracleDbType.Varchar2); OracleParameter param3 = cmd.Parameters.Add("param3", OracleDbType.Varchar2); // Setup the direction 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; param1.Value = new string[3]{"Input1", "Input2", "Input3"}; param2.Value = new string[3]{"Inout1", "Inout2", "Inout3"}; 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 ArrayBind Size for param1 param1.ArrayBindSize = new int[3]{13,14,13}; // Setup the ArrayBind Status for param1 param1.ArrayBindStatus = new OracleParameterStatus[3]{ OracleParameterStatus.Success, OracleParameterStatus.Success, OracleParameterStatus.Success}; // Setup the ArrayBind Size for param2 param2.ArrayBindSize = new int[3]{20,20,20}; // Setup the ArrayBind Size for param3 param3.ArrayBindSize =