OracleParameter and IN Clause

Question

Is there a way to add a parameter to an IN clause using System.Data.OracleClient.

For example:

string query = "SELECT * FROM TableName WHERE UserName IN (:Pram)";
OracleCommand command = new OracleCommand(query, conn);
command.Parameters.Add(":Pram", OracleType.VarChar).Value = "'Ben', 'Sam'";

Answer 1

That way your query will be:

SELECT * FROM TableName WHERE UserName IN ('''Ben'', ''Sam''');

Those two names will be input as one single value.

Have a look at this thread from asktom.oracle.com to find out how to get a dynamic in list.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:210612357425

Answer 2

You can do it more easily with ODP.NET:

1. Create a TABLE type in your database:

   CREATE TYPE t_varchar2 AS TABLE OF VARCHAR2(4000);
   

2. Create a collection parameter:

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

3. Fill the parameter:

   param = new string[2] {"Ben", "Sam" };
   

4. Bind the parameter to the following query:

   SELECT * FROM TableName WHERE UserName IN (TABLE(CAST(:param AS t_varchar2)));
   

Answer 3

Actually, I would also try this code :

string query = "SELECT * FROM TableName WHERE UserName IN (:Pram)";
param = new string[2] {"Ben", "Sam" };
OracleCommand command = new OracleCommand(query, conn);
command.ArrayBindCount = param.Length;
command.Parameters.Add(":Pram", OracleType.VarChar).Value = param;

Answer 4

I know this was asked a while ago but not a brilliant answer.

I would do something like this - please excuse the crude psudo code

string args[] = {'Ben', 'Sam'};
string bindList = "";
for(int ii=0;ii<args.count;++ii)
{
  if(ii == 0)
  {
   bindList += ":" + ii;
  }
  else
  {
   bindList += ",:" + ii;
  }
  OracleParameter param = new OracleParameter();
  param.dbType = types.varchar;
  param.value = args[ii];
  command.Parameters.Add(param);
}

query = "select * from TableName where username in(" + bindList + ")";

So then query ends up having in(:1,:2) and each of these are bound separately.

There is also a similar question here: http://stackoverflow.com/questions/2915122/oracle-c-how-do-i-use-bind-variables-with-select-statements-to-return-multiple/3782679