0 Replies Latest reply: Sep 18, 2012 2:30 PM by user5203716 RSS

    How do u create a datatable from PLSQL Associative Arrays returned from SP

    user5203716
      Using C# 4.0 ODP.NET 11g, vs2010 on Windows XP.

      Have a procedure that takes two input values and returns 35 PL\SQL Associative Arrays (pl/sql tables). I am currently able to call the proc and have it returned the values. i end up with 35 arrays each 35 elements in length.

      I want to create a datatable from them but am at a lost on how to do so.

      This what i have

      Oracle.DataAccess.Client.OracleCommand oCommand = new Oracle.DataAccess.Client.OracleCommand();
      oCommand.CommandText = ProcName;
      oCommand.CommandType = CommandType.StoredProcedure;

      //Input Parameters
      OracleParameter param1 = oCommand.Parameters.Add("p_orderid",OracleDbType.Int32);
      param1.Direction = ParameterDirection.Input;
      param1.Value = OrderID;

      OracleParameter param2 = oCommand.Parameters.Add("p_testinstanceid", OracleDbType.Int32);
      param2.Direction = ParameterDirection.Input;
      param2.Value = TestInstanceID;

      //Output Parameters
      OracleParameter param3 = oCommand.Parameters.Add("program_id", OracleDbType.Int32);
      param3.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
      param3.Direction = ParameterDirection.Output;
      param3.Size = 50;

      OracleParameter param4 = oCommand.Parameters.Add("normyear", OracleDbType.Varchar2);
      param4.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
      param4.Direction = ParameterDirection.Output;
      param4.Size = 50;
      param4.ArrayBindSize = new int[50];
      // set the bind size value for each element
      for (int i = 0; i < 50; i++)
      {
      param4.ArrayBindSize[i] = 10;
      }

      ...33 more output parameters all PLSQLAssociativeArray type

      oCommand.ExecuteNonQuery();

      OracleDataAdapter da = new OracleDataAdapter(oCommand);

      //Fill the DataTable
      da.Fill(dt);

      The datatable is empty. What am i doing wrong? Or this there a better way to get the output values to end up in a datatable. Note that when i examine the parameters in oCommand i see that they all the the right out values.