How to use parameters in a 'where value in...' clause?

This works when I have only one state code as a parameter.

How can I get code to work when I have more than one state_code in parm_list?

Requirements:

(1)I don't want to hard code the state codes in my cursor definition

(2) I do want to allow for more than one state code in my where clause

For example: I want to run this code for parm_list = ('NY','NJ','NC'). I'm encountering difficulties in reconciling single quotes in parm_list with the single quotes in the 'where state_code in ' query.

set serveroutput on;

DECLARE
parm_list varchar2(40);

cursor get_state_codes(in_state_codes varchar2)
is
select state_name, state_code from states
where state_code in (in_state_codes);

BEGIN
 parm_list := 'NY';
 for get_record in get_state_codes(parm_list) loop
  dbms_output.put_line(get_record.state_name || get_record.state_code);
 end loop;
END;

Solution 1:

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.

Solution 2:

One option is to use INSTR instead of IN:

SELECT uo.object_name
      ,uo.object_type
FROM   user_objects uo
WHERE  instr(',TABLE,VIEW,', ',' || uo.object_type || ',') > 0;

Although this looks ugly, it works well and as long as no index on the column being tested was going to be used (because this prevents the use of any index) the performance won't suffer much. If the column being tested is a primary key for instance, definitely this should not be used.

Another option is:

SELECT uo.object_name
      ,uo.object_type
FROM   user_objects uo
WHERE  uo.object_type IN
       (SELECT regexp_substr('TABLE,VIEW', '[^,]+', 1, LEVEL)
        FROM   dual
        CONNECT BY regexp_substr('TABLE,VIEW', '[^,]+', 1, LEVEL) IS NOT NULL);

In this case, the list of values should be concatenated into a single varchar variable, delimited by commas (or anything you like.)