Oracle: varying elements in IN list(comma separated string)

Ref:  http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:110612348061

You can use a bind variable in an IN but I don't think it'll do what you want.  I believe 
you want a string that has the value:

    1, 2, 3

for example (a single variable) to be used in a query like:
  
   select * from t where x in ( :that_variable );

and have all rows returned such that x is in the set of 1, 2, 3.  What would happen 
though is that every row in T such that x = "1, 2, 3" would be returned (if it worked the 
other way -- you would never be able to IN on something with commas in it). 

One 'trick' that works very well on Oracle8.0 and up is to use an object type that is a 
nested table type and a simple parse routine that returns this nested table type given a 
string input.  What I mean is best explained via an example:

ops$tkyte@8i> create or replace type myTableType as table of number;
  2  /
Type created.

ops$tkyte@8i> create or replace function str2tbl( p_str in varchar2 ) return myTableType
  2  as
  3      l_str   long default p_str || ',';
  4      l_n        number;
  5      l_data    myTableType := myTabletype();
  6  begin
  7      loop
  8          l_n := instr( l_str, ',' );
  9          exit when (nvl(l_n,0) = 0);
 10          l_data.extend;
 11          l_data( l_data.count ) := ltrim(rtrim(substr(l_str,1,l_n-1)));
 12          l_str := substr( l_str, l_n+1 );
 13      end loop;
 14      return l_data;
 15  end;
 16  /

Function created.

ops$tkyte@8i> 
ops$tkyte@8i> select * from all_users
  2  where user_id in ( select *
  3    from THE ( select cast( str2tbl( '1, 3, 5, 7, 99' ) as mytableType ) from dual ) )
  4  /

USERNAME                          USER_ID CREATED
------------------------------ ---------- ---------
SYSTEM                                  5 20-APR-99