"How can I set a variable number of values in a SQL IN clause without having to change the text of the SQL statement each time?"
Normally, SQL does not allow a bind variable in an IN clause. That is, the following SQL statement is not legal:
SELECT *
FROM EMP
WHERE ENAME IN (?)
It's more correct to say that the statement is legal, but it's not going to allow what you probably think. You can bind the ? to a single scalar value like "SMITH", but you cannot bind a value like "'SMITH','JONES'" and expect the database to return either SMITH or JONES or both. It's a bind variable, and not a lexical string substitution.
However, reading Tom Kyte's great book Expert One-on-One: Oracle I found a trick that I've since used several times successfully. It relies on setting a bind variable to an array-valued scalar, and then casting this array to be a table of records that the database can handle in an IN clause to do what you want.
You start by creating an Oracle8 object type like this:
CREATE TYPE TABLE_OF_VARCHAR AS TABLE OF VARCHAR2(2000);
then you use this type from Java to bind an array of strings as the value of the bind variable in a SQL statement like this:
SELECT *
FROM EMP
WHERE ENAME IN (
SELECT *
FROM TABLE(CAST( ? AS TABLE_OF_VARCHAR)))
So, you're binding a scalar value that happens to be an array of strings, and then casting it to a TABLE_OF_VARCHAR type which in Oracle8i 8.1.7 and greater you can select over using the TABLE() operator. By putting a sub-select like this inside the IN clause, you bind different arrays of strings (containing different numbers of strings, too, of course) and have the IN clause behave as you expect.
If you are using Oracle binding variable style (the default when Oracle is the typemap and SQL flavor for your project), then you would use the Oracle-style bind variable like this:
SELECT *
FROM EMP
WHERE ENAME IN (
SELECT *
FROM TABLE(CAST( :1 AS TABLE_OF_VARCHAR)))
I've created a simple project that illustrates how you would use this in BC4J and placed it here: ArrayOfStringDomain.zip
Just run the TestClient.java client class to call the custom method that accepts a String[] and sets up the view object's bind variable appropriately.
11:17:29 AM 
|
