4 Replies Latest reply: Mar 8, 2013 8:54 AM by user6336927 RSS

    SELECT records using PL/SQL Associative Array and display to .NET grid

      Oracle Database

      We're experimenting with PL/SQL associative arrays and ODP.NET (ODAC), version above. We're having a few problems getting the .NET (C#) test harness to handle multiple elements from the array (PL/SQL OUT parameter). The .NET Dev's re asking how they can handle capturing the elements of the array when they don't know how many records are to be returned. Below is basic example that returns one element, however it could be one row or 100 rows returned from the DB, how could .NET capture this dynamically? At the bottom of this post is the .NET code produced for the test harness. Obviously going forward we want to test capturing two or more elements from the PL/SQL associative array and handling them in .NET. Some of our queries have 40 cols and we need to map each one of them to a PL/SQL table and associated OUT parameter.

      This looks to be the issue;
      po_dpp_number_aa_t.ArrayBindSize = new int[1]{12};
      It assumes one element of size 12, but it may not be 12 and we want more than one element?

      Any help greatly appreciated.
      PROCEDURE DPP_TEST(pi_open_yn             IN     VARCHAR2,
                         po_dpp_number_aa_t        OUT dpp_number_aa_type 
      PROCEDURE DPP_TEST(pi_open_yn             IN     VARCHAR2,
                         po_dpp_number_aa_t        OUT dpp_number_aa_type 
      SELECT dpp_number
      BULK COLLECT INTO po_dpp_number_aa_t
      WHERE ...;
      The .NET test harness
      using System;
      using System.Collections.Generic;
      using System.Linq;
      using System.Text;
      using Oracle.DataAccess.Client;
      using Oracle.DataAccess.Types;
      using System.Data;
      namespace AssociativeArrays
          class Program
              static void Main(string[] args)
                  // connection string - make sure to adjust for your environment
                  string constr = "User Id=scott; password=tiger; Data Source=test; enlist=false; pooling=false"; ;
                  //// create and open connection object
                  //OracleConnection con = new OracleConnection(constr);
                  //// insert the new jobs into the jobs table
                  //// create command object and set attributes
                  //OracleCommand cmd = con.CreateCommand();
                  //cmd.CommandText = "PKG_PMTESTING.DPP_TEST";
                  //cmd.CommandType = CommandType.StoredProcedure;
                  //#region create parameter objects for each parameter
                  //// in params
                  //OracleParameter pi_first_name = new OracleParameter();
                  //OracleParameter pi_surname = new OracleParameter();
                  //OracleParameter pi_sid = new OracleParameter();
                  //OracleParameter pi_facilities_list = new OracleParameter();
                  //OracleParameter pi_agent_person_id = new OracleParameter();
                  //OracleParameter pi_open_yn = new OracleParameter();
                  ////out params
                  //OracleParameter po_sql_error = new OracleParameter();
                  //OracleParameter po_sid_aa_t = new OracleParameter();
                  //OracleParameter po_case_plan_id_aa_t = new OracleParameter();
                  //OracleParameter po_dpp_id_aa_t = new OracleParameter();
                  //OracleParameter po_dpp_number_aa_t = new OracleParameter();
                  //OracleParameter po_person_id_aa_t = new OracleParameter();
                  //OracleParameter po_full_name_aa_t = new OracleParameter();
                  //#region set parameter type for each parameter
                  //// in params
                  //pi_first_name.OracleDbType = OracleDbType.Varchar2;
                  //pi_surname.OracleDbType = OracleDbType.Varchar2;
                  //pi_sid.OracleDbType = OracleDbType.Int32;
                  //pi_facilities_list.OracleDbType = OracleDbType.Varchar2;
                  //pi_agent_person_id.OracleDbType = OracleDbType.Int32;
                  //pi_open_yn.OracleDbType = OracleDbType.Varchar2;
                  //// out params
                  //po_sql_error.OracleDbType = OracleDbType.Varchar2;
                  //po_sid_aa_t.OracleDbType = OracleDbType.Int32;
                  //po_case_plan_id_aa_t.OracleDbType = OracleDbType.Int32;
                  //po_dpp_id_aa_t.OracleDbType = OracleDbType.Int32;
                  //po_dpp_number_aa_t.OracleDbType = OracleDbType.Varchar2;
                  //po_person_id_aa_t.OracleDbType = OracleDbType.Int32;
                  //po_full_name_aa_t.OracleDbType = OracleDbType.Varchar2;
                  //// set the collection type for each parameter
                  //p_job_id.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
                  //p_job_title.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
                  //p_min_salary.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
                  //p_max_salary.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
                  //// set the parameter values
                  //p_job_id.Value = new string[1] { "IT_DBA" };
                  //p_job_title.Value = new string[1] { "Database Administrator" };
                  //p_min_salary.Value = new decimal[1] { 8000 };
                  //p_max_salary.Value = new decimal[1] { 16000};
                  //// set the size for each array
                  //p_job_id.Size = 1;
                  //p_job_title.Size = 1;
                  //p_min_salary.Size = 1;
                  //p_max_salary.Size = 1;
                  //// add parameters to command object collection
                  //// execute the insert
                  //// display the new jobs
                  //cmd.CommandText = "select job_id, job_title from jobs where job_id in ('IT_DBA', 'IT_MAN', 'IT_VP') order by job_id";
                  //cmd.CommandType = CommandType.Text;
                  //OracleDataReader dr = cmd.ExecuteReader();
                  //Console.WriteLine("New jobs have been added to the JOBS table:\n");
                  //while (dr.Read())
                  //    Console.WriteLine("{0,6}: {1}", dr.GetString(0), dr.GetString(1));
                  //// delete the new jobs
                  //cmd.CommandText = "delete from jobs where job_id in ('IT_DBA', 'IT_MAN', 'IT_VP')";
                  //Console.WriteLine("New jobs have been removed from the JOBS table.");
                  //// clean up objects
                  OracleConnection con = new OracleConnection();
                  con.ConnectionString = constr;
                  Oracle.DataAccess.Client.OracleCommand oCommand = new Oracle.DataAccess.Client.OracleCommand();
                  oCommand.CommandText = "PKG_PMTESTING.DPP_TEST";
                  oCommand.CommandType = CommandType.StoredProcedure;
                  oCommand.Connection = con;
                 #region Input Parameters
                  OracleParameter pi_first_name = new OracleParameter();
                  pi_first_name.Value = null;
                  oCommand.Parameters.Add("pi_first_name", OracleDbType.Varchar2, pi_first_name.Value, ParameterDirection.Input);
                  OracleParameter pi_surname = new OracleParameter();
                  pi_surname.Value = null;
                  oCommand.Parameters.Add("pi_surname", OracleDbType.Varchar2, pi_surname.Value, ParameterDirection.Input);
                  OracleParameter pi_sid = new OracleParameter();
                  pi_sid.Value = null;
                  oCommand.Parameters.Add("pi_sid", OracleDbType.Int32, pi_sid.Value, ParameterDirection.Input);
                  OracleParameter pi_facilities_list = new OracleParameter();
                  pi_facilities_list.Value = null;
                  oCommand.Parameters.Add("pi_facilities_list", OracleDbType.Varchar2, pi_facilities_list.Value, ParameterDirection.Input);
                  OracleParameter pi_agent_person_id = new OracleParameter();
                  pi_agent_person_id.Value = null;
                  oCommand.Parameters.Add("pi_agent_person_id", OracleDbType.Int32, pi_agent_person_id.Value, ParameterDirection.Input);
                  OracleParameter pi_open_yn = new OracleParameter();
                  pi_open_yn.Value = "Y";
                  oCommand.Parameters.Add("pi_open_yn", OracleDbType.Varchar2, pi_open_yn.Value, ParameterDirection.Input);
                  #region Output Parameters
                  OracleParameter po_sql_error = new OracleParameter();
                  po_sql_error.DbType = DbType.String;
                  po_sql_error.CollectionType = OracleCollectionType.None;
                  po_sql_error.Direction = ParameterDirection.Output;
                  po_sql_error.Size = 2000;
                  OracleParameter po_dpp_number_aa_t = oCommand.Parameters.Add("po_dpp_number_aa_t", OracleDbType.Varchar2);
                  po_dpp_number_aa_t.Direction = ParameterDirection.Output;
                  po_dpp_number_aa_t.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
                  po_dpp_number_aa_t.Value = null;
                  po_dpp_number_aa_t.Size = 1;
                  po_dpp_number_aa_t.ArrayBindSize = new int[1]{12};
                      OracleDataReader odr = oCommand.ExecuteReader();
                      DataTable dt = new DataTable();
                      int i = 0;
                      for (i = 0; i <= odr.FieldCount - 1; i++)
                      if (odr.HasRows)
                          while (odr.Read())
                              DataRow dr = dt.NewRow();
                              for (i = 0; i <= odr.FieldCount - 1; i++)
                                  dr[odr.GetName(i)] = odr.GetValue(i);
                  catch (Exception e)
              public static void Display(OracleParameterCollection collection)
                  foreach (OracleParameter p in collection)
                      if (p.Direction == ParameterDirection.Output && p.CollectionType == OracleCollectionType.PLSQLAssociativeArray)
                          Console.Write(p.ParameterName + ": ");
                          for (int i = 0; i < p.Size; i++)
                              if (p.Value is OracleString[])
                                  Console.Write((p.Value as OracleString[]));
      Console.Write((p.Value as string[])[i]);
      Console.Write(" ");
        • 1. Re: SELECT records using PL/SQL Associative Array and display to .NET grid
          Just a quick update as we've had some prgress, but are still not quite where we want to be;
          po_dpp_number_aa_t.ArrayBindSize = new int[2]{12,12};
          We can now handle two rows being returned in a single array element, but have hard coded the length (12) twice. The issue we have is we don't know how many rows the element will return to .NET, it could be 2 or it could be 100 or 1000 etc. How do we circumvent having to hard code the number of rows returned and size etc. We just want to define the size once and element count once and have it dynamically expand as required.?

          • 2. Re: SELECT records using PL/SQL Associative Array and display to .NET grid
            from the book odp.net developers guide
            dim p_empno as OracleParameter = _
            .Parameters.Add("v_EmpArray", OracleDBType.Int32, parameterDirection.Output)
            p_empno.CollectionType = OracleCollectionType.PLSQLAssociativeArray
            the above defines an OracleParameter name p_empno as PLSQLAssociativeArray. you must note that it is defined as an output parameter. We are also required to specify the number of values(size) expected
            in that parameter.
            Once the OracleCommand gets executed we retrieve the whole set of values into an array as follows
            Dim Empno() as Oracle.DataAccess.Types.OracleDecimal = p_empno.Value
            Another important point to note is that the number of values you are about to receive must already be known to you for specifying the size. if the value is higher than the number of values being
            received from the database it doesn't really give us any problem. but if the value is lower it certainly raises an error.
            if specifying Size in advance is problematic you are encouraged to opt for the usage of REF CURSOR
            • 3. Re: SELECT records using PL/SQL Associative Array and display to .NET grid
              Thanks for the update, I've fired it off to our .NET Devs to review and implement. Will let you know how we get in.
              • 4. Re: SELECT records using PL/SQL Associative Array and display to .NET grid
                We've reverted back to using Ref Cursors as the .NET Devs couldn't create a data grid from the PL/SQL out parameter (CLOB).