SQL> CREATE OR REPLACE TYPE mytabletype AS TABLE OF VARCHAR2 (255)
  2  /
 
Type created.
 
SQL> CREATE OR REPLACE FUNCTION in_list (
  2      p_string IN VARCHAR2
  3  )
  4  RETURN mytabletype
  5  AS
  6      l_string        LONG DEFAULT p_string || ',';
  7      l_data          mytabletype := mytabletype();
  8      n               NUMBER;
  9  BEGIN
 10      LOOP
 11          EXIT WHEN l_string IS NULL;
 12          n := INSTR(l_string, ',');
 13          l_data.EXTEND;
 14          l_data(l_data.COUNT) := TRIM(SUBSTR(l_string, 1, n-1));
 15          l_string := SUBSTR(l_string, n + 1);
 16      END LOOP;
 17
 18      RETURN (l_data);
 19  END in_list;
 20  /
 
Function created.
 
SQL> SELECT empno
  2  ,      ename
  3  FROM   emp
  4  WHERE  ename IN (SELECT * FROM TABLE(in_list('ADAMS,BLAKE,JONES')))
  5  /
 
     EMPNO ENAME
---------- ----------
      7876 ADAMS
      7698 BLAKE
      7566 JONES
 
SQL>