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

If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

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;
*********************************************************

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’);
*********************************************************

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.

Replacing date in Dynamic SQL code

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;