In Oracle7, this is rather difficult. You cannot bind in a SINGLE value and expect it to be treated as MANY values (think about how hard it would be to find something in an IN LIST that had a "comma" in it for example).

In Oracle8.0 and up, this is easy with object types and nested tables. Just to see what that will look like, I'll show that - then I'll show the v7 implementation.

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

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

Function created.


So, in Oracle8 and up we can "select * from PLSQL_FUNCTION" when the function returns a SQL Table type as follows:

ops$tkyte@8i>
ops$tkyte@8i> select *
2 from THE ( select cast( in_list('1,2,3,5,12') as
mytableType ) from dual ) a
3 /

COLUMN_VALUE
------------
1
2
3
5
12

Which means we can do that in a subquery:

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

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




In v7, we can do something very similar (works in 8.0 and up as well) with plsql tables. Its a little more work but not too much. It would look like this:


ops$tkyte@8i> create or replace package my_pkg
2 as
3 function in_list( p_string in varchar2, p_idx in number )
return varchar2;
4 pragma restrict_references( in_list, wnds, rnds,
wnps, rnps );
5
6 function get_list_count(p_string in varchar2)
return number;
7 pragma restrict_references( get_list_count, wnds,
rnds, wnps, rnps );
8
9 pragma restrict_references( my_pkg, wnds, rnds,
wnps, rnps );
10 end;
11 /

Package created.

ops$tkyte@8i>
ops$tkyte@8i> create or replace package body my_pkg
2 as
3 function in_list( p_string in varchar2, p_idx in number )
return varchar2
4 is
5 l_start number;
6 l_stop number;
7 begin
8 if ( p_idx = 1 ) then
9 l_start := 1;
10 else
11 l_start := instr( p_string, ',', 1, p_idx-1 )+1;
12 end if;
13
14 l_stop := instr( p_string ||',', ',', 1, p_idx );
15
16 return ltrim( rtrim( substr( p_string, l_start,
l_stop-l_start ) ) );
17 end;
18
19 function get_list_count( p_string in varchar2 )
return number
20 is
21 l_cnt number default 0;
22 begin
23 for i in 1 .. 1000 loop
24 exit when nvl( instr( p_string, ',', 1, i ), 0 ) = 0;
25 l_cnt := i+1;
26 end loop;
27
28 return l_cnt;
29 end;
30
31 end;
32 /

Package body created.



So, now I can code a query like:

ops$tkyte@8i> variable bindVar varchar2(255)
ops$tkyte@8i>
ops$tkyte@8i> exec :bindVar := '1, 3, 44, 1000'

PL/SQL procedure successfully completed.

ops$tkyte@8i>
ops$tkyte@8i> select my_pkg.in_list( :bindVar, rownum )
2 from all_objects
3 where rownum <= ( select my_pkg.get_list_count( :bindVar ) from dual )
4 /

MY_PKG.IN_LIST(:BINDVAR,ROWNUM)
----------------------------------------------------------------------------
------------------------
1
3
44
1000


All we need for that to work is a table with MORE rows then we have IN LIST
items -- all_objects is generally a good candidate. It works by using
rownum as an "index" into the plsql table.

So, now we can simply:


ops$tkyte@8i> select *
2 from all_users
3 where user_id in ( select my_pkg.in_list( :bindVar, rownum )
4 from all_objects
5 where rownum <= ( select
y_pkg.get_list_count( :bindVar ) from dual )
6 )
7 /

USERNAME USER_ID CREATED
------------------------------ ---------- ---------
WEB$AZEILMAN 1000 24-JUN-99