Issue: Sending dynamic paramter to oracle pl/sql.
Example : you will pass a paramter of employee names to the procedure and you want to dynamically construct the SQL query.
Parameter Passed :
v_emp
Value Passed :
v_emp = ‘haneef,ganesh,rajesh,minaal’
This query will not work:
SELECT emp_id, emp_name from,
FROM EMP_TABLE
WHERE emp_name IN v_emp;
Working Query
SELECT emp_id, emp_name from,
FROM EMP_TABLE
WHERE emp_name IN
(SELECT EXTRACTVALUE (xt.COLUMN_VALUE, ‘e’)
FROM TABLE (
XMLSEQUENCE (
EXTRACT (
xmltype (
‘<coll><e>’
|| REPLACE (v_emp,
‘,’,
‘</e><e>’)
|| ‘</e></coll>’),
‘/coll/*’))) xt)
ORDER BY emp_id ASC;