Archive for May, 2014

How to pass comma separated values in IN Clause query to oracle

How to pass comma separated values in IN Clause query to oracle

Comma separated value = ‘O’,’P’,’I’

Generally we use below query in oracle and try to use same query in asp.net using procedure.

select * from tablename where column in (‘O’,’P’,’I’);

In procedure, we use p_parameter varible to pass comma separated value like,
select * from tablename where column in (p_parameter);

if you get output text query, it will work in oracle but not works in asp.net.
Solution

Use below mention query to resolve this issue.
So query should be
Select * from tablename where column in
(
SELECT substr(str, instr(str, ‘,’, 1, LEVEL) + 1, instr(str, ‘,’, 1, LEVEL + 1) –
instr(str, ‘,’, 1, LEVEL) – 1) column_value
FROM (SELECT ‘,’ || p_parameter || ‘,’ str FROM dual)
CONNECT BY LEVEL <= length(str) – length(REPLACE(str, ‘,’)) – 1
)