passed multiple values in the bind parameter

Question

I am running the following query but it is not working

select * From  table r where account_id = 1111 and phone_number 
in (   :phone )

I passed these values

(1111111111,2222222222,3333333333)

But it did not return any result. If I pass one of them only then it did return the result.

There are various ways to tackle this, but first, where do the individual values come from and how are you constructing the single bind value (CSV string)? If you can use a collection instead of a string then it will be simpler; or if they are coming from the same database then you might be able to avoid using a variable at all. — Alex Poole, Aug 27 '21 at 15:45

MT0 · Answer 1 · 2021-08-27T16:09:30.350

You can use:

select /*+ full(r) parallel(r,8) */
       *
From   table_name r
where  account_id = 1111
and    ','||:phone:||',' LIKE '%,'||phone_number||',%'

As to your query:

But it did not return any result. If I pass one of them only then it did return the result.

A bind variable is a single string and not a list of strings so your query is the equivalent of:

select /*+ full(r) parallel(r,8) */
       *
From   table_name r
where  account_id = 1111
and    phone_number in ( '1111111111,2222222222,3333333333' );

and is not your expected query of:

select /*+ full(r) parallel(r,8) */
       *
From   table_name r
where  account_id = 1111
and    phone_number in ( '1111111111','2222222222','3333333333' );

Since the table does not contain a phone number that matches the string '1111111111,2222222222,3333333333' then it returns zero rows.

passed multiple values in the bind parameter

1 Answers1