How do you specify IN clause in a dynamic query using a variable?

Question

In PL/SQL, you can specify the values for the IN operator using concatenation:

v_sql := 'select field1
from table1
where field2 in (' || v_list || ')';

Is it possible to do the same using a variable?

v_sql := 'select field1
from table1
where field2 in (:v_list)'; 

If so, how?

EDIT: With reference to Marcin's answer, how do I select from the resultant table?

declare

cursor c_get_csv_as_tables is
select in_list(food_list) food_list
from emp_food
where emp_type = 'PERM';

cursor c_get_food_list (v_food_table varchar2Table)is
select *
from v_food_table;

begin
    for i in c_get_csv_as_tables loop
        for j in c_get_food_list(i.food_list) loop
            dbms_output.put_line(j.element);
        end loop;
    end loop;
end;

I get the following error:

ORA-06550: line 10, column 6:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 9, column 1:
PL/SQL: SQL Statement ignored
ORA-06550: line 15, column 34:
PLS-00364: loop index variable 'J' use is invalid
ORA-06550: line 15, column 13:
PL/SQL: Statement ignored

Solution 1

Like in @Sathya link, you can bind the varray (I took @Codo example):

CREATE OR REPLACE TYPE str_tab_type IS VARRAY(10) OF VARCHAR2(200);
/
DECLARE
  l_str_tab str_tab_type;
  l_count NUMBER;
  v_sql varchar2(3000);
BEGIN
  l_str_tab := str_tab_type();
  l_str_tab.extend(2);
  l_str_tab(1) := 'TABLE';
  l_str_tab(2) := 'INDEX';

  v_sql := 'SELECT COUNT(*) FROM all_objects WHERE object_type IN (SELECT COLUMN_VALUE FROM TABLE(:v_list))';

  execute immediate v_sql into l_count using l_str_tab;

  dbms_output.put_line(l_count);
END;
/

UPDATE: the first command can be replaced with:

CREATE OR REPLACE TYPE str_tab_type IS TABLE OF VARCHAR2(200);
    /

then call:

l_str_tab.extend(1);

when ever you add a value

Solution 2

Unfortunately you cannot bind a list like this, however you can use a table function. Read this

Here's an example of usage based on your code:

declare

cursor c_get_csv_as_tables is
select in_list(food_list) food_list
from emp_food
where emp_type = 'PERM';

cursor c_get_food_list (v_food_table varchar2Table)is
select column_value food
from TABLE(v_food_table);

begin
    for i in c_get_csv_as_tables loop
        for j in c_get_food_list(i.food_list) loop
            dbms_output.put_line(j.food);
        end loop;
    end loop;
end;

I used here a column_value pseudocolumn

Solution 3

Bind variable can be used in Oracle SQL query with "in" clause.

Works in 10g; I don't know about other versions.

Bind variable is varchar up to 4000 characters.

Example: Bind variable containing comma-separated list of values, e.g.

:bindvar = 1,2,3,4,5

select * from mytable
  where myfield in
    (
      SELECT regexp_substr(:bindvar,'[^,]+', 1, level) items
      FROM dual
      CONNECT BY regexp_substr(:bindvar, '[^,]+', 1, level) is not null
    );

Solution 4

As per @Marcin's answer you can't do this, however, there's a fair bit to add to that, as your query should actually work, i.e. run.

Simply put, you cannot use a bind variable for a table or column. Not only that, bind variables they are assumed to be a character, so if you want a number you have to use to_number(:b1) etc.

This is where your query falls down. As you're passing in a string Oracle assumes that your entire list is a single string. Thus you are effectively running:

select field1
  from table1
where field2 = v_list

There is no reason why you can't do this a different way though. I'm going to assume you're dynamically creating v_list, which means that all you need to do is create this list differently. A series of or conditions is, purportedly :-), no different to using an in.

By purportedly, I mean never rely on something that's untested. Although Tom does say in the link that there may be performance constraints there's no guarantee that it wasn't quicker than using in to begin with. The best thing to do is to run the trace on your query and his and see what difference there is, if any.

SQL> set serveroutput on
SQL>
SQL> declare
  2
  3    l_string varchar2(32767);
  4    l_count number;
  5
  6  begin
  7
  8      for xx in ( select rownum as rnum, a.*
  9                    from user_tables a
 10                   where rownum < 20 ) loop
 11
 12        if xx.rnum = 1 then
 13          l_string := 'table_name = ''' || xx.table_name || '''';
 14        else
 15          l_string := l_string || ' or table_name = ''' || xx.table_name || '
''';
 16        end if;
 17
 18      end loop;
 19
 20      execute immediate 'select count(*)
 21                           from user_tables
 22                          where ' || l_string
 23                           into l_count
 24                                ;
 25
 26      dbms_output.put_line('count is ' || l_count);
 27
 28  end;
 29  /
count is 19

PL/SQL procedure successfully completed.

View more solutions