This discussion is archived
0 Replies Latest reply: Sep 18, 2012 12:30 PM by user5203716 RSS

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

user5203716 Newbie
Currently Being Moderated
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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points