Dynamic sql replacement of logical operators with OPEN-FOR-USING
Hello,
Is there any way I can do the replacement of the subject other than EXECUTE IMMEDIATE?
This is what I tried:
DECLARE
TYPE EmpCurTyp IS REF CURSOR;
v_emp_cursor EmpCurTyp;
emp_record employees%ROWTYPE;
v_stmt_str VARCHAR2(200);
v_e_job employees.job%TYPE;
BEGIN
-- Dynamic SQL statement with placeholder:
v_stmt_str := 'SELECT *
FROM employees
WHERE job_id = :j
:k
user_id = :l';
-- Open cursor & specify bind variable in USING clause:
OPEN v_emp_cursor FOR v_stmt_str
USING 'MANAGER' -- :j
,'AND' -- :k
,1; -- :l
-- Fetch rows from result set one at a time:
LOOP
FETCH v_emp_cursor INTO emp_record;
EXIT WHEN v_emp_cursor%NOTFOUND;
END LOOP;
-- Close cursor:
CLOSE v_emp_cursor;
END;
/
I understand that the second replacement(letter :k) when the v_stmt_str is opened will be used as 'AND' instead of AND, that's what is breaking it.
Is there any way I can do this without using EXECUTE IMMEDIATE and concatenating the values?
Thanks a lot for your help!
Leo
Edited by: leocoppens on Oct 5, 2012 4:46 PM