Pass Collection to Oracle Stored Procedure from .NET Layer

Oracle 9 provides real nice options to keep collection of values.

SYS.ODCINUMBERLIST – VARRAY(32767) of NUMBER
SYS.ODCIVARCHAR2LIST – VARRAY(32767) OF VARCHAR2(4000)

Using these we can pass collection of Numbers / Strings to Stored Procedure.

Now, how could we use this at .Net Layer?

Here are the steps,

Create an Oracle Custom Type for replicate ODCINUMBERLIST / ODCIVARCHAR2LIST.

Map it to appropriate Oracle Types.

Use this Custom Type to pass parameter.

Create Oracle Custom Type.

Make sure a reference “Oracle.DataAccess”.

using System;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
namespace MyDataAccessUtilities.OracleCustomTypes
{
    public class OdciNumberList: IOracleCustomType, INullable
    {
        [OracleArrayMapping] public Int32[] Array;
        public OracleUdtStatus[] StatusArray
        {
            get;
            set;
        }
        public void FromCustomObject(OracleConnection con, IntPtr pUdt)
        {
            OracleUdt.SetValue(con, pUdt, 0, Array, StatusArray);
        }
        public void ToCustomObject(OracleConnection con, IntPtr pUdt)
        {
            object objectStatusArray;
                Array = (Int32[]) OracleUdt.GetValue(con, pUdt, 0, out objectStatusArray);
            StatusArray = (OracleUdtStatus[]) objectStatusArray;
        }
        public bool IsNull
        {
            get;
            private set;
        }
        public static OdciNumberList Null
        {
            get
            {
                var odciNumberList = new OdciNumberList();
                odciNumberList.IsNull = true;
                return odciNumberList;
            }
        }
    }
    [OracleCustomTypeMapping("SYS.ODCINUMBERLIST")]
    public class OdciNumberListFactory: IOracleCustomTypeFactory, IOracleArrayTypeFactory
    {
        public IOracleCustomType CreateObject()
        {
            return new OdciNumberList();
        }
        public Array CreateArray(int numElems)
        {
            return new Int32[numElems];
        }
        public Array CreateStatusArray(int numElems)
        {
            return new OracleUdtStatus[numElems];
        }
    }
}

Extension Method to Add this custom type as Parameter value:

using System;
using System.Collections.Generic;
using System.Data;
using Oracle.DataAccess.Client;
namespace MyDataAccessUtilities.OracleCustomTypes
{
    public static class OracleParameterExtension
    {
        public static OracleParameter GenerateOdciNumberListParameter(this OracleParameter oracleParameter, string parameterName, List < Int32 > numberList, ParameterDirection parameterDirection = ParameterDirection.Input)
        {
            oracleParameter.OracleDbType = OracleDbType.Array;
            oracleParameter.ParameterName = parameterName;
            oracleParameter.UdtTypeName = "SYS.ODCINUMBERLIST";
            oracleParameter.Direction = parameterDirection;
            if (parameterDirection == ParameterDirection.Input || parameterDirection == ParameterDirection.InputOutput)
            {
                var numberArray = new OdciNumberList();
                if (numberList != null)
                {
                    numberArray.Array = numberList.ToArray();
                }
                oracleParameter.Value = numberArray;
            }
            return oracleParameter;
        }
        public static List < int > GetOdciNumberListParameterValue(this OracleParameter oracleParameter)
        {
            var result = new List < int > ();
            if (oracleParameter.Value is OdciNumberList)
            {
                var parameter = oracleParameter.Value as OdciNumberList;
                if (parameter.Array != null) result.AddRange(parameter.Array);
            }
            return result;
        }
    }
}

That’s it.

Here is sample procedure which takes SYS.ODCINUMBERLIST as input parameter:

CREATE OR REPLACE
PROCEDURE GETEMPLOYEES(PIN_EMPLOYEEIDS SYS.ODCINUMBERLIST, POUT_EMPLOYEES OUT SYS_REFCURSOR)
AS
BEGIN
SELECT EMP.*FROM EMPLOYEE EMP
JOIN TABLE(PIN_EMPLOYEEIDS) IDS
ON EMP.EMP_ID = IDS.COLUMN_VALUE;
END

Here is the .Net code to call this procedure.

var employeeIds = new List < int >
{
    100,
    110,
    115,
    116,
    117,
    118,
    119,
    120,
    124,
    125
};
using(var con = new OracleConnection("Connection sting here"))
{
    con.Open();
    using(var command = new OracleCommand("GetEmployees", con))
    {
        command.Parameters.Add(new OracleParameter().GenerateOdciNumberListParameter("PIN_EMPLOYEEIDS", employeeIds, ParameterDirection.Input));
        command.Parameters.Add(new OracleParameter("POUT_EMPLOYEES", OracleDbType.RefCursor, ParameterDirection.Output));
        using(var reader = command.ExecuteReader())
        {
            // Reader Code here.....
        }
    }
}