PL/SQL query IN comma deliminated string

Question

I am developing an application in Oracle APEX. I have a string with user id's that is comma deliminated which looks like this,

45,4932,20,19

This string is stored as

:P5_USER_ID_LIST

I want a query that will find all users that are within this list my query looks like this

SELECT * FROM users u WHERE u.user_id IN (:P5_USER_ID_LIST);

I keep getting an Oracle error: Invalid number. If I however hard code the string into the query it works. Like this:

SELECT * FROM users u WHERE u.user_id IN (45,4932,20,19);

Anyone know why this might be an issue?

Answer 1

A bind variable binds a value, in this case the string '45,4932,20,19'. You could use dynamic SQL and concatenation as suggested by Randy, but you would need to be very careful that the user is not able to modify this value, otherwise you have a SQL Injection issue.

A safer route would be to put the IDs into an Apex collection in a PL/SQL process:

declare
    array apex_application_global.vc_arr2;
begin
    array := apex_util.string_to_table (:P5_USER_ID_LIST, ',');
    apex_collection.create_or_truncate_collection ('P5_ID_COLL');
    apex_collection.add_members ('P5_ID_COLL', array);
end;

Then change your query to:

SELECT * FROM users u WHERE u.user_id IN 
(SELECT c001 FROM apex_collections
 WHERE collection_name = 'P5_ID_COLL')

Answer 2

An easier solution is to use instr:

SELECT * FROM users u 
WHERE instr(',' || :P5_USER_ID_LIST ||',' ,',' || u.user_id|| ',', 1) !=0;

tricks:

',' || :P5_USER_ID_LIST ||','

to make your string ,45,4932,20,19,

',' || u.user_id|| ','

to have i.e. ,32, and avoid to select the 32 being in ,4932,