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

oracle, oracle11g, plsql

Solution

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;

Problem

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.

Original source

Related problems