3 Replies Latest reply: Feb 11, 2009 12:23 PM by 683374 RSS

    Nested Table as PL/SQL Function Return Type

    MarkHoxey
      Hello,

      We're having difficulty retrieving a nested table of UDTs from a PL/SQL package using the latest ODP.Net 11g.

      We've adapted the Nested Table sample code provided with ODP.Net by creating a packaged function that returns the nested table data type and modified the sample .Net code to call this function. We receive the following error:

      Error raised is ‘pTDO’

      Stack trace:
      at Oracle.DataAccess.Types.OracleUdtDescriptor.GetOracleUdtDescriptor(OracleConnection con, IntPtr pTDO, Boolean bRefresh, Boolean& bExists)
      at Oracle.DataAccess.Client.OracleParameter.CreateCustomObject(OracleConnection conn, Int32 index)
      at Oracle.DataAccess.Client.OracleParameter.PostBind_Collection(OracleConnection conn)
      at Oracle.DataAccess.Client.OracleParameter.PostBind(OracleConnection conn, OpoSqlValCtx* pOpoSqlValCtx, Int32 arraySize)
      at Oracle.DataAccess.Client.OracleCommand.ExecuteNonQuery()
      at NestedTableSample.Main(String[] args) in C:\Dev\_Spikes\UDT\NestedTable.cs:line 64

      We're not sure if we have coded things correctly, have a configuration problem or have hit an ODP.Net bug.

      Below is the PL/SQL package code and the modified .Net code. Any pointers would be gratefully received.

      Regards

      Mark

      ------------------------------------------
      CREATE OR REPLACE PACKAGE pkg_odp_nt_person
      AS
      -- returns a nested table populated via BULK COLLECT
      FUNCTION get_person_1
      RETURN odp_nt_sample_person_coll_type;
      END pkg_odp_nt_person;
      /

      CREATE OR REPLACE PACKAGE BODY pkg_odp_nt_person
      AS
      -- returns a nested table populated via BULK COLLECT
      FUNCTION get_person_1
      RETURN odp_nt_sample_person_coll_type
      AS
      l_persons odp_nt_sample_person_coll_type;
      BEGIN
      SELECT col1
      INTO l_persons
      FROM odp_nt_sample_person_rel_tab
      WHERE rownum = 1;

      RETURN l_persons;
      END get_person_1;
      END pkg_odp_nt_person;
      /
      ------------------------------------------
      The following is the replacement NestedTableSample class. The rest of the code is as per the original NestedTable.cs sample with the exception of updating the schema name in the mapping classes.


      class NestedTableSample
      {
      static void Main(string[] args)
      {
      //modify as required
      string constr = "user id=udt_test;password=udt_test;data source=DEV";


      try
      {
      Person[] persons = null;
      using (OracleConnection connection = new OracleConnection(constr))
      {
      using (OracleCommand command = connection.CreateCommand() as OracleCommand)
      {
      command.CommandType = CommandType.StoredProcedure;
      command.CommandText = "PKG_ODP_NT_PERSON.GET_PERSON_1";

      //Return value.
      OracleParameter param1 = new OracleParameter();
      param1.OracleDbType = OracleDbType.Array;
      param1.Direction = ParameterDirection.ReturnValue;
      param1.UdtTypeName = "UDT_TEST.ODP_NT_SAMPLE_PERSON_COLL_TYPE";
      command.Parameters.Add(param1);

      connection.Open();
      command.ExecuteNonQuery();

      persons = (param1.Value == DBNull.Value) ? null : (Person[])param1.Value;
      }
      }
      }
      catch (Exception e)
      {
      System.Console.WriteLine(e);
      }

      }
      }
        • 1. Re: Nested Table as PL/SQL Function Return Type
          24208
          Hi Mark,

          First - apologies this is not a completely thought-out answer and relies on my bad memory!

          Second - I've not run your code...

          OK, disclaimers out of the way, I recall that I experienced a similar issue when I did not implement the CreateStatusArray method in the implementation of IOracleArrayTypeFactory for the arrary factory. If you are using the provided sample code it would mean something like this:
          [OracleCustomTypeMappingAttribute("SCOTT.ODP_NT_SAMPLE_PERSON_COLL_TYPE")]
          public class PersonArrayFactory : IOracleArrayTypeFactory
          {
            // IOracleArrayTypeFactory Inteface
            public Array CreateArray(int numElems)
            {
              return new Person[numElems];
            }
          
            public Array CreateStatusArray(int numElems)
            {
              // An OracleUdtStatus[] is not required to store null status information
              // return null;
          
              return new OracleUdtStatus[numElems];
            }
          }
          Perhaps that is some help...

          Regards,

          Mark
          • 2. Re: Nested Table as PL/SQL Function Return Type
            MarkHoxey
            Thanks for your reply Mark,

            Yes, you are quite right, the CreateArrayStatus needs the implementation you described.

            As it turned out, the source of our original problem was a corrupt ODP.Net installation. The sample as I posted is now working. However, we're now looking into more complex object models and are hitting a memory corruption error. I may post a follow-up scenario in the next day or two if we cannot resolve the problem.

            Regards

            Mark
            • 3. Re: Nested Table as PL/SQL Function Return Type
              683374
              Hi,

              I've a similar issue that I'm unable to resolve. I get a System.NullReference exception from the OracleUDT.setValue() method. Here's my code. Please help...


              -- a custom object type
              CREATE TYPE {color:#0000ff}TYPE_EventNotificationObj{color} AS OBJECT (EVENTCATEGORYKEY INT, USERKEY INT) FINAL INSTANTIABLE;

              -- a table of custom object types
              CREATE TYPE {color:#0000ff}TYPE_EventNotificationTable{color} AS TABLE OF{color:#0000ff} TYPE_EventNotificationObj{color};

              PROCEDURE sp_ClientPreference_set(v_NotificationList in {color:#0000ff}TYPE_EventNotificationTable{color}) IS
              v_tmp_EventNotificationKey number;
              begin
              for i in 1..v_NotificationList.Count loop
              sp_ADDEVENTNOTIFICATION(
              V_EVENTCATEGORYKEY => v_NotificationList(i).EventCategoryKey,
              V_USERKEY => v_NotificationList(i).UserKey,
              V_EVENTNOTIFICATIONKEY => v_tmp_EventNotificationKey);
              end loop;
              END sp_ClientPreference_set;


              In my C# class:*

              I've used the Oracle Object Wizard to generate a class out of the custom object type. The class name is TYPEEventNotificationObj_ *that derives from INullable, IOracleCustomType, IxmlSerializable.

              Inside my data access layer I've defined a method as follows:

              public static void SetClientPreference(List<EventNotification> notificationList)
              *{*
              {color:#0000ff}OracleParameter{color} pEventNotify = new {color:#0000ff}OracleParameter{color}("v_NotificationList", {color:#3366ff}OracleDataType.Array{color}, {color:#3366ff}ParameterDirection.Input{color});

              // EventNotificationObject is the class that was generated by the Oracle object wizard
              {color:#0000ff}TYPE_EventNotificationObj{color}[] notificationObjectArray = new {color:#0000ff}TYPE_EventNotificationObj{color}[notificationList.Count];

              // load EventNotificationObject from the EventNotification list
              int i = 0;
              foreach ({color:#0000ff}EventNotification{color} en in notificationList)
              {
              {color:#0000ff}TYPE_EventNotificationObj{color} notificationObject = new {color:#0000ff}TYPE_EventNotificationObj{color}();
              notificationObject.EVENTCATEGORYKEY = en.EventCategoryKey;
              notificationObject.USERKEY = en.UserKey.Value;
              notificationObjectArray[i] = notificationObject;
              i++;
              }

              pEventNotify.ArrayBindSize = new int[notificationObjectArray.Length];
              pEventNotify.Value = notificationObjectArray;
              //pEventNotify.Size = notificationObjectArray.Length;

              {color:#0000ff}OracleConnection{color} cnx = new {color:#0000ff}OracleConnection{color}(connectionString);
              {color:#0000ff}OracleCommand{color} cmd = new {color:#0000ff}OracleCommand{color}("*sp_ClientPreference_set*", cnx);
              cmd.CommandType = {color:#3366ff}CommandType.StoredProcedure{color};

              cmd.Parameters.Add(pEventNotify);

              cnx.Open();
              cmd.ExecuteScalar();
              *}*

              here's the auto-gen code of the custom type that Oracle Wizard produced...

              public class TYPE_EventNotificationObject: INullable, IOracleCustomType, IXmlSerializable
              {
              private bool m_IsNull, m_EventCategoryKeyIsNull, m_UserKeyIsNull;
              private decimal m_EventCategoryKey, m_UserKey;

              .....
              }

              --- and the Factory class...
              [OracleCustomTypeMappingAttribute("ONLUSER.TYPE_EVENTNOTIFICATIONOBJ")]
              public class TYPE_EventNotificationObjFactory : IOracleCustomTypeFactory
              {
              public virtual IOracleCustomType CreateObject()
              {
              return new {color:#0000ff}TYPE_EventNotificationObj{color}();
              }
              }