Varying IN Lists

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 )
 



Linking to SQL Snippets ™

To link to this page in Oracle Technology Network Forums or OraFAQ Forums cut and paste this code.

  • [url=http://www.sqlsnippets.com/en/topic-12428.html]SQL Snippets: SQL Techniques Tutorials - Varying IN Lists[/url]

To link to this page in HTML documents or Blogger comments cut and paste this code.

  • <a href="http://www.sqlsnippets.com/en/topic-12428.html">SQL Snippets: SQL Techniques Tutorials - Varying IN Lists</a>

To link to this page in other web sites use the following values.

  • Link Text : SQL Snippets: SQL Techniques Tutorials - Varying IN Lists
  • URL (href): http://www.sqlsnippets.com/en/topic-12428.html

Revision Notes

Date Category Note
2007-10-13 Revision added pseudocode and Gotcha section