Using IN() in SQL

We can’t combine bind variables and the “IN” clause, but there is another way.

You can’t do this:

select 1
from psroleuser
where roleuser = :1
and rolename in (:2)

rolename in (:2) just doesn’t work : (

However, you can do this …rather more complex method:

select 1
from psroleuser
where roleuser = :1
and rolename in (
   select regexp_substr(:2, '[^,]+', 1, level) token
   from dual
   connect by level <= length(:2) - length(replace(:2, ',', '')) + 1
)