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.
