Wednesday, September 1, 2010

Pass Custom UDT types to Oracle Stored Procedure using ODP.NET

Introduction: I came across a situation where I have a list of .Net custom objects and I want to pass this list to an Oracle procedure to process them. I did not want to loop through and update each record as I wanted to reduce the number of network round trips. And I found the following way to accomplish this. I had to create a User Defined Type (UDT) / Custom Type in Oracle that represents my .Net Object and another UDT as a table of these objects. Then I used ODP.Net to map between my .Net Custom Objects to Oracle UDT types


Prerequisites: ODP.NET 11g, Visual Studio 2005 / 2008 / 2010

Following are the UDT declarations in Oracle
----------------------------------------------------------------
create or replace type "COMPANYINFO" AS OBJECT
(
    CompanyId     varchar2(15),
    CompanyName   varchar2(50),
    Address1      varchar2(100),
    Address2      varchar2(100),
    City          varchar2(20),
    State         varchar2(20),
    Zip           varchar2(10),
    Country       varchar2(20)
);
   
create or replace type "COMPANYINFOLIST" is table of COMPANYINFO;
----------------------------------------------------------------

Procedure is defined as
----------------------------------------------------------------
procedure GetCompanyInfoArray(companyInfoList in COMPANYINFOLIST, resultCursor out sys_refcursor) is
 results stringSet;
 compName varchar2(100);
 begin
      if companyInfoList.count > 0 then
         results := stringSet();
          for i in companyInfoList.first .. companyInfoList.last
          loop 
           -- dbms_output.put_line(companyInfoList(i).companyId);              
              results.extend(1);
              results(results.count) := companyInfoList(i).companyName;  
          end loop;                   
      end if;  
      
      open resultCursor for
            select column_value as compName from table(cast (results as stringSet));
 end;
----------------------------------------------------------------
Following type is used by the procedure to add company names back to the return cursor
----------------------------------------------------------------
CREATE OR REPLACE TYPE "STRINGSET" is table of varchar(32000);
----------------------------------------------------------------

Open Visual Studio and create a new sample project "OracleUDTSample"
Add reference to Oracle.DataAccess.dll to the project. This dll is located in oracle home directory as shown.




Add following classes to the project
  • CompanyInfoThis class to represent Oracle type "COMPANYINFO" This class needs to inherit following interfaces - INullable, IOracleCustomType. Implement the INullable method IsNull  and IOracleCustomType methods FromCustomObject, ToCustomObject.
  • CompanyInfoFactory - This class is required by ODP.NET to create custom objects that represent oracle custom objects and collections. This should implement IOracleCustomTypeFactory.
  • CompanyInfoList -This class to represent Oracle type "COMPANYINFOLIST". This type is used to pass a list of COMPANYINFO objects. This class also implements INullable, IOracleCustomType interfaces. This class has an array of CompanyInfo property to capture the CompanyInfo object list.
  • CompanyInfoListFactory This class is required by ODP.NET to create custom objects that represent oracle custom objects and collections. This should also implement OracleCustomTypeFactory.




using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Oracle.DataAccess.Types;

namespace OracleUDTSample
{
    /* CompanyInfo Class
    **   An instance of a CompanyInfo class represents an CompanyInfo object
    **   A custom type must implement INullable and IOracleCustomType interfaces
    */
    public class CompanyInfo : INullable, IOracleCustomType
    {
        private bool objectIsNull;

        [OracleObjectMappingAttribute("COMPANYID")]
        public string CompanyId { get; set; }
        
        [OracleObjectMappingAttribute("COMPANYNAME")]
        public string CompanyName { get; set; }

        [OracleObjectMappingAttribute("ADDRESS1")]
        public string Address1 { get; set; }

        [OracleObjectMappingAttribute("ADDRESS2")]
        public string Address2 { get; set; }

        [OracleObjectMappingAttribute("CITY")]
        public string City { get; set; }

        [OracleObjectMappingAttribute("STATE")]
        public string State { get; set; }

        [OracleObjectMappingAttribute("ZIP")]
        public string Zip { get; set; }

        [OracleObjectMappingAttribute("COUNTRY")]
        public string Country { get; set; }

        public static CompanyInfo Null
        {
            get
            {
                CompanyInfo company = new CompanyInfo();
                company.objectIsNull = true;
                return company;
            }
        }

        #region INullable Members

        public bool IsNull
        {
            get { return objectIsNull; }
        }

        #endregion

        #region IOracleCustomType Members

