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?

Solution 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')

Solution 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,

Solution 3

I have faced this situation several times and here is what i've used:

SELECT * 
  FROM users u 
 WHERE ','||to_char(:P5_USER_ID_LIST)||',' like '%,'||to_char(u.user_id)||',%'

ive used the like operator but you must be a little carefull of one aspect here: your item P5_USER_ID_LIST must be ",45,4932,20,19," so that like will compare with an exact number "',45,'".

When using it like this, the select will not mistake lets say : 5 with 15, 155, 55.

Try it out and let me know how it goes;)

Cheers , Alex

Comments

[Stephen ODonnell]

Don't do this - firstly if the query is run frequently for many user_id's it will flood the shared pool with many unusable cursors and quickly the entire DB will have a hard parsing problem. There is also a significant risk of SQL Injection if you are not VERY careful.

[Tony Andrews]

You don't want or need the TO_CHAR around :P5_USER_ID_LIST

[Oleksandr Papchenko]

apex_collection.create_or_truncate_collection gives me cannot insert NULL into ("APEX_040000"."WWV_FLOW_COLLECTIONS$"."SESSION_ID")

[Tony Andrews]

@OleksandrPapchenko You can only call apex_collection package from within an APEX session.