Binding Variable For IN Queries

Question

While Using Binding Variable its not getting the Values

eg,, SELECT * FROM EMP WHERE USER IN (:VALUES)

IF i use this above query its not executed

passing the values directly

SELECT * FROM EMP WHERE USER IN (+ arraylist +) its working fine

========================================

here is the sample caode

string[] Myarray = ListVal.split(','); Query = "SELECT * FROM EMP WHERE USER IN (:VALUES)"

OracleParameter Param = { new OracleParameter (":VALUES",OracleDBType.Array)

};

Param[0].value = Myarray;

using(dr = OracleFactory.ExecuteReader(CommandType.Text,Query,true,Param)) {

}

while Executing this I get an Exception Like

Invalid Parameter Binding ParameterName VALUES

Answer 1

You need to pass the values as an array and to slightly modify the query. The following code is for ODP.NET. I don't know whether it can also be done with Microsoft's deprecated Oracle drivers.

At first, you need to define a table type (e.g. for VARCHAR):

CREATE TYPE varchar_table AS TABLE OF VARCHAR2(2000); 

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

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

Then assign some values:

param1 = new string[2] { "johnp", "billt" }; 

And your query needs a cast:

SELECT * FROM EMP WHERE USER IN (TABLE(CAST(:values AS varchar_table)))

Answer 2

Unfortunately you cannot pass arrays to bind variables.

Your only options are directly inserting a comma separated list in the query or writing your own helper/wrapper that automatically rewrites your statement to

IN (:value1, :value2, :value3...)

and fills those values automatically. This could at least in some cases (if you have lots of repeat queries with the same amount of parameters) ease up the work for the Oracle parser.