Dynamically Creating a Variable In-List

Frequently SQL statements in code use an "in-list". That is, SQL that resembles the following:

select   employee_id,
         first_name,
         last_name
from     employees
where    employee_id in (102,127,206)

Here I am providing an in-list for the employee_id column. Using the HR sample that ships with Oracle Database, the above query returns the following results:

EMPLOYEE_ID FIRST_NAME           LAST_NAME
----------- -------------------- -------------------------
        102 Lex                  De Haan
        206 William              Gietz
        127 James                Landry

Of course you may wish to vary the number of items in the in-list as well as the actual values. When the value of an item in the list changes, you may wish to use a bind variable for that item. See my Oracle Magazine column here for more details on bind variables and why you might want to use them.

The above SQL statement re-factored to use bind variables would look like:

select   employee_id,
         first_name,
         last_name
from     employees
where    employee_id in (:0,:1,:2)

In this re-factored statement I am still using 3 values in the in-list. This is definitely not a requirement: the in-list can contain anywhere from 1 to 1000 items. See the official Oracle documentation here for more details.

When attempting to use an in-list with bind variables you may be tempted to supply the comma-delimited values as a single string argument to the in-list. This may resemble:

select   employee_id,
         first_name,
         last_name
from     employees
where    employee_id in (:0)

In this case you may try to pass the string "102,127,206" as the value for the bind variable. However, this will not work as you might expect. You will receive "ORA-1722: invalid number" if you try this because the string "102,127,206" can not be converted to a numeric value. Instead you must create the statement with a separate bind variable for each item you wish to pass in the in-list.

One technique to do this uses a StringBuilder object to create the SQL statement and dynamically build the in-list items. Here is some sample code that illustrates this technique:

using System;
using System.Data;
using System.Text;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;

namespace VariableInListDemo
{
  class Program
  {
    static void Main(string[] args)
    {
      // change connection string for your environment
      string constr = "User Id=hr;" +
                      "Password=hr;" +
                      "Data Source=orademo;" +
                      "Pooling=false;" +
                      "Enlist=false";

      // the "base" sql statement to which the in-list will be appended
      StringBuilder sbSQL = new StringBuilder("select employee_id, " +
                                              "first_name, " +
                                              "last_name " +
                                              "from employees " +
                                              "where employee_id in (");

      // if no command-line arguments specified, simply return
      if (args.Length == 0)
      {
        Console.WriteLine("No employee_id values specified...");
        Console.WriteLine("Please specify values such as: 102,127,206");
        return;
      }
      // create and open connection
      OracleConnection con = new OracleConnection(constr);
      con.Open();

      // split the command-line argument into separate tokens
      // the command-line argument should be similar to:
      // 102,127,206
      string[] EmpNos = args[0].Split(',');

      // build the in-list part of the sql
      for (int i = 0; i < EmpNos.Length; i++)
      {
        // append the bind variables into the sql
        sbSQL.Append(":" + i.ToString());

        // add a "," after all but the last bind variable
        if (i < EmpNos.Length - 1)
        {
          sbSQL.Append(",");
        }
      }

      // close the in-list with the trailing ")"
      sbSQL.Append(")");

      // create the command object
      OracleCommand cmd = con.CreateCommand();
      cmd.CommandText = sbSQL.ToString();

      // create an array of OracleParameter objects for each
      // empno specified on the command-line
      OracleParameter[] EmpNoParams = new OracleParameter[EmpNos.Length];

      // set the properties for each parameter
      // and add to parameters collection
      for (int i = 0; i < EmpNos.Length; i++)
      {
        EmpNoParams[i] = new OracleParameter();
        EmpNoParams[i].OracleDbType = OracleDbType.Decimal;
        EmpNoParams[i].Value = Convert.ToDecimal(EmpNos[i]);

        cmd.Parameters.Add(EmpNoParams[i]);
      }

      // create a data reader
      OracleDataReader dr = cmd.ExecuteReader();

      // iterate over the reader and display to console
      while (dr.Read())
      {
        Console.WriteLine(dr[0].ToString() + ", " +
                          dr[1].ToString() + ", " +
                          dr[2].ToString());
      }

      // add clean up code as necessary for your environment

      // prevent console from closing when run from VS environment
      Console.WriteLine();
      Console.WriteLine("ENTER to continue...");
      Console.ReadLine();
    }
  }
}

Running the above sample from a console window using the values "102,127,206" produces the following results:

C:\>VariableInListDemo.exe 102,127,206
102, Lex, De Haan
206, William, Gietz
127, James, Landry

ENTER to continue...

If you need to use a variable in-list in your application, I encourage you to experiment with this technique using different values and numbers of items as a possible solution.