SQL Snippets ™: Tutorials for Oracle Developers

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