Passing Dynamic value to IN clause

I came across a wonderful solution for passing values dynamically to IN clause in SQL query and would like to share with all.

Problem:
To pass the list of values in IN clause in SQL Query dynamically. We need to just keep in mind that giving ? after IN clause will not work.

Solution:
1) Use DB Adapter, select Execute Custom SQL as Operation Type and click on Next
2)You need to write a SQL query like the one given below if you want to use IN clause in SQL query and pass the value list dynamically:

SELECT ID,FNAME,MNAME,LNAME,LOC,DEPT
FROM EMP
WHERE LOC
IN (WITH VALUE_LIST AS
(SELECT ? val FROM dual)
SELECT SUBSTR(val, (decode(LEVEL, 1, 0, instr(val, ':', 1, LEVEL -1)) + 1), (decode(instr(val, ':', 1, LEVEL) -1, -1, LENGTH(val), instr(val, ':', 1, LEVEL) -1)) -(decode(LEVEL, 1, 0, instr(val, ':', 1, LEVEL -1)) + 1) + 1) a
FROM VALUE_LIST CONNECT BY LEVEL <=
(SELECT(LENGTH(val) -LENGTH(REPLACE(val, ':', NULL)))
FROM VALUE_LIST) + 1)


NOTE1:
a) Here you need to change the portion showed in Bold as per your query requirement.
b) In this SQL, value list for IN clause should be delimited with a : symbol, If you want to use any other symbol as a delimiter you need to replace : with the symbol you want to use.


3) You need to create IN value list dynamically. Use XSL transformation to generate the dynamic value list and pass it to the DB adapter.

4) To query all the employees from US,INDIA,UK locations you need to create an IN clause value list as US:INDIA:UK

NOTE2:
While generating value list you don’t need to wrap character data with apostrophe.


Limitations:
1) If you create a very big value list for IN query (greater than 4000 characters) than you need to break value list and invoke this query multiple times, otherwise you will get ORA-01704: string literal too long error.

2) If you use SELECT * rather than giving the field list with SELECT, DB adapter won't be able to generate a correct XSD for request.