OracleParameter and IN Clause

Solution 1:

You can wrap it in OracleCommandExtension method:

public static class OracleCommandExtension
{
    public static OracleCommand AddParameterCollection<TValue>(this OracleCommand command, string name, OracleType type, IEnumerable<TValue> collection)
    {
        var oraParams = new List<OracleParameter>();
        var counter = 0;
        var collectionParams = new StringBuilder(":");
        foreach (var obj in collection)
        {
            var param = name + counter;
            collectionParams.Append(param);
            collectionParams.Append(", :");
            oraParams.Add(new OracleParameter(param, type) { Value = obj });
            counter++;
        }
        collectionParams.Remove(collectionParams.Length - 3, 3);
        command.CommandText = command.CommandText.Replace(":" + name, collectionParams.ToString());
        command.Parameters.AddRange(oraParams.ToArray());
        return command;
    }
}

Solution 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)));
   

Solution 3:

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: Oracle/c#: How do i use bind variables with select statements to return multiple records?