How to enter binds for a multi-valued parameter in SQL Developer

Question

I have a lot of SQL with named parameters that I need to be able to execute in SQL Developer. For SQL where the parameters are scalar values it's easy to paste the SQL into a worksheet and SQL Developer will prompt me (in a dialog with the title "Enter Binds") to enter the parameter values. But for cases where the parameter needs to hold multiple values, like this:

select count(*) from foo 
where foo.id in (:ids)

where, say, :ids needs to be replaced with 1,2,3 so that the query executed is

select count(*) from foo 
where foo.id in (1,2,3)

I try entering the values into the dialog (and I've tried delimiting with commas, or just spaces, or wrapping everything in parens), and regardless what I try I get the error message:

ORA-01722: invalid number
01722. 00000 -  "invalid number"
*Cause:    
*Action:

Is there a syntax for entering the multiple values into the "Enter Binds" dialog so that SQL Developer will be able to perform the substitution correctly? Or are the bindings strictly limited to scalar values?

I'm using Oracle SQL Developer 3.2.20.09.

Alex Poole Alex Poole · Accepted Answer · 2014-02-27T17:52:50

This isn't a SQL Developer restriction, it's just how bind variables work. You're effectively doing:

select count(*) from foo 
where foo.id in ('1,2,3')

... which is really in (to_number('1,2,3')), hence the error. It'll work for a single value, give odd results for two values if your decimal separator is a comma, and fail for anything more.

You can't enter multiple values at a bind prompt, or supply multiple values to an in() with a single bind. You can ~~cheat~~ be a bit inventive though. The xmltable function will convert the comma-separated string into rows with one value in each:

var ids varchar2(50);
exec :ids := '1,2,3';
select * from xmltable(:ids);

COLUMN_VALUE
------------
1            
2            
3

You can then use that as a look-up table:

select count(*)
from xmltable(:ids) x
join foo f on f.id = to_number(x.column_value);

  COUNT(*)
----------
         3

How to enter binds for a multi-valued parameter in SQL Developer

1 Answers