        public void FromCustomObject(Oracle.DataAccess.Client.OracleConnection con, IntPtr pUdt)
        {
            // Convert from the Custom Type to Oracle Object
            if (!string.IsNullOrEmpty(CompanyId))
            {
                OracleUdt.SetValue(con, pUdt, "COMPANYID", CompanyId);
            }
            if (!string.IsNullOrEmpty(CompanyName))
            {
                OracleUdt.SetValue(con, pUdt, "COMPANYNAME", CompanyName);
            }
            if (!string.IsNullOrEmpty(Address1))
            {
                OracleUdt.SetValue(con, pUdt, "ADDRESS1", Address1);
            }
            if (!string.IsNullOrEmpty(Address2))
            {
                OracleUdt.SetValue(con, pUdt, "ADDRESS2", Address2);
            }
            if (!string.IsNullOrEmpty(City))
            {
                OracleUdt.SetValue(con, pUdt, "CITY", City);
            }
            if (!string.IsNullOrEmpty(State))
            {
                OracleUdt.SetValue(con, pUdt, "STATE", State);
            }
            if (!string.IsNullOrEmpty(Zip))
            {
                OracleUdt.SetValue(con, pUdt, "ZIP", Zip);
            }
            if (!string.IsNullOrEmpty(Country))
            {
                OracleUdt.SetValue(con, pUdt, "COUNTRY", Country);
            }

        }

        public void ToCustomObject(Oracle.DataAccess.Client.OracleConnection con, IntPtr pUdt)
        {
            CompanyId = (string)OracleUdt.GetValue(con, pUdt, "COMPANYID");
            CompanyName = (string)OracleUdt.GetValue(con, pUdt, "COMPANYNAME");
            Address1 = (string)OracleUdt.GetValue(con, pUdt, "ADDRESS1");
            Address2 = (string)OracleUdt.GetValue(con, pUdt, "ADDRESS2");
            City = (string)OracleUdt.GetValue(con, pUdt, "CITY");
            State = (string)OracleUdt.GetValue(con, pUdt, "STATE");
            Zip = (string)OracleUdt.GetValue(con, pUdt, "ZIP");
            Country = (string)OracleUdt.GetValue(con, pUdt, "COUNTRY");
        }

        #endregion

    }

    [OracleCustomTypeMappingAttribute("DATABASE_SCHEMA_NAME.COMPANYINFO")]
    public class CompanyInfoFactory : IOracleCustomTypeFactory
    {
        #region IOracleCustomTypeFactory Members

        public IOracleCustomType CreateObject()
        {
            return new CompanyInfo();
        }

        #endregion
    }


   
   /* CompanyInfoList Class
   **   An instance of a CompanyInfoList class represents an CompanyInfoList object
   **   A custom type must implement INullable and IOracleCustomType interfaces
   */
    public class CompanyInfoList : INullable, IOracleCustomType
    {
        [OracleArrayMapping()]
        public CompanyInfo[] CompanyInfoArray;

        private bool objectIsNull;

        #region INullable Members

        public bool IsNull
        {
            get { return objectIsNull; }
        }

        public static CompanyInfoList Null
        {
            get
            {
                CompanyInfoList obj = new CompanyInfoList();
                obj.objectIsNull = true;
                return obj;
            }
        }

        #endregion

        #region IOracleCustomType Members

        public void FromCustomObject(Oracle.DataAccess.Client.OracleConnection con, IntPtr pUdt)
        {
            OracleUdt.SetValue(con, pUdt, 0, CompanyInfoArray);
        }

        public void ToCustomObject(Oracle.DataAccess.Client.OracleConnection con, IntPtr pUdt)
        {
            CompanyInfoArray = (CompanyInfo[])OracleUdt.GetValue(con, pUdt, 0);
        }

        #endregion
    }

    [OracleCustomTypeMapping("DATABASE_SCHEMA_NAME.COMPANYINFOLIST")]
    public class CompanyInfoListFactory :  IOracleCustomTypeFactory, IOracleArrayTypeFactory
    {
        #region IOracleCustomTypeFactory Members
        public IOracleCustomType CreateObject()
        {
            return new CompanyInfoList();
        }

        #endregion

        #region IOracleArrayTypeFactory Members
        public Array CreateArray(int numElems)
        {
            return new CompanyInfo[numElems];
        }

        public Array CreateStatusArray(int numElems)
        {
            return null;
        }

        #endregion
    }
}

Add new class DBAccess to add the database handling code. In this class we add CreateCustomTypeArrayInputParameter method to create our new OracleParameter to pass User Defined Type to Oracle. Make sure to add the following using statements -
using Oracle.DataAccess.Client
using Oracle.DataAccess.Types

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;

