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;
Oracle.DataAccess.Client.OracleDataReader reader = OracleLayer.Cmd.ExecuteReader();
catch (Exception exc)
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
I forgot to update this topic but few months ago I found out that I had to assign the parameter variable into another one to get the result.
I was exptecting the out parameter in the call of the function would writte the data into it but this is wrong, you have to read your parameter variable to get the result back.