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

Cavitation

possible duplicate of PL/SQL use VARRAY in IN CLAUSE – Mcgee

@Sathya I don't understand... how's that the same? I need to pass a bind variable. Is there a similar way to do this? – Cavitation

I didn't realize you wanted to bind. having said that, afaik, you can't use a bind variable like that. – Mcgee

but you can still bind a varray like in the link @Sathya gave you – Bartizan

possible duplicate of How do I check for a IN condition against a dynamic list in Oracle? – Roughen

Answer 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

Bartizan

Ah I understand now. But this solution seems to require us to know the maximum number of values to set the length of the array. It also requires converting the CSV string into an array. The second can be done, but the first should be difficult. – Cavitation

I used the VARRAY just because it was in the original example. You can use a different collection such as TABLE of VARCHAR2(200). The extend method can be called for each value added (when converting the CSV) – Bartizan

Answer 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

Faceharden

Thanks! I will wait for a day to see if there are any other solutions and then mark this as solution. – Cavitation

I ran into a problem with this approach. How do I select from the resultant table, since I don't have the "column" name? – Cavitation

Answer 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
    );

Petrinapetrine

Answer 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.

Nitrification