How to enable multiple variables to be searched when having a Colon search box

Question

Unable to make multiple search with = :variable.

I have a Select statement where I want to pull data, to do it in the most efficient way I have many places to gather the data from therefore I want null unless otherwise stated.

SELECT * FROM table WHERE (

(:nameColmn is null or nameColmn = :nameColmn)
AND
(:nameColmn2 is null or nameColmn2 = :nameColmn2)
AND
(:nameColmn3 is null or nameColmn3 = :nameColmn3)

)ORDER BY variable desc

there are 9 in the one I actually have, however it's the same.

A box prompts up where I can fill my data unless nothing is filled it stays blank (null)

I need to be able to add more then 1 variable, such as when doing

( nameColmn IN ('xyz','xyy','xyx'))

However this does not prompt a box. as they are set.

Answer 1

If you want to use Bind variable with multiple values, it can be passed as CSV string and split into rows.

SELECT * FROM yourtable
WHERE namecolmn IN ( SELECT regexp_substr(:vals,'[^,]+',1,level)
                     FROM dual CONNECT BY
                          regexp_substr(:vals,'[^,]+',1,level) IS NOT NULL
                   )

Note that the argument to this (:vals) should be xyz,xyy,xyx, i.e there can't be quotes in between.

Another option is to use Substitution variable (&vals), which will also prompt you for input and you can pass 'xyz','xyy','xyx' to get the desired result.

SET VERIFY OFF --diables substitution output.
SELECT * FROM yourtable
WHERE namecolmn IN (&vals);