how to pass a variable in WHERE IN clause of oracle sql?

Question

Hi
I have a variable $1 which hold comma separated email addresses like [email protected],[email protected] . I wish to pass this variable in a where clause like

where myColumn in ($1)

But obviously this won't work, I tried APEX_UTIL.STRING_TO_TABLE and DBMS_UTILITY.COMMA_TO_TABLE but in vain.

Any help appreciated.

Answer 1

As Pavanred alluded to, the easiest way -- though not necessarily the best -- is to interpolate the values yourself. You don't say what your calling language is, but something like:

sql = "SELECT something FROM whatever WHERE myColumn in (" + $1 + ")"

However, this means it's very important that you have pre-checked all the values in $1 to make sure that they are either numbers, or properly escaped strings, or whatever else it is that you need to pass but cannot be raw values supplied by a user, to avoid a SQL injection.

The other option is to make it a two-step process. First, insert the values from $1 into a temporary table, then select those values as a subquery:

WHERE myColumn in (SELECT temp_value FROM temp_table)