Array type in Variables dialog

I want to test a simple select statement that allows the user to change the where clause at runtime via the binding mechanism:

select * from my_table where id in (:id_arg);

This works fine as long as I only use one value for id_arg. But for my testing I need to be able to supply more than one value, e.g.:

select * from my_table where id in (1, 2, 3);

The Variables dialog where the users would enter the values for :id_arg does not seem to allow for an array type. Is there a way to do what I want to do?

Capture

Oracle does not allow for this. You can use a substitution variable, instead.

select * from my_table where id in (&id_arg);

Now whatever they pass in will simply replace the &id_arg variable verbatim. Entering 1, 2, 3 into the variable value dialog will result in select * from my_table where id in (1, 2, 3); being executed.

1 Like