problem using Oracle parameters in SELECT IN

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.

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 

That's not how parameters work. You cannot specify a "set" as a parameter, you have to assemble the SQL query in the string. And watch out for SQL Injection.

In addition, you might want to take a look at these:

• Is it possible to use query parameters to fill the IN keyword
• Parameterize an SQL IN clause

Update

Codo's answer has a very interesting approach for Oracle. I cannot test it right now, but it sure looks promising.

There's a very similar question here: OracleParameter and IN Clause , as pointed out by @DCookie. It's not an exact duplicate because when the type of the item in array changes, the SQL cast also changes.