sql - Oracle in C#, bind variables, and queries like ID IN (1, 2, 3) -

i'm looking c# odac adaptation of following java technique, code able bind array of numbers (the array size can vary) non-pl/sql select statement , use resulting array in where id in (...) style check.

http://rafudb.blogspot.com/2011/10/variable-inlist.html

here's example of technique used in oci-based c programs:

oracle oci, bind variables, , queries id in (1, 2, 3)

specifically, want execute query like

select * mytable id in (select * table(:1)) 

and pass in array of numbers :1.

code:

oraparam.udttypename = "sys.odcinumberlist"; varray newarray = new varray(); newarray.array = new int32[] {12,24,42}; oraparam.oracledbtype = oracledbtype.array; oraparam.value = newarray;  string query = @"select * table(:1) "; oraclecommand command = new oraclecommand(query, myconnection); command.parameters.add(oraparam); oracledatareader reader; var m_connection = new oracleconnection("the connection string"); m_connection.open(); var reader = command.executereader(); reader.close(); m_connection.close(); 

which followed following helper classes:

class varray : ioraclecustomtype, inullable {     [oraclearraymapping()]     public int32[] array;      private oracleudtstatus[] m_statusarray;     public oracleudtstatus[] statusarray     {                 {             return this.m_statusarray;         }         set         {             this.m_statusarray = value;         }     }      private bool m_bisnull;      public bool isnull     {                 {             return m_bisnull;         }     }      public static varray null     {                 {             varray obj = new varray();             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 "varray.null";         else         {             string rtnstr = string.empty;             if (m_statusarray[0] == oracleudtstatus.null)                 rtnstr = "null";             else                 rtnstr = array.getvalue(0).tostring();             (int = 1; < m_statusarray.length; i++)             {                 if (m_statusarray[i] == oracleudtstatus.null)                     rtnstr += "," + "null";                 else                     rtnstr += "," + array.getvalue(i).tostring();             }             return "varray(" + rtnstr + ")";         }     } }  [oraclecustomtypemapping("sys.odcinumberlist")] public class varrayfactory : ioraclecustomtypefactory, ioraclearraytypefactory {     // ioraclecustomtypefactory     public ioraclecustomtype createobject()     {         return new varray();     }      // ioraclearraytypefactory interface     public array createarray(int numelems)     {         return new int32[numelems];     }      public array createstatusarray(int numelems)     {         // createstatusarray may return null if null status information          // not required.         return new oracleudtstatus[numelems];     } } 

description:

the general idea similar oci example, have cast parameter sys.odcinumberlist (or other valid type). type not defined default in c# oracledbtype have use udttypename , custom factory/class bind successfully.

this inspired following post on defining custom types.

limits:

this exact solution work int/number values because piggybacking off of sys.odcinumberlist table type. if needed other types may need find/write additional custom table types.