namespace OracleUDTSample
{
    public class DBAccess : IDisposable
    {
        OracleConnection connection;
        public OracleCommand CreateCommand(string sql, CommandType type, params OracleParameter[] parameters)
        {
            connection = new OracleConnection("Data Source=database_name;User ID=userid;Password=pwd;Pooling=true;Connection Lifetime=600;Max Pool Size=10;Min Pool Size=0");
            connection.Open();
            OracleCommand command = new OracleCommand(sql, connection);
            command.CommandType = type;
            if (parameters != null && parameters.Length > 0)
            {
                OracleParameterCollection cmdParams = command.Parameters;
                for (int i = 0; i < parameters.Length; i++) { cmdParams.Add(parameters[i]); }
            }
            return command;
        }

        public OracleDataReader GetDataReader(string storedProcedure, params OracleParameter[] parameters)
        {
            return CreateCommand(storedProcedure, CommandType.StoredProcedure, parameters).ExecuteReader();
        }

        public static OracleParameter CreateCursorParameter(string name)
        {
            OracleParameter prm = new OracleParameter(name, OracleDbType.RefCursor);
            prm.Direction = ParameterDirection.Output;
            return prm;
        }

        /*
         * Create this parameter when you want to pass Oracle User-Defined Type (Custom Type) which is table of Oracle User-Defined Types.                  
         * This way you can pass mutiple records at once.
         * 
         * Parameters:
         * name - Name of the UDT Parameter name in the Stored Procedure.
         * oracleUDTName - Name of the Oracle User Defined Type with Schema Name. (Make sure this is all caps. For ex: DESTINY.COMPANYINFOLIST)
         * 
         * */
        public static OracleParameter CreateCustomTypeArrayInputParameter<t>(string name, string oracleUDTName, T value) where T : IOracleCustomType, INullable
        {
            OracleParameter parameter = new OracleParameter();
            parameter.ParameterName = name;
            parameter.OracleDbType = OracleDbType.Array;
            parameter.Direction = ParameterDirection.Input;
            parameter.UdtTypeName = oracleUDTName;
            parameter.Value = value;
            return parameter;
        }

        #region IDisposable Members

        public void Dispose()
        {
            if (connection != null)
                connection = null;
        }

        #endregion
    }
}

Create a new CompanyInfoInterface class which defines a method GetCompanyInfo. This method is creates the procedure parameters and calls the procedure using the DBAccess class.
public class CompanyInfoInterface
{
   #region Constants
   private const string procName = "Package_Name.GetCompanyInfoArray";
   #endregion

   public static List<string> GetCompanyinfo(List<companyinfo> companyList)
   {
     List<string> companyNames = new List<string>();
     CompanyInfoList companyInfoList = new CompanyInfoList();
     companyInfoList.CompanyInfoArray = companyList.ToArray();

     using (DBAccess context = new DBAccess())
     {
         try
         {
            OracleParameter[] parameters = new OracleParameter[2];
            parameters[0] = DBAccess.CreateCustomTypeArrayInputParameter<companyinfolist>("companyInfoList", "DATABASE_SCHEMA.COMPANYINFOLIST", companyInfoList);
            parameters[1] = DBAccess.CreateCursorParameter("resultCursor");
            using (OracleDataReader dr = context.GetDataReader(procName, parameters))
            {
               while (dr.Read())
               {
                 companyNames.Add(dr.IsDBNull(0) ? string.Empty : dr.GetString(0));
               }
           }
         }
         catch
         {
           throw;
         }
         return companyNames;
     }
   }
 }
Finally the Test class
public class Test
{
    static void Main(string[] args)
    {
       List<CompanyInfo> companyList = new List<CompanyInfo>();
       CompanyInfo company1 = new CompanyInfo();
       company1.CompanyId = "AAA";
       company1.CompanyName = "Sample Company 1";
       company1.Address1 = "123 West Washington";
       company1.Address2 = "Suite 200";
       company1.City = "Chicago";
       company1.State = "IL";
       company1.Zip = "60606";
       company1.Country = "USA";
       companyList.Add(company1);

       company1 = new CompanyInfo();
       company1.CompanyId = "BBB";
       company1.CompanyName = "Sample Company 2";
       company1.Address1 = "990 West Jackson";
       company1.Address2 = "Suite 1000";
       company1.City = "Chicago";
       company1.State = "IL";
       company1.Zip = "60606";
       company1.Country = "USA";

       companyList.Add(company1);
       List<string> result = CompanyInfoInterface.GetCompanyinfo(companyList);

       string val = result.Where(c => c == "Sample Company 2").SingleOrDefault<string>();
       Console.WriteLine(val);
       Console.ReadLine();
   }
}

