Barriers: I can't CREATE anything. No GTT's, PROCs, etc. I'm querying an Exadata data lake and whatever script I create will be executed from Toad or SQL Developer. The platform is Oracle 11g.
I'm creating a self-serve report that relies on two types of parameters: Date Range and DNIS (essentially, a number identifying an IVR call flow). There is at least one DNIS, potentially as many as three:
WHERE DNIS IN ('1234567', '9876543', '1232345')
I would like to create 3 bind variables: :DNIS1, :DNIS2, and :DNIS3 where one or more variables may be populated and if not, NULL.
In Theory:
WHERE DNIS IN (:DNIS1, :DNIS2, :DNIS3)
Perhaps a better question would be; given my limitations, how would one create a dynamic IN() clause? Is this possible?
Thanks to Sudipta Mondal (see comments) the answer was to wrap the bind variables in TO_CHAR()
WHERE DNIS IN (to_char(:DNIS1), to_char(:DNIS2), to_char(:DNIS3) )