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

• CompanyInfo - This 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();
   }
}