OracleParameter and IN Clause


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'";
2/12/2009 2:29:08 PM

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)));
2/12/2009 3:12:29 PM

Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow