SQL>
SQL> --
SQL> -- Check the data in the EMPLOYEES table for a few EMPLOYEE_IDs
SQL> --
SQL> select employee_id, first_name, last_name
  2    from employees
  3   where employee_id in (100, 121, 145, 179, 202);
 
EMPLOYEE_ID FIRST_NAME           LAST_NAME
----------- -------------------- -------------------------
        100 Steven               King
        121 Adam                 Fripp
        145 John                 Russell
        179 Charles              Johnson
        202 Pat                  Fay
 
5 rows selected.
 
SQL>
SQL> --
SQL> var str varchar2(30)
SQL> exec :str := '100,121,145,179,202';
 
PL/SQL procedure successfully completed.
 
SQL> print str
 
STR
------------------------------
100,121,145,179,202
 
SQL>
SQL> --
SQL> -- Query 1: Should work in any (non-ancient / real-life) version of Oracle
SQL> --
SQL> select e.employee_id, e.first_name, e.last_name
  2    from employees e,
  3         (
  4              select substr(','||:str||',',
  5                            instr(','||:str||',',',',1,level) + 1,
  6                            instr(','||:str||',',',',1,level+1) - instr(','||:str||',',',',1,level) - 1
  7                           ) as token
  8                from dual
  9               connect by level <= length(:str) - length(replace(:str,',')) + 1
 10         ) x
 11   where e.employee_id = x.token;
 
EMPLOYEE_ID FIRST_NAME           LAST_NAME
----------- -------------------- -------------------------
        100 Steven               King
        121 Adam                 Fripp
        145 John                 Russell
        179 Charles              Johnson
        202 Pat                  Fay
 
5 rows selected.
 
SQL>
SQL> --
SQL> -- Query 2: Should work in Oracle 10g and higher
SQL> --
SQL> select e.employee_id, e.first_name, e.last_name
  2    from employees e,
  3         (
  4              select regexp_substr(:str,'[^,]+',1,level) as token
  5                from dual
  6               connect by level <= length(:str) - length(replace(:str,',')) + 1
  7         ) x
  8   where e.employee_id = x.token;
 
EMPLOYEE_ID FIRST_NAME           LAST_NAME
----------- -------------------- -------------------------
        100 Steven               King
        121 Adam                 Fripp
        145 John                 Russell
        179 Charles              Johnson
        202 Pat                  Fay
 
5 rows selected.
 
SQL>
SQL>