Using Native Dynamic SQL in PL/SQL to pass multiple parameters to the IN CLAUSE

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

Why don't you just send in a delimited string and parse out the values with a function creating a collection? You can also just pass in a collection if you are calling this procedure from another procedure.

I realized that I was using the Dynamic SQL syntax for the Oracle Precompiler and not the Native Dynamic SQL.

Here is the code that I finally used.
************************************************
CREATE OR REPLACE PROCEDURE DynShowProd (
p_from_date IN VARCHAR,
p_to_date IN VARCHAR,
p_cursor_out IN OUT master.generic_type)
IS

BEGIN

OPEN p_cursor_out FOR
'SELECT D_DATE FROM MSA.LOG_PROGRAMS where
(ABC_DATE BETWEEN TO_DATE(p_from_date,''MM/DD/YYYY'') AND TO_DATE(p_to_date,''MM/DD/YYYY''))';
END;
/
***************************************************

The probmlem with this is that this does not work unless I replace p_from_date and p_to_date with the actual dates ex. '01/02/2003' as below
***************************************************

OPEN p_cursor_out FOR
'SELECT D_DATE FROM DEF.Employee where
(ABC_DATE BETWEEN TO_DATE('01/02/2003' ,''MM/DD/YYYY'') AND TO_DATE('01/26/2003' ,''MM/DD/YYYY''))';
END;

Thanks for your response.
Vasant