Oracle pl/sql dynamic query with IN paramters

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;