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

Question

I have a lot of SQL with named parameters that I need to execute in SQL Developer. For SQL, where the parameters are scalar values, it is easy to insert SQL into the worksheet, and SQL Developer will prompt me (in the dialog box with the heading "Enter Binds") to enter the parameter values. But for cases when the parameter must contain several values, for example:

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

where, say, :idsyou need to replace with 1,2,3so that the query being executed

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

I am trying to enter values ​​in a dialog (and I tried to delimit with commas or just spaces or wrap everything in parens), and no matter what I try, I get an error message:

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

" ", SQL Developer ? ?

Oracle SQL Developer 3.2.20.09.

Answer 1

SQL Developer, , . :

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

... in (to_number('1,2,3')), , . , , , - .

in() . cheat , . xmltable , , :

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

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

:

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

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