problem using Oracle parameters in SELECT IN

ghz 1years ago ⋅ 6840 views

Question

I have a problem when inserting a string of numbers into sql query

  SELECT * 
    FROM tablename a 
   WHERE a.flokkurid IN (3857,3858,3863,3285) 
ORDER BY sjodategund, rodun

...or:

  SELECT * 
    FROM tablename a 
   WHERE a.flokkurid IN (:strManyNumbers) 
ORDER BY sjodategund, rodun

...with this code:

using (OracleCommand sel = new OracleCommand(SQL, connectionstring)) {
  sel.Parameters.Add(":strManyNumbers", 
                      OracleDbType.Varchar2, 
                      "Client",
                      ParameterDirection.Input);
}

So if i run this query i get:

ORA-01722: invalid number

but if i insert just one number, i.e. "3857" it will return query OK with data.

Answer

To pass a set of values, you need to use Oracle's table or array types.

At first, you create a table type (e.g. for NUMBER):

CREATE TYPE number_table AS TABLE OF NUMBER;

When you create the parameter for the query, declare it as an associative PL/SQL array:

OracleParameter param1 = new OracleParameter(); 
param1.OracleDbType = OracleDbType.Int32; 
param1.CollectionType = OracleCollectionType.PLSQLAssociativeArray;

Then assign some values:

param1 = new int[] { 3857, 3858, 3863, 3285 };

And your query needs a cast:

SELECT * FROM tablename a 
where a.flokkurid in (TABLE(CAST(:manyNumbers AS number_table)))
order by sjodategund, rodun
#oracle