I am trying to replace the values in an IN Clause using Native Dynamic SQL in ORACLE (PL/SQL). For example I am trying to pass values (1,2,3....) to the IN Clause of an SQL in a Stored Procedure, as I will not know the number or paramters in the IN clause before calling the stored procedure. It could be one to 250 or even more.

So far this is what I have....
*********************************************************
CREATE OR REPLACE PROCEDURE DynamicPLSQLTest (
p_cursor_out IN OUT master.generic_type,
p_InText IN VARCHAR2)
IS
BEGIN
DECLARE
TYPE EmpCurTyp IS REF CURSOR;
emp_cv EmpCurTyp;
Emp_rec abc.Employee%ROWTYPE;
sql_stmt VARCHAR2(200);
BEGIN
sql_stmt := 'SELECT * FROM abc.Employee WHERE Emp_id IN (:j)';
OPEN emp_cv FOR sql_stmt USING p_InText;
LOOP
FETCH emp_cv INTO Emp_rec;
EXIT WHEN emp_cv%NOTFOUND;
OPEN p_cursor_out FOR
SELECT * FROM abc.Employee WHERE Emp_id = Emp_rec.Emp_id;
END LOOP;
CLOSE emp_cv;
END;

END DynamicPLSQLTest;
*********************************************************

The above stored proc is called using......

*********************************************************
set autoprint on
variable rv refcursor
exec DynamicPLSQLTest(:rv, '1056');
*********************************************************

I am trying to make it work for any number of paramteres, For example with this call..
*********************************************************
set autoprint on
variable rv refcursor
exec DynamicPLSQLTest(:rv, '1056,1059,1100,1174’);
*********************************************************

Thanks
Vasant