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”.

  1. using System;  
  2. using Oracle.DataAccess.Client;  
  3. using Oracle.DataAccess.Types;  
  4. namespace MyDataAccessUtilities.OracleCustomTypes  
  5. {  
  6.     public class OdciNumberList: IOracleCustomType, INullable  
  7.     {  
  8.         [OracleArrayMapping] public Int32[] Array;  
  9.         public OracleUdtStatus[] StatusArray  
  10.         {  
  11.             get;  
  12.             set;  
  13.         }  
  14.         public void FromCustomObject(OracleConnection con, IntPtr pUdt)  
  15.         {  
  16.             OracleUdt.SetValue(con, pUdt, 0, Array, StatusArray);  
  17.         }  
  18.         public void ToCustomObject(OracleConnection con, IntPtr pUdt)  
  19.         {  
  20.             object objectStatusArray;  
  21.                 Array = (Int32[]) OracleUdt.GetValue(con, pUdt, 0, out objectStatusArray);  
  22.             StatusArray = (OracleUdtStatus[]) objectStatusArray;  
  23.         }  
  24.         public bool IsNull  
  25.         {  
  26.             get;  
  27.             private set;  
  28.         }  
  29.         public static OdciNumberList Null  
  30.         {  
  31.             get  
  32.             {  
  33.                 var odciNumberList = new OdciNumberList();  
  34.                 odciNumberList.IsNull = true;  
  35.                 return odciNumberList;  
  36.             }  
  37.         }  
  38.     }  
  39.     [OracleCustomTypeMapping("SYS.ODCINUMBERLIST")]  
  40.     public class OdciNumberListFactory: IOracleCustomTypeFactory, IOracleArrayTypeFactory  
  41.     {  
  42.         public IOracleCustomType CreateObject()  
  43.         {  
  44.             return new OdciNumberList();  
  45.         }  
  46.         public Array CreateArray(int numElems)  
  47.         {  
  48.             return new Int32[numElems];  
  49.         }  
  50.         public Array CreateStatusArray(int numElems)  
  51.         {  
  52.             return new OracleUdtStatus[numElems];  
  53.         }  
  54.     }  
  55. }  
Extension Method to Add this custom type as Parameter value:
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Data;  
  4. using Oracle.DataAccess.Client;  
  5. namespace MyDataAccessUtilities.OracleCustomTypes  
  6. {  
  7.     public static class OracleParameterExtension  
  8.     {  
  9.         public static OracleParameter GenerateOdciNumberListParameter(this OracleParameter oracleParameter, string parameterName, List < Int32 > numberList, ParameterDirection parameterDirection = ParameterDirection.Input)  
  10.         {  
  11.             oracleParameter.OracleDbType = OracleDbType.Array;  
  12.             oracleParameter.ParameterName = parameterName;  
  13.             oracleParameter.UdtTypeName = "SYS.ODCINUMBERLIST";  
  14.             oracleParameter.Direction = parameterDirection;  
  15.             if (parameterDirection == ParameterDirection.Input || parameterDirection == ParameterDirection.InputOutput)  
  16.             {  
  17.                 var numberArray = new OdciNumberList();  
  18.                 if (numberList != null)  
  19.                 {  
  20.                     numberArray.Array = numberList.ToArray();  
  21.                 }  
  22.                 oracleParameter.Value = numberArray;  
  23.             }  
  24.             return oracleParameter;  
  25.         }  
  26.         public static List < int > GetOdciNumberListParameterValue(this OracleParameter oracleParameter)  
  27.         {  
  28.             var result = new List < int > ();  
  29.             if (oracleParameter.Value is OdciNumberList)  
  30.             {  
  31.                 var parameter = oracleParameter.Value as OdciNumberList;  
  32.                 if (parameter.Array != null) result.AddRange(parameter.Array);  
  33.             }  
  34.             return result;  
  35.         }  
  36.     }  
  37. }  
That’s it.

Here is sample procedure which takes SYS.ODCINUMBERLIST as input parameter:
  1. CREATE OR REPLACE  
  2. PROCEDURE GETEMPLOYEES(PIN_EMPLOYEEIDS SYS.ODCINUMBERLIST, POUT_EMPLOYEES OUT SYS_REFCURSOR)  
  3. AS  
  4. BEGIN  
  5. SELECT EMP.*FROM EMPLOYEE EMP  
  6. JOIN TABLE(PIN_EMPLOYEEIDS) IDS  
  7. ON EMP.EMP_ID = IDS.COLUMN_VALUE;  
  8. END  
Here is the .Net code to call this procedure.
  1. var employeeIds = new List < int >  
  2. {  
  3.     100,  
  4.     110,  
  5.     115,  
  6.     116,  
  7.     117,  
  8.     118,  
  9.     119,  
  10.     120,  
  11.     124,  
  12.     125  
  13. };  
  14. using(var con = new OracleConnection("Connection sting here"))  
  15. {  
  16.     con.Open();  
  17.     using(var command = new OracleCommand("GetEmployees", con))  
  18.     {  
  19.         command.Parameters.Add(new OracleParameter().GenerateOdciNumberListParameter("PIN_EMPLOYEEIDS", employeeIds, ParameterDirection.Input));  
  20.         command.Parameters.Add(new OracleParameter("POUT_EMPLOYEES", OracleDbType.RefCursor, ParameterDirection.Output));  
  21.         using(var reader = command.ExecuteReader())  
  22.         {  
  23.             // Reader Code here.....  
  24.         }  
  25.     }  
  26. }