With a little more code than the previous solution we can write a pure SQL query that implements a varying IN list without supressing indexes. The key is to use logic that converts a string to a set of rows. Fortunately we have a number of these techniques in our arsenal at SQL Techniques Tutorials: String to Rows. In the next example we employ a method described at String to Rows: Integer Series Generator Method.
variable d varchar2
execute :d := ','
variable p varchar2(100)
execute :p := '1,3,5'
select *
from t
where key in
( select
to_number
( substr
( p,
instr( p, :d, 1, i.position ) + 1,
instr( p, :d, 1, i.position + 1 )
- ( instr( p, :d, 1, i.position ) + 1 )
)
) as val
from
( select
column_value as position ,
:d || :p || :d as p
from
table( integer_varray_type( 1,2,3,4,5,6,7,8,9,10 ) )
where
column_value <= length( :p || :d ) - nvl( length( replace( :p, :d, null ) ), 0 )
) i
)
order by 1 ;
KEY C
---------- ----------
1 v1
3 v3
5 v5
Duplicates in the IN list produce the same result as if there were no duplicates.
execute :p := '1,3,3,3,3,5' /
KEY C
---------- ----------
1 v1
3 v3
5 v5
The query accepts IN lists with spaces.
execute :p := '1, 2, 3, 4, 5' /
KEY C
---------- ----------
1 v1
2 v2
3 v3
4 v4
5 v5
If the IN list is null no rows are returned.
execute :p := null PL/SQL procedure successfully completed. / no rows selected
For quoted string lists like 'v1', 'v3', 'v5' we need to tweak the logic slightly.
-- the delimiter is a single quote this time
execute :d := ''''
execute :p := q'['v1', 'v3', 'v5']'
select *
from t
WHERE C IN
( select
substr
( p,
instr( p, :d, 1, i.position ) + 1,
instr( p, :d, 1, i.position + 1 )
- ( instr( p, :d, 1, i.position ) + 1 )
)
as val
from
( select
column_value as position ,
:P AS P
from
table( INTEGER_VARRAY_TYPE( 1,3,5,7,9,11,13,15,17,19 ) )
where
column_value < NVL(LENGTH(:P),1) - NVL( LENGTH( REPLACE( :P, :D, NULL ) ), 0 )
) i
)
order by 1 ;
KEY C
---------- ----------
1 v1
3 v3
5 v5