Pass value stored in a PL/SQL variable into an IN clause

Question

I have the code as follows:

DECLARE
vr_arr_list VARCHAR2(100) := `7,3,4';
BEGIN
FOR CX IN (Select ID, NAME from TBL_DEMO where ID IN (vr_arr_list))
LOOP
DBMS_OUTPUT.PUT_LINE(CX.ID || '-' || CX.NAME);
END LOOP;
/

I had also tried using

DBMS_UTILITY.comma_to_table (list => REGEXP_REPLACE (vr_arr_list, '(^|,)','\1x'), tablen => l_ID_count, tab => l_ID_array);

But passing the array l_ID_array also didn't work.

Kindly assist.

Answer 1

Another way is to make use of Nested tables in conjunction with TABLE operator

create type nt_vr_arr_list is table of number;

DECLARE
  vr_arr_list  nt_vr_arr_list := nt_vr_arr_list(100, 200, 330);
BEGIN
  FOR cx IN (SELECT id, name
               FROM tbl_demo
              WHERE id IN (SELECT COLUMN_VALUE FROM TABLE(vr_arr_list))) LOOP
    DBMS_OUTPUT.put_line('ID: ' || cx.id || ' Name: ' || cx.name);
  END LOOP;
END;

Answer 2

where ID IN (vr_arr_list)

It is a Varying IN list of values in WHERE clause. IN (‘1, 2, 3′) is NOT same as IN (1, 2, 3) OR IN(‘1′, ‘2’, ‘3’). Please see more explanation about Varying IN list of values in WHERE clause.

You could do it in many other ways:

1. REGEXP_SUBSTR
2. XML
3. Table function
4. Pipelined function

I have already answered here https://stackoverflow.com/a/26273192/3989608

You could use DBMS_UTILITY.comma_to_table.

SQL> set serveroutput on;
SQL> DECLARE
  2    l_tablen BINARY_INTEGER;
  3    l_tab DBMS_UTILITY.uncl_array;
  4    CURSOR cur
  5    IS
  6      SELECT 'word1, word2, word3, word4, word5, word6' val FROM dual;
  7    rec cur%rowtype;
  8  BEGIN
  9    OPEN cur;
 10    LOOP
 11      FETCH cur INTO rec;
 12      EXIT
 13    WHEN cur%notfound;
 14      DBMS_UTILITY.comma_to_table (
 15      list => rec.val, tablen => l_tablen, tab => l_tab);
 16      FOR i IN 1 .. l_tablen
 17      LOOP
 18        DBMS_OUTPUT.put_line(i || ' : ' || trim(l_tab(i)));
 19      END LOOP;
 20    END LOOP;
 21    CLOSE cur;
 22  END;
 23  /
1 : word1
2 : word2
3 : word3
4 : word4
5 : word5
6 : word6

PL/SQL procedure successfully completed.

SQL>

There are also other ways, you could refer my demonstrations here.

Answer 3

You can simply use the MEMBER OF keyword for this. But, the input has to be an UDT (Nested table)

CREATE TYPE my_array IS TABLE OF NUMBER;

DECLARE
  vr_arr_list  my_array := my_array(7, 3, 4);
BEGIN
    FOR CX IN (Select ID, NAME from TBL_DEMO
               where ID MEMBER OF vr_arr_list)
..

Answer 4

There are much simpler way to do this:

Select ID, NAME from TBL_DEMO where  ','||vr_arr_list||',' like '%,'|| ID ||',%'

Answer 5

You can not do it like that. Number of bind variables to a query must be fixed. Oracle query gets optimized, compiled and you can reuse exec. plan many times. But each call must use the same number of bind variables. Oracle might even create different(better) exec. plan for a query where you have many values in IN clause.

So this:

Select ID, NAME from TBL_DEMO where ID IN (?,?);

and this:

Select ID, NAME from TBL_DEMO where ID IN (?,?,?);

can never have the same exec. plan id. The ways how to bypass this limitation are mentioned in Lalit's answer.