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?

score 11 · Answer 1 · answered Aug 12 '11 at 13:40

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

`apex_collection.create_or_truncate_collection` gives me `cannot insert NULL into ("APEX_040000"."WWV_FLOW_COLLECTIONS$"."SESSION_ID")` — Oleksandr Papchenko, Dec 08 '16 at 10:34
@OleksandrPapchenko You can only call `apex_collection` package from within an APEX session. — Tony Andrews, Dec 08 '16 at 17:07

score 3 · Answer 2 · edited Aug 13 '11 at 11:49

3

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,

edited Aug 13 '11 at 11:49

Dan Abramov

264,556
84
409
511

answered Aug 12 '11 at 13:37

vbguru

31
1

score 1 · Answer 3 · answered Aug 25 '11 at 08:37

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

You don't want or need the TO_CHAR around :P5_USER_ID_LIST – Tony Andrews Aug 25 '11 at 11:19 — Tony Andrews, Aug 25 '11 at 11:19

m12lrpv · Answer 4 · 2014-02-09T23:53:36.900

Please dont use: WHERE ','||to_char(:P5_USER_ID_LIST)||',' like '%,'||to_char(u.user_id)||',%' because you'll force a full table scan although with the users table you may not have that many so the impact will be low but against other tables in an enterprise environment this is a problem.

EDIT: I have put together a script to demonstrate the differences between the regex method and the wildcard like method. Not only is regex faster but it's also a lot more robust.

-- Create table
create table CSV_TEST
(
  NUM NUMBER not null,
  STR VARCHAR2(20)
);


create sequence csv_test_seq;

begin
  for j in 1..10 loop
    for i in 1..500000 loop
     insert into csv_test( num, str ) values ( csv_test_seq.nextval, to_char( csv_test_seq.nextval ));
    end loop;
    commit;
  end loop;
end;
/

-- Create/Recreate primary, unique and foreign key constraints 
alter table CSV_TEST
  add constraint CSV_TEST_PK primary key (NUM)
  using index ;
alter table CSV_TEST
  add constraint CSV_TEST_FK unique (STR)
  using index;

select sysdate from dual;
select *
from csv_test t
where t.num in ( Select Regexp_Substr('100001, 100002,   100003   ,      100004, 100005','[^,]+', 1, Level) From Dual
                 Connect By Regexp_Substr('100001, 100002,100003, 100004, 100005', '[^,]+', 1, Level) Is Not Null);
select sysdate from dual;

select *
from csv_test t
where ('%,' || '100001,100002,   100003,  100004  ,100005' || ',%') like '%,' || num || ',%';
select sysdate from dual;
select *
from csv_test t
where t.num in ( Select Regexp_Substr('100001, 100002,   100003   ,      100004, 100005','[^,]+', 1, Level) From Dual
                 Connect By Regexp_Substr('100001, 100002,100003, 100004, 100005', '[^,]+', 1, Level) Is Not Null);
select sysdate from dual;

select *
from csv_test t
where ('%,' || '100001,100002,   100003,  100004  ,100005' || ',%') like '%,' || num || ',%';
select sysdate from dual;

drop table csv_test;
drop sequence csv_test_seq;

score 0 · Answer 5 · answered Sep 10 '14 at 15:47

0

Solution from Tony Andrews works for me. The process should be added to "Page processing" >> "After submit">> "Processes".

answered Sep 10 '14 at 15:47

Bigyellowbee

123
1
12

score 0 · Answer 6 · answered Dec 07 '18 at 21:26

As you are Storing User Ids as String so You can Easily match String Using Like as Below

SELECT * FROM users u WHERE u.user_id LIKE '%'||(:P5_USER_ID_LIST)||'%'

For Example

:P5_USER_ID_LIST  = 45,4932,20,19

Your Query Surely Will return Any of 1 User Id which Matches to Users table

This Will Surely Resolve Your Issue , Enjoy

score 0 · Answer 7 · answered Aug 12 '11 at 13:30

0

Create a native query rather than using "createQuery/createNamedQuery"

answered Aug 12 '11 at 13:30

Kevin

3,509
4
31
45

Stephen ODonnell · Answer 8 · 2011-08-12T15:57:19.350

The reason this is an issue is that you cannot just bind an in list the way you want, and just about everyone makes this mistake at least once as they are learning Oracle (and probably SQL!).

When you bind the string '32,64,128', it effectively becomes a query like:

select ...
from t
where t.c1 in ('32,64,128')

To Oracle this is totally different to:

select ...
from t
where t.c1 in (32,64,128)

The first example has a single string value in the in list and the second has a 3 numbers in the in list. The reason you get an invalid number error is because Oracle attempts to cast the string '32,64,128' into a number, which it cannot do due to the commas in the string.

A variation of this "how do I bind an in list" question has come up on here quite a few times recently.

Generically, and without resorting to any PLSQL, worrying about SQL Injection or not binding the query correctly, you can use this trick:

with bound_inlist
  as
  (
  select
    substr(txt,
           instr (txt, ',', 1, level  ) + 1,
           instr (txt, ',', 1, level+1) - instr (txt, ',', 1, level) -1 )
           as token
    from (select ','||:txt||',' txt from dual)
  connect by level <= length(:txt)-length(replace(:txt,',',''))+1
  )
  select *
from bound_inlist a, users u
where a.token = u.id;

score 0 · Answer 9 · answered Sep 24 '11 at 13:25

0

If possible the best idea may be to not store your user ids in csv! Put them in a table or failing that an array etc. You cannot bind a csv field as a number.

answered Sep 24 '11 at 13:25

Emu

494
6
15

score -1 · Answer 10 · answered Aug 12 '11 at 13:28

-1

you will need to run this as dynamic SQL.

create the entire string, then run it dynamically.

answered Aug 12 '11 at 13:28

Randy

16,480
1
37
55

1

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. – Stephen ODonnell Aug 12 '11 at 15:51

PL/SQL query IN comma deliminated string

10 Answers10

Related