Code:
oraParam.UdtTypeName = "SYS.ODCINUMBERLIST";
VArray newArray = new VArray();
newArray.Array = new Int32[] {12,24,42};
oraParam.OracleDbType = OracleDbType.Array;
oraParam.Value = newArray;
string query = @"Select * from TABLE(:1) ";
OracleCommand command = new OracleCommand(query, MyConnection);
command.Parameters.Add(oraParam);
OracleDataReader reader;
var m_connection = new OracleConnection("The CONNECTION STRING");
m_connection.Open();
var reader = command.ExecuteReader();
reader.Close();
m_connection.Close();
Which is followed by the following Helper Classes:
class VArray : IOracleCustomType, INullable
{
[OracleArrayMapping()]
public Int32[] Array;
private OracleUdtStatus[] m_statusArray;
public OracleUdtStatus[] StatusArray
{
get
{
return this.m_statusArray;
}
set
{
this.m_statusArray = value;
}
}
private bool m_bIsNull;
public bool IsNull
{
get
{
return m_bIsNull;
}
}
public static VArray Null
{
get
{
VArray obj = new VArray();
obj.m_bIsNull = true;
return obj;
}
}
public void ToCustomObject(OracleConnection con, IntPtr pUdt)
{
object objectStatusArray = null;
Array = (Int32[])OracleUdt.GetValue(con, pUdt, 0, out objectStatusArray);
m_statusArray = (OracleUdtStatus[])objectStatusArray;
}
public void FromCustomObject(OracleConnection con, IntPtr pUdt)
{
OracleUdt.SetValue(con, pUdt, 0, Array, m_statusArray);
}
public override string ToString()
{
if (m_bIsNull)
return "VArray.Null";
else
{
string rtnstr = String.Empty;
if (m_statusArray[0] == OracleUdtStatus.Null)
rtnstr = "NULL";
else
rtnstr = Array.GetValue(0).ToString();
for (int i = 1; i < m_statusArray.Length; i++)
{
if (m_statusArray[i] == OracleUdtStatus.Null)
rtnstr += "," + "NULL";
else
rtnstr += "," + Array.GetValue(i).ToString();
}
return "VArray(" + rtnstr + ")";
}
}
}
[OracleCustomTypeMapping("SYS.ODCINUMBERLIST")]
public class VArrayFactory : IOracleCustomTypeFactory, IOracleArrayTypeFactory
{
// IOracleCustomTypeFactory
public IOracleCustomType CreateObject()
{
return new VArray();
}
// IOracleArrayTypeFactory Interface
public Array CreateArray(int numElems)
{
return new Int32[numElems];
}
public Array CreateStatusArray(int numElems)
{
// CreateStatusArray may return null if null status information
// is not required.
return new OracleUdtStatus[numElems];
}
}
DESCRIPTION:
The general idea is that similar to the OCI example, you have to cast the parameter as a SYS.ODCINUMBERLIST (Or other valid type). That type is not defined by default in the C# OracleDBType so you have to use the UdtTypeName and a custom Factory/Class to bind successfully.
This was inspired from the following post on defining custom types.
Limits:
This exact solution will only work with INT/NUMBER values because it is piggybacking off of the SYS.ODCINUMBERLIST table type. If it is needed for other types you may need to find/write additional custom table types.
