How to use Oracle 11g ODP.NET UDT in an Oracle Stored Procedure's Where clause

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];
    }
}