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.

Answer 1

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.