ExecuteReader with Oracle array binding

Question

I'm trying to improve performance of my Oracle SQL queries by using array binding to an OracleParameter.

This is basically what I'm trying to do:

                List<string> IDValList = new List<string>();
                IDValList.Add( "IDOne" );
                IDValList.Add( "IDTwo" );

                List<int> sizes = new List<int>();
                foreach( string id in IDValList )
                {
                    sizes.Add( id.Length );
                }

                using( OracleCommand cmd = new OracleCommand( "select col1, col2, col3 from table where col4 in ( :idArray )", _conn ) )
                {
                    cmd.CommandType = System.Data.CommandType.Text;

                    OracleParameter arrayParam = new OracleParameter( "idArray", OracleDbType.Varchar2 );
                    arrayParam.Direction = System.Data.ParameterDirection.Input;
                    arrayParam.Value = IDValList.ToArray();
                    arrayParam.ArrayBindSize = sizes.ToArray();

                    cmd.ArrayBindCount = IDValList.Count;
                    cmd.Parameters.Add( arrayParam );

                    using( OracleDataReader dr = cmd.ExecuteReader() )
                    {
                        while( dr.Read() )
                        {
                           // now read the row...

This compiles and runs, but I always only get back one row, for the first ID. Its like its ignoring the rest of the values in the array in the parameter.

Interestingly enough, the ArrayBindStatus of the parameter is successful for all the values.

What am I missing? Or will this not work with an OracleReader?

Thanks

Edit: Basically, I'm trying to follow this example, but I want to be able to read the resulting dataset from the query using a DataReader.

http://www.oracle.com/technology/oramag/oracle/09-sep/o59odpnet.html

Answer 1

There are several ways of doing the "variable in list" that you are searching for. To utilize a bound parameter.

This is based on this: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:210612357425

frankly I try to use bind variables as often as possible if not just for the performance increase, but for the additional level of security.

while there are many ways to skin a cat, this (while verbose) ought to do it

in Oracle --create your type create or replace type varcharTableType as table of varchar2 (255);

--create your function
function in_varchar( p_string in varchar2 ) return varcharTableType  
    as
        l_string        long default p_string || ',';
        l_data          varcharTableType := varcharTableType();
        n               number;
    begin
      loop
          exit when l_string is null;
          n := instr( l_string, ',' );
         l_data.extend;
         l_data(l_data.count) := 
                 ltrim( rtrim( substr( l_string, 1, n-1 ) ) );
         l_string := substr( l_string, n+1 );
    end loop;

    RETURN L_DATA;
  END in_varchar;

now ammend your query in .net

 col4 in ( select COLUMN_VALUE from table(in_varchar(:idArray )) )

(btw I copied most of this code from a previous posting I answered on oracle forums: http://forums.oracle.com/forums/thread.jspa?messageID=4299793&#4299793

this would actually make it so you wouldn't have to use the array binding, just make sure that is a comma delimited string: :idArray = "A,B,C"

Another option is to return the select statements into a ref cursor array:

        /* example table
         * 
Create  Table Zzztab(Deptno Number, Deptname Varchar2(50) , Loc Varchar2(50) , State Varchar2(2) , Idno Number(10)) ;
/
insert into Zzztab(Deptno , Deptname  , Loc  , State , Idno)
values (0,'Zero','US','NY',0);
insert into Zzztab(Deptno , Deptname  , Loc  , State , Idno)
values (1,'One','CA','ON',1);
insert into Zzztab(Deptno , Deptname  , Loc  , State , Idno)
values (2,'Three','IS',null,2);
insert into Zzztab(Deptno , Deptname  , Loc  , State , Idno)
values (3,'Four','BD',null,3);
         */
    string connectStr = GetConnectionString();

    // Initialize array of data
    String[] myArrayDeptName = { "Zero", "Three", "Four" };

    OracleConnection connection = new OracleConnection(connectStr);
    OracleCommand command = new OracleCommand();
    command.Connection = connection;
    command.CommandType = CommandType.Text ;
    command.CommandText = "begin open :cur for SELECT DEPTNO, DEPTNAME FROM ZZZTAB WHERE DEPTNAME = :DEPT; end;";

    command.ArrayBindCount = myArrayDeptName.Length ;
    command.BindByName = true;

    OracleParameter cur = new OracleParameter("cur", OracleDbType.RefCursor );
    cur.Direction = ParameterDirection.Output;
    cur.Value = myArrayDeptName;
    command.Parameters.Add(cur);

    // deptname parameter
    OracleParameter deptNameParam = new OracleParameter("DEPT", OracleDbType.Varchar2);
    deptNameParam.Direction = ParameterDirection.Input;
    deptNameParam.Value = myArrayDeptName;
    command.Parameters.Add(deptNameParam);

     try
    {
        connection.Open();
        command.ExecuteNonQuery();

        foreach (Oracle.DataAccess.Types.OracleRefCursor  rc in (Oracle.DataAccess.Types.OracleRefCursor[])cur.Value)
        { ...  fill in an join the datatables

you can use the exact same logic and just have the columns return into each their own array.