odp.net 自定义类型的使用
Introduction
This code shows how to use Oracle's User Defined Types such as VARRAY
using ODP.NET in a WHERE
clause of a query in an Oracle Stored Procedure. I am passing three parameters into my Oracle Stored Procedure: the first parameter is a UDT VARRAY
as number
(ParameterDirection.IN
); the second parameter is a UDT VARRAY
as number
(ParameterDirection.IN
); the third parameter is a refcursor
which contains the result (ParameterDirection.Output
).
Prerequisites: ODP.NET 11g, Visual Studio 2005 and 2008.
Background
In previous versions of ODP.NET, we did not have User Defined Type support by Oracle for .NET. Therefore, in order to accomplish the same task, we had to first insert the array values into temporary table(s) and then use the values from that temporary table in a WHERE
clause.
Using the Code
Create a type odp_varray_sample_type
as a varray(3000)
of number
.
/
CREATE OR REPLACE
procedure odp_varray_sample_proc(PARAM1 IN OUT ODP_VARRAY_SAMPLE_TYPE,
PARAM2 IN OUT ODP_VARRAY_SAMPLE_TYPE, param3 OUT TYPES.cursor_type) as
local_param TYPES.cursor_type;
begin
OPEN local_param FOR
select * from sched_gen_report_detail where reporter_sid in (select *
from table(cast(param1 as ODP_VARRAY_SAMPLE_TYPE)))
union
select * from sched_gen_report_detail where item_sid in (select * from table(
cast(param2 as ODP_VARRAY_SAMPLE_TYPE)));
param3 := local_param;
END ODP_VARRAY_SAMPLE_PROC;
/
*/
Here is the C# code:
using System;
using System.Data;
using System.Collections;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
class VArraySample
{
static void Main(string[] args)
{
DataSet ds ;
OracleDataAdapter myAdapter =null;
Oracle.DataAccess.Types.OracleRefCursor refcur = null;
string constr = "user id=PPI_UDB_FORMS;password=;" +
"data source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=" +
"(PROTOCOL=TCP)(HOST=cosmo.psb.bls.gov)(PORT=1521)))(" +
"CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=" +
"udbdev.psb.bls.gov)));User Id=PPI_UDB_FORMS;Password=aaaaaa;";
string sql1 = "odp_varray_sample_proc";
// create a new simple varray with values 1, 2, 3, and 4.
SimpleVarray pa = new SimpleVarray();
pa.Array = new Int32[] { 100018035, 100024174, 100022751, 100024637,
100027800, 100022749, 100023094, 100027800,
100011261, 100019536, 100007392, 100016106 };
SimpleVarray pa2 = new SimpleVarray();
pa2.Array = new Int32[] { 100000480, 100000481 };
// create status array indicate element 2 is Null
//pa.StatusArray = new OracleUdtStatus[] { OracleUdtStatus.NotNull,
// OracleUdtStatus.Null, OracleUdtStatus.NotNull, OracleUdtStatus.NotNull };
// Establish a connection to Oracle
OracleConnection con = new OracleConnection(constr);
con.Open();
OracleCommand cmd = new OracleCommand(sql1, con);
cmd.CommandType = CommandType.StoredProcedure;
OracleParameter param = new OracleParameter();
param.OracleDbType = OracleDbType.Array;
param.Direction = ParameterDirection.InputOutput;
// Note: The UdtTypeName is case-senstive
param.UdtTypeName = "PPI_UDB_FORMS.ODP_VARRAY_SAMPLE_TYPE";
param.Value = pa;
cmd.Parameters.Add(param);
OracleParameter param2 = new OracleParameter();
param2.OracleDbType = OracleDbType.Array;
param2.Direction = ParameterDirection.InputOutput ;
// Note: The UdtTypeName is case-senstive
param2.UdtTypeName = "PPI_UDB_FORMS.ODP_VARRAY_SAMPLE_TYPE";
param2.Value = pa2;
cmd.Parameters.Add(param2);
OracleParameter p2 = cmd.Parameters.Add("param2",
OracleDbType.RefCursor, ParameterDirection.Output);
// Insert SimpleVarray(1,NULL,3,4,9) into the table
cmd.ExecuteNonQuery();
refcur = (Oracle.DataAccess.Types.OracleRefCursor)p2.Value;
myAdapter = new OracleDataAdapter ("",con);
ds = new DataSet("testDS");
myAdapter.Fill (ds, refcur);
// Clean up
cmd.Dispose();
con.Close();
con.Dispose();
}
}
/* SimpleVarray Class
** An instance of a SimpleVarray class represents an
** ODP_VARRAY_SAMPLE_TYPE object
** A custom type must implement INullable and IOracleCustomType interfaces
*/
public class SimpleVarray : 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 SimpleVarray Null
{
get
{
SimpleVarray obj = new SimpleVarray();
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 "SimpleVarray.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 "SimpleVarray(" + rtnstr + ")";
}
}
}
/* SimpleVarrayFactory Class
** An instance of the SimpleVarrayFactory class is used to create
** SimpleVarray objects
*/
[OracleCustomTypeMapping("PPI_UDB_FORMS.ODP_VARRAY_SAMPLE_TYPE")]
public class SimpleVarrayFactory : IOracleCustomTypeFactory, IOracleArrayTypeFactory
{
// IOracleCustomTypeFactory
public IOracleCustomType CreateObject()
{
return new SimpleVarray();
}
// IOracleArrayTypeFactory Inteface
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];
}
}