Dynamic IN clauses and PLSQL

Question:
I am using an IN clause in my query to specify a subset of rows. The users just changed the requirements so that now I need to supply the values for the IN clause at runtime. There could be 1, 2 or 100 strings (or numbers or dates...) in the list. How can I implement a dynamic IN clause in PL/SQL?
Answer:
PL/SQL actually gives you several options for implementing a dynamic IN clause. I will first offer some background and then show the options available to you.
As you note in your question, the SQL language offers an IN operator for WHERE clauses, which allows us to write queries like this:
SELECT *
FROM employees
WHERE employee_id IN (137, 555, 603);
In other words, you pass in a comma-delimited list of values, and the query returns all rows containing an employee ID that matches a value in the list.
The maximum number of items you can have in an IN list is 1000. If you try to include more than that, Oracle raises the following exception:
ORA-01795: maximum number of expressions in a list is 1000
For most situations, that 1000 item list is not going to be much of a challenge. Still, it is certainly possible that an application will encounter this limit.
In addition, a common requirement among developers is to be able to use a dynamic set of values in the IN clause - as you write in your question. In this scenario, at the time I write my program, I don't know if I will have 2 or 22 values that will need to go inside the list for IN.
PL/SQL offers a number of ways to implement a dynamic IN clause, and at least in some cases also bypass the 1000 item limit. We will look at a few of the choicest among tehse implementations, namely:
• Use native dynamic SQL (NDS) to construct and execute a query with a list of values provided at run-time and concatenated into the query string. You avoid the hard-coding problem, but still have the 1000 item limit.
• Use a nested table containing the list of values, employing the TABLE operator to query from that collection inside my query. With this option, you avoid the hard-coding problem and also the 1000 item limit.
• Use the Oracle Database 10g MEMBER OF syntax in place of the IN operator. A very elegant and simple syntax, MEMBER OF will be the best way to implement dynamic IN clauses with any number of items (no maximum), but only when Oracle fixes a performance bug.
• Use DBMS_SQL to construct and execute a query with a list of values provided at run-time, relying in this case on bind variables. With this option, you avoid the hard-coding problem, but still have a maximum of 1000 items. A key benefit of this approach is to minimize the chance of code injection.
I will not only demonstrate the kind of code needed to implement this functionality, but also compare the performance of these various options. You can try out these same alternatives by running the script found after the heading "Script comparing dynamic IN clause implementations". These are the results I obtained from the test script:
Technique Time in Seconds
Use NDS with comma delimited string of items 0.29
Use DBMS_SQL with bind variables 0.25
Use a nested table and the TABLE operator 0.34
Use MEMBER OF 19.59
All of this code is written to work with a specific table and columns, but you should be able to easily transform the programs to work with your own structures. Please also note that these programs are prototypes and not intended to be used in a production context; you should, for example, add exceptions handlers that log any errors that occur, making sure to record the string that was being executed so you can scan it for syntax errors.