CREATE OR REPLACE PROCEDURE testtest (i_depts IN VARCHAR2)
IS
   i       NUMBER;
   l_sql   VARCHAR2 (2000);
BEGIN
   l_sql := 'Select empno from emp where deptno in  (' || i_depts || ')';
   DBMS_OUTPUT.put_line (l_sql);

   FOR i IN l_sql
   LOOP
      EXIT WHEN SQL%NOTFOUND;
      DBMS_OUTPUT.put_line ('EmpNo' || i.empno);
   END LOOP;
END;

Open RefCursor for l_sql;

SCOTT@orcl_11g> CREATE OR REPLACE PROCEDURE TestTest
  2    (i_depts IN VARCHAR2)
  3  AS
  4  BEGIN
  5  	FOR i IN
  6  	  (SELECT empno
  7  	   FROM   emp
  8  	   WHERE  deptno IN
  9  		  (SELECT deptno
 10  		   FROM   TABLE (CAST (str2tbl (i_depts) AS MyTableType)))
 11  	   ORDER  BY empno)
 12  	LOOP
 13  	  DBMS_OUTPUT.put_line ('EmpNo ' || i.empno);
 14  	END LOOP;
 15  END TestTest;
 16  /

Procedure created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> execute testtest ('20,30,40')
EmpNo 7369
EmpNo 7499
EmpNo 7521
EmpNo 7566
EmpNo 7654
EmpNo 7698
EmpNo 7782
EmpNo 7788
EmpNo 7839
EmpNo 7844
EmpNo 7876
EmpNo 7900
EmpNo 7902
EmpNo 7934

PL/SQL procedure successfully completed.

SCOTT@orcl_11g>