Read Using SQL Snippets before using any of this site's code or techniques on your own systems.

Varying IN Lists

Separate Variables Method

Ideally, if the user will only be entering a small number of values it is better not to use a varying IN list at all. Separate variables used in a fixed IN list are easier to code and can perform well.

variable p1 number
variable p2 number
variable p3 number
variable p4 number
variable p5 number

-- this next part simulates a user entering "1" in one form field, "3" in another,
-- and "5" in a third; p4 and p5 remain null

execute :p1 := 1 ; :p2 := 3 ; :p3 := 5 ;

-- this is the main query

select *
from   t
where  key in ( :p1, :p2, :p3, :p4, :p5 ) ;
 
       KEY C
---------- ----------
         1 v1
         3 v3
         5 v5