Currently i am using below query in OSB DB adapter it is working as expected. I stored 10 employee ids into #empids dynamically and getting 10 records as output.
Now i would like to know how to pass one more parameter (EMP_DEPT_IDs) in the IN clause?
SELECT emp_id,
emp_dept_id,
emp_nmae,
address,
email,
mobile
FROM employee
WHERE emp_id IN (WITH value_list AS(SELECT #empids 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)