Given a table like this one

       KEY C
---------- ----------
         1 v1
         2 v2
         3 v3
         4 v4
         5 v5
 

queries are sometimes required that allow users to enter a parameter like "1,3,5" in one run to select rows with certain KEY column values, like these

       KEY C
---------- ----------
         1 v1
         3 v3
         5 v5
 

or a parameter value like "2,4" in another run to select these rows.

       KEY C
---------- ----------
         2 v2
         4 v4
 

In pseudocode the query would look like this.

select key, c
from   t
where  key in ( ...user-enetered value list... ) ;
 

The ...user-enetered value list... part is often referred to as a "varying IN list" or a "variable IN list". In this tutorial we will see different approaches for implementing varying IN lists. The solutions will be presented as SQL commands, but the logic can be easily implemented in PL/SQL functions if required.

Gotchas

Programmers sometimes try to implement varying IN lists using a query like this.

variable v_list varchar2(10)

execute :v_list := '1,3,5'

select key, c
from   t
where  key in ( :v_list ) ;
where  key in ( :v_list )
                 *
ERROR at line 3:
ORA-01722: invalid number


 

This approach fails with an ORA-01722 because Oracle treats the WHERE clause as

where key in ( '1,3,5' )
 

not, as was hoped, this way.

where key in ( 1,3,5 )