ODAC 11g Released

ODAC 11g Version 11.1.0.6.20 (including the Oracle Developer Tools for Visual Studio .NET and ODP.NET) has now been released to production.

Get your download here and check out the new features list!

The Bulk Copy and User-Defined Types (UDT) features are likely to be well received along with the Instant Client support.

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.