Se necesita que un condicional IN en consulta de oracle se cargue mas de un dato.

https://blogs.oracle.com/aramamoo/entry/how_to_split_comma_separated_string_and_pass_to_in_cl
ause_of_select_statement

Esta seria la sentencia:

 select regexp_substr('SMITH,ALLEN,WARD,JONES','[^,]+', 1, level) from dual
 connect by regexp_substr('SMITH,ALLEN,WARD,JONES', '[^,]+', 1, level) is not null;


REGEXP_SUBSTR('SMITH,A
----------------------
SMITH
ALLEN
WARD
JONES