sql>create or replace type tNumberTable is table of number;
  2  /
 
Type created.
 
sql>create or replace function f_get_number_table_from_string(
  2      p_string     in  varchar2,
  3      p_delimiter  in  varchar2 default ',')
  4      return tNumberTable
  5  is
  6    v_string  long := p_string || p_delimiter;
  7    v_pos     pls_integer;
  8    v_data    tNumberTable := tNumberTable();
  9  begin
 10    loop
 11      v_pos := instr(v_string, p_delimiter);
 12      exit when (nvl(v_pos, 0) = 0);
 13      v_data.extend;
 14      v_data(v_data.count) := trim(substr(v_string, 1, v_pos - 1));
 15      v_string := substr(v_string, v_pos + 1);
 16    end loop;
 17    return (v_data);
 18  end f_get_number_table_from_string;
 19  /
 
Function created.
 
sql>var list varchar2(256)
sql>exec :list := '7369,7499,7521'
 
PL/SQL procedure successfully completed.
 
sql>select empno, ename
  2    from emp
  3   where empno in (select *
  4                     from table(cast(f_get_number_table_from_string(:list) as tNumberTable)));
 
    EMPNO ENAME
--------- ----------
     7369 SMITH
     7499 ALLEN
     7521 WARD
 
3 rows selected.