Varying in lists...

Varying in lists. This is such a frequently asked question on asktom that I’ve decided just to write up the various ways you can do this.

First the problem statement: You have a string, it looks like

• 1, 2, 3, 4
• ‘A’, ‘B’, ‘C’

Or something similar. You would like to retrieve all rows from some table/query such that some column is in that string. That is, you would like to execute:

• select * from t where x in (1,2,3,4)
• select * from t where y in (‘A’,’B’,’C’)

You would like to use bind variables (because you’ve heard through the grapevine that bind variables are “good”). However, when you try:

SQL> variable txt varchar2(25)
SQL> exec :txt := ' ''SYS'', ''SYSTEM'' '
PL/SQL procedure successfully completed.

SQL> print txt

TXT
--------------------------------
'SYS', 'SYSTEM'

SQL> select *
  2    from all_users
  3   where username in (:txt);
no rows selected

SQL> select *
  2    from all_users
  3   where username in ('SYS','SYSTEM');

USERNAME      USER_ID CREATED
---------- ---------- ---------
SYS                 0 30-JUN-05
SYSTEM              5 30-JUN-05

It does not seem to work at all. When you “bind” the inlist – no data, when you hard code it – data is found. The reason – well, that should be clear, the example above that used the bind variable in this case is equivalent to this query with literals:

SQL> select *
 2    from all_users
 3   where username in ( ' ''SYS'', ''SYSTEM'' ' );
no rows selected

There is a single string, a single value in that in list. What we need to do is turn that into a “set”. Here are some approaches you can take.

If you have a finite number of items in the in-list
By all means just bind the individual elements. That is, in the above example, suppose we let the user pick up to 10 items in a pick list. I would strongly encourage the query you use to be:

Select * from all_users where username in ( :bv1, :bv2, :bv3, … :bv10 );

And you would NOT use a single string, you would bind 10 inputs to this query (binding NULLs for any bind variable they did not set a value for). This works well for small lists – it would be quite tedious obviously for dozens or hundreds of items.

If you are in Oracle 8i
We can use a function that returns a collection. We’ll make it so that we can “query a string” – use the string as if it were a table itself. The concept goes like this. We’ll need a collection type (I’ll just use a table of varchar2(4000)), and a PLSQL function that will parse a delimited string and return it as a collection:

SQL> create or replace type str2tblType as table of varchar2(4000)
2  /
Type created.

SQL> create or replace
2  function str2tbl
3  ( p_str in varchar2,
4    p_delim in varchar2 default ',' )
5  return str2tblType
6  as
7     l_str long default p_str || p_delim;
8     l_n   number;
9     l_data str2tblType := str2tblType();
10  begin
11      loop
12          l_n := instr( l_str, p_delim );
13          exit when (nvl(l_n,0) = 0);
14          l_data.extend;
15          l_data(l_data.count) := ltrim(rtrim(substr(l_str,1,l_n-1)));
16          l_str := substr( l_str, l_n+1 );
17      end loop;
18      return l_data;
19  end;
20  /
Function created.

SQL> column column_value format a10
SQL> exec :txt := 'SYS, SYSTEM'
PL/SQL procedure successfully completed.

SQL> select *
2    from TABLE( cast( str2tbl(:txt) as str2TblType ) )
3  /

COLUMN_VAL
----------
SYS
SYSTEM

SQL> select *
2    from all_users
3   where username in
4  (select *
5     from TABLE( cast( str2tbl(:txt) as str2TblType ) )
6  )
7  /

USERNAME      USER_ID CREATED
---------- ---------- ---------
SYS                 0 30-JUN-05
SYSTEM              5 30-JUN-05

If you are in 9iR2 and above
Then we can skip the function all together and just use DUAL to generate rows and parse the string. Consider:

SQL> select level l
2    from dual
3  connect by level <= 5;

    L
----------
    1
    2
    3
    4
    5

So, we can use DUAL to generate rows and then using substr/instr – effectively parse the bind variable and return the i'th element from it. For example:

SQL> exec :txt := 'SYS, SYSTEM'
PL/SQL procedure successfully completed.

SQL> with data
2  as
3  (
4  select
5    trim( substr (txt,
6          instr (txt, ',', 1, level  ) + 1,
7          instr (txt, ',', 1, level+1)
8             - instr (txt, ',', 1, level) -1 ) )
9      as token
10    from (select ','||:txt||',' txt
11            from dual)
12  connect by level <=
13     length(:txt)-length(replace(:txt,',',''))+1
14  )
15  select * from data;

TOKEN
----------------------------------
SYS
SYSTEM

Once we have that accomplished – the rest is easy:

SQL> with data
2  as
3  (
4  select
5    trim( substr (txt,
6          instr (txt, ',', 1, level  ) + 1,
7          instr (txt, ',', 1, level+1)
8             - instr (txt, ',', 1, level) -1 ) )
9      as token
10    from (select ','||:txt||',' txt
11            from dual)
12  connect by level <=
13     length(:txt)-length(replace(:txt,',',''))+1
14  )
15  select *
16    from all_users
17   where username in (select * from data);

USERNAME      USER_ID CREATED
---------- ---------- ---------
SYSTEM              5 30-JUN-05
SYS                 0 30-JUN-05

Now, some people look at that “with data” bit and say “that is too much, too hard to code that every time”. We can use a VIEW to hide the complexity here – and use a stored procedure as our way to “bind to the view” (this is sort of a parameterized view in effect). It would look like this:

SQL> create or replace context my_ctx using my_ctx_procedure
2  /
Context created.

SQL> create or replace
2  procedure my_ctx_procedure
3  ( p_str in varchar2 )
4  as
5  begin
6          dbms_session.set_context
7          ( 'my_ctx', 'txt', p_str );
8  end;
9  /
Procedure created.

SQL> create or replace view IN_LIST
2  as
3  select
4    trim( substr (txt,
5          instr (txt, ',', 1, level  ) + 1,
6          instr (txt, ',', 1, level+1)
7             - instr (txt, ',', 1, level) -1 ) )
8      as token
9    from (select ','||sys_context('my_ctx','txt')||',' txt
10            from dual)
11  connect by level <=
12     length(sys_context('my_ctx','txt'))
13       -length(replace(sys_context('my_ctx','txt'),',',''))+1
14  /
View created.

SQL> exec my_ctx_procedure( :txt )
PL/SQL procedure successfully completed.

SQL> select *
2    from all_users
3   where username in
4   (select * from IN_LIST);

USERNAME      USER_ID CREATED
---------- ---------- ---------
SYSTEM              5 30-JUN-05
SYS                 0 30-JUN-05

Now, you “bind” your queries in-list by calling MY_CTX_PROCEDURE and the view does the rest.