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 )