SQL> drop type  array1 ;

Type dropped.

SQL> CREATE TYPE array1 AS TABLE OF NUMBER
  2  /

Type created.

SQL> CREATE OR REPLACE FUNCTION ret_array(str VARCHAR2 )
  2  RETURN ARRAY1 PIPELINED
  3  IS
  4  str1  VARCHAR2(100);
  5  num1  NUMBER(5);
  6  BEGIN
  7  str1 := str ||',';
  8  WHILE LENGTH(str1)>=0
  9  LOOP
 10       num1 := TO_NUMBER(SUBSTR(str1,1,INSTR(str1,',',1)-1));
 11       pipe ROW (num1);
 12       str1 := SUBSTR(str1,INSTR(str1,',',1)+1);
 13  END LOOP;
 14  --NULL;
 15  RETURN  ;
 16  END;
 17  /

Function created.

SQL> select EMPNO, ENAME, JOB,DEPTNO
  2  FROM EMP
  3  WHERE deptno IN
  4       (SELECT COLUMN_VALUE FROM
  5        TABLE(ret_array(DECODE(&para,1,'10',2,'20,30'))));
Enter value for para: 1
old   5:       TABLE(ret_array(DECODE(&para,1,'10',2,'20,30'))))
new   5:       TABLE(ret_array(DECODE(1,1,'10',2,'20,30'))))

     EMPNO ENAME      JOB           DEPTNO
---------- ---------- --------- ----------
      7782 CLARK      MANAGER           10
      7839 KING       PRESIDENT         10
      7934 MILLER     CLERK             10

SQL> /
Enter value for para: 2
old   5:       TABLE(ret_array(DECODE(&para,1,'10',2,'20,30'))))
new   5:       TABLE(ret_array(DECODE(2,1,'10',2,'20,30'))))

     EMPNO ENAME      JOB           DEPTNO
---------- ---------- --------- ----------
      7566 JONES      MANAGER           20
      7902 FORD       ANALYST           20
      7876 ADAMS      CLERK             20
      7788 SCOTT      ANALYST           20
      7369 SMITH      CLERK             20
      7499 ALLEN      SALESMAN          30
      7900 JAMES      CLERK             30
      7844 TURNER     SALESMAN          30
      7654 MARTIN     SALESMAN          30
      7698 BLAKE      MANAGER           30
      7521 WARD       SALESMAN          30

11 rows selected.