代码:
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();
紧随其后是以下帮助类:
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
{
public IOracleCustomType CreateObject()
{
return new VArray();
}
public Array CreateArray(int numElems)
{
return new Int32[numElems];
}
public Array CreateStatusArray(int numElems)
{
return new OracleUdtStatus[numElems];
}
}
描述:
总的来说,与OCI示例类似,您需要将参数强制转换为SYS.ODCINUMBERLIST(或其他有效类型)。该类型在C# OracleDBType中默认未定义,因此您需要使用UdtTypeName和自定义工厂/类才能成功绑定。
这受到了以下帖子中关于定义自定义类型的启发。
限制:
这种解决方案仅适用于INT / NUMBER值,因为它是基于SYS.ODCINUMBERLIST表类型的附带效应。如果需要其他类型,则可能需要找到/编写其他自定义表类型。