Click here to Skip to main content
11,480,082 members (69,097 online)
Click here to Skip to main content

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

, 3 Mar 2009 CPOL 42K 18
Rate this:
Please Sign up or sign in to vote.
Passing a VARRAY custom type from .NET and using it in a Where clause in an Oracle Stored Procedure.

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

License

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

Share

About the Author

kamran anwar (Software Engineer)
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

 
GeneralSimple Oracle Commands without refcursor Pin
Member 22938743-Feb-11 0:05
memberMember 22938743-Feb-11 0:05 
GeneralDefine type in package Pin
mon_compte_anti_spam17-Nov-09 7:58
membermon_compte_anti_spam17-Nov-09 7:58 
GeneralRe: Define type in package Pin
Niklas Zacho26-Nov-09 15:31
memberNiklas Zacho26-Nov-09 15:31 
GeneralExcuse me for not providing enough explanation Pin
kamran anwar (Software Engineer)4-Mar-09 1:18
memberkamran anwar (Software Engineer)4-Mar-09 1:18 

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

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

| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.150520.1 | Last Updated 3 Mar 2009
Article Copyright 2009 by kamran anwar (Software Engineer)
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid