How to use parameters in a ‘where value in…’ clause?

Using dynamic SQL is the simplest approach from a coding standpoint. The problem with dynamic SQL, though, is that you have to hard parse every distinct version of the query which not only has the potential of taxing your CPU but has the potential to flood your shared pool with lots of non-sharable SQL statements, pushing out statements you’d like to cache, causing more hard parses and shared pool fragmentation errors. If you’re running this once a day, that’s probably not a major concern. If hundreds of people are executing it thousands of times a day, that is likely a major concern.

An example of the dynamic SQL approach

SQL> ed
Wrote file afiedt.buf

  1  declare
  2    l_deptnos  varchar2(100) := '10,20';
  3    l_rc       sys_refcursor;
  4    l_dept_rec dept%rowtype;
  5  begin
  6    open l_rc for 'select * from dept where deptno in (' || l_deptnos || ')';
  7    loop
  8      fetch l_rc into l_dept_rec;
  9      exit when l_rc%notfound;
 10      dbms_output.put_line( l_dept_rec.dname );
 11    end loop;
 12    close l_rc;
 13* end;
SQL> /
ACCOUNTING
RESEARCH

PL/SQL procedure successfully completed.

Alternately, you can use a collection. This has the advantage of generating a single, sharable cursor so you don’t have to worry about hard parsing or flooding the shared pool. But it probably requires a bit more code. The simplest way to deal with collections

SQL> ed
Wrote file afiedt.buf

  1  declare
  2    l_deptnos  tbl_deptnos := tbl_deptnos(10,20);
  3  begin
  4    for i in (select *
  5                from dept
  6               where deptno in (select column_value
  7                                  from table(l_deptnos)))
  8    loop
  9      dbms_output.put_line( i.dname );
 10    end loop;
 11* end;
SQL> /
ACCOUNTING
RESEARCH

PL/SQL procedure successfully completed.

If, on the other hand, you really have to start with a comma-separated list of values, then you will have to parse that string into a collection before you can use it. There are various ways to parse a delimited string– my personal favorite is to use regular expressions in a hierarchical query but you could certainly write a procedural approach as well

SQL> ed
Wrote file afiedt.buf

  1  declare
  2    l_deptnos     tbl_deptnos;
  3    l_deptno_str  varchar2(100) := '10,20';
  4  begin
  5    select regexp_substr(l_deptno_str, '[^,]+', 1, LEVEL)
  6      bulk collect into l_deptnos
  7      from dual
  8   connect by level <= length(replace (l_deptno_str, ',', NULL));
  9    for i in (select *
 10                from dept
 11               where deptno in (select column_value
 12                                  from table(l_deptnos)))
 13    loop
 14      dbms_output.put_line( i.dname );
 15    end loop;
 16* end;
 17  /
ACCOUNTING
RESEARCH

PL/SQL procedure successfully completed.