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
Procedure is defined as
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 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
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.
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 classpublic 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();
}
}
