Following are Customer_Type and Customer_Table .Net custom types. Customer_Table type represesnts a collection/array of Customer_Type types.
You could copy these types and change them as per your UDT type requirements. Or you could auto-generate these custom types using ODP .Net “Generate Custom Class" wizard. To see how to auto-generate these custom types using ODP .Net “Generate Custom Class" wizard, refer: How to auto-generate .net custom types using ODP .Net and VS2008.
using System;
using Oracle.DataAccess.Types;
namespace UDT_Sample_Liabrary
{
public class Customer_Type : IOracleCustomType
{
public Customer_Type()
{
// TODO : Add code to initialise the object
}
[OracleObjectMappingAttribute("CUSTOMER_ID")]
public int CustomerId { get; set; }
[OracleObjectMappingAttribute("CUSTOMER_NAME")]
public string CustomerName { get; set; }
#region IOracleCustomType Members
public virtual void FromCustomObject(Oracle.DataAccess.Client.OracleConnection con, System.IntPtr pUdt)
{
Oracle.DataAccess.Types.OracleUdt.SetValue(con, pUdt, "CUSTOMER_ID", this.CustomerId);
if (this.CustomerName != null)
{
Oracle.DataAccess.Types.OracleUdt.SetValue(con, pUdt, "CUSTOMER_NAME", this.CustomerName);
}
}
public virtual void ToCustomObject(Oracle.DataAccess.Client.OracleConnection con, System.IntPtr pUdt)
{
if (!Oracle.DataAccess.Types.OracleUdt.IsDBNull(con, pUdt, "CUSTOMER_ID"))
{
this.CustomerId = ((int)(Oracle.DataAccess.Types.OracleUdt.GetValue(con, pUdt, "CUSTOMER_ID")));
}
if (!Oracle.DataAccess.Types.OracleUdt.IsDBNull(con, pUdt, "CUSTOMER_NAME"))
{
this.CustomerName = ((string)(Oracle.DataAccess.Types.OracleUdt.GetValue(con, pUdt, "CUSTOMER_NAME")));
}
}
#endregion
}
//[OracleCustomTypeMappingAttribute("CUSTOMER_DB_SCHEMA.CUSTOMER_TYPE")]
//Remove this class attribute and add custom type mapping to Config file.
public class Customer_TypeFactory : IOracleCustomTypeFactory
{
public virtual IOracleCustomType CreateObject()
{
Customer_Type obj = new Customer_Type();
return obj;
}
}
}
Customer_Table is a collection is Customer_Type types:
using System;
using Oracle.DataAccess.Types;
namespace UDT_Sample_Liabrary
{
public class Customer_Table : IOracleCustomType
{
public Customer_Table()
{
// TODO : Add code to initialise the object
}
[OracleArrayMappingAttribute()]
public virtual Customer_Type[] Value { get; set; }
public virtual void FromCustomObject(Oracle.DataAccess.Client.OracleConnection con, System.IntPtr pUdt)
{
OracleUdt.SetValue(con, pUdt, 0, this.Value);
}
public virtual void ToCustomObject(Oracle.DataAccess.Client.OracleConnection con, System.IntPtr pUdt)
{
this.Value = ((Customer_Type[])(OracleUdt.GetValue(con, pUdt, 0)));
}
}
//[OracleCustomTypeMappingAttribute("CUSTOMER_DB_SCHEMA.CUSTOMER_TABLE")]
//Remove this class attribute and add custom type mapping to Config file.
public class Customer_TableFactory : IOracleCustomTypeFactory, IOracleArrayTypeFactory
{
public virtual IOracleCustomType CreateObject()
{
Customer_Table obj = new Customer_Table();
return obj;
}
public virtual System.Array CreateArray(int length)
{
Customer_Type[] collElem = new Customer_Type[length];
return collElem;
}
public virtual System.Array CreateStatusArray(int length)
{
return null;
}
}
}
Following configuration file will have the custom type mappings for above created types:
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<sectionGroup name="oracle.dataaccess.client"
type="Oracle.DataAccess.Client.OracleSectionGroup, Oracle.DataAccess, Version=2.111.6.20, Culture=neutral, PublicKeyToken=89b483f429c47342">
<section name="settings"
type="Oracle.DataAccess.Client.SettingsSection, Oracle.DataAccess, Version=2.111.6.20, Culture=neutral, PublicKeyToken=89b483f429c47342" />
<section name="udtCustomTypeMappings"
type="Oracle.DataAccess.Client.UdtCustomTypeMappingsSection, Oracle.DataAccess, Version=2.111.6.20, Culture=neutral, PublicKeyToken=89b483f429c47342" />
</sectionGroup>
<oracle.dataaccess.client>
<settings>
<add name="Customer_Type" value="udtMapping factoryName='UDT_Sample_Liabrary.Customer_TypeFactory,
UDT_Sample_Liabrary' typeName='CUSTOMER_TYPE' schemaName='CUSTOMER_DB_SCHEMA'"/>
<add name="Customer_Table" value="udtMapping factoryName='UDT_Sample_Liabrary.Customer_TableFactory,
UDT_Sample_Liabrary' typeName='CUSTOMER_TABLE' schemaName='CUSTOMER_DB_SCHEMA'"/>
</settings>
</oracle.dataaccess.client>
</configuration>
Following is the oracle stored procedure that takes in the CUSTOMER_TABLE type as input parameter, loop through it and then inserts the records in Customers table:
PROCEDURE insert_customers (i_customer_array in customer_table,
o_new_id out integer) IS
BEGIN
-- loop through the customers
for i in 1..i_customer_array.count loop
INSERT
INTO Customers (customer_id,
Customer_name)
VALUES (i_customer_array(i).CustomerId,
i_customer_array(i).CustomerName);
end loop;
END
Following sample code demonstrates how to call the above created stored procedure with custom types, from .Net enviroment:
Customer_Table cust_table = new Customer_Table
{
Value = new List<Customer_Type>
{
new Customer_Type { CustomerId= 0, CustomerName = "John Trivolta" },
new Customer_Type { CustomerId= 0, CustomerName = "Mark Robinson" }
}.ToArray()
};
OracleConnection conn = new OracleConnection("User Id=user;Password=pwd;Data Source=datasource");
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "INSERT_CUSTOMERS";
// First, lets send this UDT Object type as an input parameter to the stored proc, for storage.
OracleParameter p1 = new OracleParameter();
p1.ParameterName = "I_CUSTOMER_ARRAY";
p1.OracleDbType = OracleDbType.Object;
p1.UdtTypeName = "CUSTOMER_TABLE";
p1.Direction = ParameterDirection.Input;
p1.Value = cust_table;
cmd.Parameters.Add(p1);
OracleParameter p2 = new OracleParameter();
p2.ParameterName = "O_NEW_ID";
p2.Direction = ParameterDirection.Output;
cmd.Parameters.Add(p2);
conn.Open();
cmd.ExecuteNonQuery();
int id = cmd.Parameters["O_NEW_ID"].Value;