3 Replies Latest reply: Mar 18, 2013 8:03 AM by 946366 RSS

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

    946366
      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