This discussion is archived
3 Replies Latest reply: Mar 18, 2013 6:03 AM by 946366 RSS

UDT Nested - Output parameter - cannot have result back into .Net program

946366 Newbie
Currently Being Moderated
Hello,

As suggested in this Associative Array with more than one field - retrieving as output parameter ,

I did a PL/SQL Stored Procedure with an input parameter (an associative array of varchar2) and an output parameter using the UDT Nested table.

I tested my PL/SQL SP directly in toad. I can see my output parameter filled correctly and I can display the data.

Now I'm trying to retrieve these data in my .Net project.
I generated a class to handle the UDT as explained in this article .

When my .net program calls my PL/SQL SP, the SP is correctly executed and the output parameter is filled correctly.
But in my .net program I have nothing back.

Here is how the output parameter is used :

//this class is generated with visual studio according the definition of myType UDT and the table of myTYPE.
NESTED_myTYPE[] Result = null;

Oracle.DataAccess.Client.OracleParameter param = new Oracle.DataAccess.Client.OracleParameter();
param.OracleDbType = Oracle.DataAccess.Client.OracleDbType.Array;
param.Direction = ParameterDirection.Output;
param.ParameterName = "ptab_Result";
param.UdtTypeName = "mySchema.NESTED_myTYPE";
param.Value = Result;
OracleLayer.Cmd.Parameters.Add(param);

try
{
Oracle.DataAccess.Client.OracleDataReader reader = OracleLayer.Cmd.ExecuteReader();
}
catch (Exception exc)
{
MessageBox.Show(exc.Message);
}

I don't have any exceptions, but my reader is empty.

I think there is something wrong with the way I'm configuring the parameter.

In the ODP.Net sample, in the UDT example, this is quiet similar except the commandType is not a StoredProcedure but a Text one...

For now I'll use a cursor and return the result in my .Net program because I lost too much time trying to use the UDP tech.
But if someone has the solution, I'll be glad to read it.

Thanks in advance,

Edited by: 943363 on 09-juil.-2012 12:33

Legend

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