If separate variables are not an option then programmers sometimes turn to a dynamic SQL approach such as this one (presented here only as an example of what NOT to do).
create procedure dynamic_query( p_list in varchar2 ) as
v_sql_stmt varchar2(32767);
v_ref_cursor sys_refcursor;
v_row t%rowtype ;
begin
v_sql_stmt := 'select * from t where key in (' || p_list || ')' ;
open v_ref_cursor for v_sql_stmt ;
loop
fetch v_ref_cursor into v_row;
exit when v_ref_cursor%notfound;
dbms_output.put_line( v_row.c ) ;
end loop;
close v_ref_cursor;
end;
/
execute dynamic_query( '1,3,5' )
v1
v3
v5
execute dynamic_query( '2,4' )
v2
v4
drop procedure dynamic_query ;
This is a BAD, BAD, BAD thing to do. Why? Well, the main reason is that queries like the one assembled in v_sql_stmt, which can look different each time they are executed, will flood the database's shared pool. This in turn limits the application's scalability, reduces response time, and has a negative impact on other queries that need space in the shared pool too.
Another reason is that dynamic SQL can make your application vulnerable to SQL injection attacks.
Beyond that, static SQL solutions to the varying IN list problem exist so why futz with dynamic SQL when you don't have to? Let's look at some better options next.
