PL/SQL - comma separated list within IN CLAUSE

Question

I am having trouble getting a block of pl/sql code to work. In the top of my procedure I get some data from my oracle apex application on what checkboxes are checked. Because the report that contains the checkboxes is generated dynamically I have to loop through the

APEX_APPLICATION.G_F01 

list and generate a comma separated string which looks like this

v_list VARCHAR2(255) := (1,3,5,9,10);

I want to then query on that list later and place the v_list on an IN clause like so

SELECT * FROM users 
WHERE user_id IN (v_list);

This of course throws an error. My question is what can I convert the v_list to in order to be able to insert it into a IN clause in a query within a pl/sql procedure?

Answer 1

If users is small and user_id doesn't contain commas, you could use:

SELECT * FROM users WHERE ',' || v_list || ',' LIKE '%,'||user_id||',%'

This query is not optimal though because it can't use indexes on user_id.

I advise you to use a pipelined function that returns a table of NUMBER that you can query directly. For example:

CREATE TYPE tab_number IS TABLE OF NUMBER;
/
CREATE OR REPLACE FUNCTION string_to_table_num(p VARCHAR2)
   RETURN tab_number
   PIPELINED IS
BEGIN
   FOR cc IN (SELECT rtrim(regexp_substr(str, '[^,]*,', 1, level), ',') res
                FROM (SELECT p || ',' str FROM dual)
              CONNECT BY level <= length(str) 
                                  - length(replace(str, ',', ''))) LOOP
      PIPE ROW(cc.res);
   END LOOP;
END;
/

You would then be able to build queries such as:

SELECT * 
  FROM users 
 WHERE user_id IN (SELECT *
                     FROM TABLE(string_to_table_num('1,2,3,4,5'));

Answer 2

You can use XMLTABLE as follows

SELECT * FROM users 
WHERE user_id IN (SELECT to_number(column_value) FROM XMLTABLE(v_list));

Answer 3

I have tried to find a solution for that too but never succeeded. You can build the query as a string and then run EXECUTE IMMEDIATE, see http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/dynamic.htm#i14500.

That said, it just occurred to me that the argument of an IN clause can be a sub-select:

SELECT * FROM users 
WHERE user_id IN (SELECT something FROM somewhere)

so, is it possible to expose the checkbox values as a stored function? Then you might be able to do something like

SELECT * FROM users
WHERE user_id IN (SELECT my_package.checkbox_func FROM dual)

Answer 4

Personally, i like this approach:

with t as (select 'a,b,c,d,e' str from dual)
--
select val
from t, xmltable('/root/e/text()'
                 passing xmltype('<root><e>' || replace(t.str,',','</e><e>')|| '</e></root>')
                 columns val varchar2(10) path '/'
                )

Which can be found among other examples in Thread: Split Comma Delimited String Oracle

If you feel like swamping in even more options, visit the OTN plsql forums.