34 comments:

  1. Can you give me any pointers on using packages correctly? I'm trying to use hz_party_v2pub.get_organization_rec but am having no luck. I believe I had defined an object in C# correctly and am passing it to the procedure but of course it bombs. Perhaps I'm not passing it correctly? Can you tell me what parameter options I have to set and to what values?

    Thanks,

    George

    ReplyDelete
  2. Hi,

    When i run above code in my project (Visual Studion 2010, 4.0 framework) i am getting following error at below line of code. Please help me out, what i missed.

    public static OracleParameter CreateCustomTypeArrayInputParameter(string name, string oracleUDTName, T value) where T : IOracleCustomType, INullable

    Below errors are thrown by .net framework

    ReplyDelete
    Replies
    1. This comment has been removed by the author.

      Delete
  3. Try this
    public static OracleParameter CreateCustomTypeArrayInputParameter'<'T'>'(string name, string oracleUDTName, T value) where T : IOracleCustomType,INullable

    Note: remove ' sign

    ReplyDelete
  4. This code is really straight forward and easy to use.
    Thanks a lot.

    ReplyDelete
  5. i'm a new to oracle and at that time this blog is really helpful to me ..... thanks

    ReplyDelete
  6. Is there a way to do this with the new Managed Data Access API?

    ReplyDelete
  7. Great example!, Thanks!

    ReplyDelete
  8. Thanks so much - I'd never have worked that out from Oracle's documentation!

    ReplyDelete
  9. There is an automatic code generation wizard included with the free Oracle Developer Tools for Visual Studio. So you do not need to copy this code, you can simply auto generate it. For more information on how to use this see: https://apexapps.oracle.com/pls/apex/f?p=44785:24:103077088849673:::24:P24_CONTENT_ID,P24_PROD_SECTION_GRP_ID,P24_PREV_PAGE:10199,,24

    ReplyDelete
  10. Hello,
    This is an excellent example. However, I think I need some additional assistance.
    Would you happen to have some insight on this:

    My custom types are defined inside a package and when I apply the parameter, I receive an error {OCI-22303: type "XX_SR_PKG\".\"XX_SERIAL_TBL\" not found}

    The types in the package xx_sr_pkg are:

    type xx_serial_rec is record
    (
    serial_number varchar2(4000),
    serial_comment varchar2(4000)
    );

    type xx_serial_table is table of xx_serial_rec

    ReplyDelete
    Replies
    1. The Create Parameter call:

      DBAccess.CreateCustomTypeArrayInputParameter("serial_table" , "XXFTK_SR_PKG.XX_SERIAL_TBL" , SerialNumberInfoList);

      public static OracleParameter CreateCustomTypeArrayInputParameter(string name, string oracleUDTName, T value) where T : IOracleCustomType , INullable
      {
      OracleParameter parameter = new OracleParameter ();
      parameter.ParameterName = name;
      parameter.OracleDbType = OracleDbType.Array;
      parameter.Direction = ParameterDirection.Input;
      parameter.UdtTypeName = oracleUDTName;
      parameter.Value = value;
      return parameter;
      }

      Delete
    2. Kaze, have you resolved this issue? I am also facing the same error.

      Delete
  11. Thanx for your excellent example However, I have an error.
    "value does not fall within the expected".
    how can I fix that, can you help me?

    ReplyDelete
  12. Worked perfectly! Also works if using a VARRAY of Objects.

    ReplyDelete
  13. With the above example i get an error saying "Invalid parameter binding
    Parameter name: companyInfoList".

    ReplyDelete
  14. This comment has been removed by a blog administrator.

    ReplyDelete
  15. while running getting error:
    {"OCI-22303: type \"DATABASE_SCHEMA\".\"COMPANYINFOLIST\" not found"}

    ReplyDelete
  16. This is one of my favorite performance enhancers using .Net and SQL Server. Glad to see it is available in Oracle, as well. Thanks for the articel

    ReplyDelete
  17. Thanks for sharing this,i was looking for this and it worked perfectly fine.
    Regarding the error most of people are facing on below line
    public static OracleParameter CreateCustomTypeArrayInputParameter(string name, string oracleUDTName, T value) where T : IOracleCustomType, INullable

    You just need to replace small t with capital T in the place

    ReplyDelete
  18. You saved my day, thanks !

    ReplyDelete
  19. Does this work in Oracle 10g also ?

    ReplyDelete
    Replies
    1. Yes it works in Oracle 10g & Oracle 12R1 & Oracle 12R2.

      Delete
  20. hi, i have difined a table type of a custom row type in MY_PACKAGE_G. while running i get error MY_PACKAGE_G_MY_TABLE_TYPE not found

    ReplyDelete
    Replies
    1. Both the User Defined Type and the Collection have to be created at schema level, not package level.

      Delete