Dynamic query, parameters not working for where in

Question

I am working with dynamic queries on oracle and when I pass the string parameter for where in condition, its not working.

for x in (
            SELECT DISTINCT column_id
            FROM table
            WHERE column_id in (in_column_ids)          
            /* WHERE column_id in (15,16,17) =>works */
            /* => in_column_ids is a varchar type which 
                  holds comma separated value */
            and column_title=in_column_title /* works */
    )

Here, if I keep the values directly on that in_column_ids, the query works. But, the value that is being passed as parameter doesn't seem to work for where in.

Any idea ?

score 2 · Accepted Answer · answered May 09 '13 at 09:39

IMO, you have to splits comma delimited variable using regexp_substr. Your query should be like this:

for x in (
            SELECT DISTINCT column_id
            FROM table
            WHERE column_id in (
            SELECT DISTINCT regexp_substr(in_column_ids,'[^,]+', 1, LEVEL) FROM DUAL
            CONNECT BY regexp_substr(in_column_ids, '[^,]+', 1, LEVEL) IS NOT NULL
            )          
            /* WHERE column_id in (15,16,17) =>works */
            /* => in_column_ids is a varchar type which 
                  holds comma separated value */
            and column_title=in_column_title /* works */
    )

Check out SQLFIDDLE DEMO

Dynamic query, parameters not working for where in

1 Answers1