Click here to Skip to main content
15,942,847 members
Articles / Programming Languages / SQL

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

Rate me:
Please Sign up or sign in to vote.
4.14/5 (5 votes)
3 Mar 2009CPOL 75K   4   20   5
Passing a VARRAY custom type from .NET and using it in a Where clause in an Oracle Stored Procedure.


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.


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.

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;
 OPEN local_param FOR
 select * from sched_gen_report_detail where reporter_sid in (select * 
    from table(cast(param1 as ODP_VARRAY_SAMPLE_TYPE)))
 select * from sched_gen_report_detail where item_sid in (select * from table(
    cast(param2 as ODP_VARRAY_SAMPLE_TYPE)));
 param3 := local_param;

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)(" + 
           ";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);
        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;

        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;
        OracleParameter p2 = cmd.Parameters.Add("param2", 
                             OracleDbType.RefCursor, ParameterDirection.Output);
        // Insert SimpleVarray(1,NULL,3,4,9) into the table
        refcur = (Oracle.DataAccess.Types.OracleRefCursor)p2.Value;
        myAdapter = new OracleDataAdapter ("",con);
        ds = new DataSet("testDS");
        myAdapter.Fill (ds, refcur);
        // Clean up

/* SimpleVarray Class
**  An instance of a SimpleVarray class represents an
**  A custom type must implement INullable and IOracleCustomType interfaces
public class SimpleVarray : IOracleCustomType, INullable
    public Int32[] Array;
    private OracleUdtStatus[] m_statusArray;
    public OracleUdtStatus[] StatusArray
            return this.m_statusArray;
            this.m_statusArray = value;

    private bool m_bIsNull;

    public bool IsNull
          return m_bIsNull;

    public static SimpleVarray Null
            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";
            string rtnstr = String.Empty;
            if (m_statusArray[0] == OracleUdtStatus.Null)
                rtnstr = "NULL";
                rtnstr = Array.GetValue(0).ToString();
            for (int i = 1; i < m_statusArray.Length; i++)
                if (m_statusArray[i] == OracleUdtStatus.Null)
                    rtnstr += "," + "NULL";
                    rtnstr += "," + Array.GetValue(i).ToString();
            return "SimpleVarray(" + rtnstr + ")";

/* SimpleVarrayFactory Class
**   An instance of the SimpleVarrayFactory class is used to create 
**   SimpleVarray objects
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];


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Written By
Architect BAE Systems IT
United States United States
BAE Systems IT (Client – US BLS) (Washington, DC), SEP 2004 – Present
Senior Software Architect
Duties and Accomplishments – Designed, Documented, Implemented, & Tested:
1. Currently in Progress: IPS Collection System (Java 6, Oracle 11g, WebLogic 11g, SOA, JNDI, JMS, EJB, SQL Developer, Star UML, Adobe Flex/AIR, DI/IOC Parsley Framework Container, Live Cycle Data Services DMS, SQLite & RIA Online/Offline Synchronization with Distributed Transactions).

2. Currently in Progress: Additional 10 hours/week from home for a different department (Sean Curran’s) in order to implement Jasper Reports with Spring RESTFUL services according to user’s requirements by utilizing Spring toolkit IDE, IReport Designer for Jasper Reports, Weblogic and Oracle PL/SQL.

3. In Production: JANUS Forms & Batch Processing System (.NET 3.5, WCF, Win Forms, WPF, Oracle 11g, ODP.NET, SOA, SQL Developer, Visio, Visual Studio Team Architect 2010).

4. On Going: Conducting .NET/Java resource interviews for BAE Systems

5. On Going: Successfully helping other .NET/Java developers in order to troubleshoot the issues they are facing.

6. On Going: Support for Fairfax Imaging Fax/Scanner Software solutions with Brooktrout Fax.

Comments and Discussions

QuestionORACLE UDT Pin
Murugan8119-Apr-16 20:09
Murugan8119-Apr-16 20:09 
GeneralSimple Oracle Commands without refcursor Pin
Member 22938742-Feb-11 23:05
Member 22938742-Feb-11 23:05 
Thanks a lot for example, is really very convenient. Thumbs Up | :thumbsup: By the way, the parameters can be passed directly to the request without procedures:

select * from sched_gen_report_detail where reporter_sid in (select * from table(:param1))
GeneralDefine type in package Pin
mon_compte_anti_spam17-Nov-09 6:58
mon_compte_anti_spam17-Nov-09 6:58 
GeneralRe: Define type in package Pin
Niklas Zacho26-Nov-09 14:31
Niklas Zacho26-Nov-09 14:31 
GeneralExcuse me for not providing enough explanation Pin
kamran anwar (Software Engineer)4-Mar-09 0:18
kamran anwar (Software Engineer)4-Mar-09 0:18 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.