sql - Using :var with "IN" operator in WHERE clause

Question

I'm usually working with MySQL or SQL Server and I've experienced quite a problem in Oracle SQL Developer.

I have query like this (making it simple just to replicate my issue):

SELECT *
FROM table t1
WHERE t1.date > :START_DATE AND t1.date < :END_DATE AND t1.id IN (:IDS)

When I run this query, the dialog window opens and I'm prompted to enter the variables.

Problem is when I enter comma separated ids like 5,6,7 or with quotes '5,6,7' I get this error:

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

Any ideas here?

PS: There has to be dialog prompt to enter variables. Colleagues are not SQL friendly. ;)

Answer 1

The issue is that the bind variable :ids contains a literal value (rather than a list of literal values) so your query is:

AND t1.id IN ( '5,6,7' )

instead of:

AND t1.id IN ( 5, 6, 7 )

What you need to do is either pass in a collection (which you could define from an array in an external language and pass in directly as a bind variable):

CREATE OR REPLACE TYPE intlist IS TABLE OF INTEGER;
/

SELECT *
FROM table t1
WHERE t1.date > :START_DATE
AND   t1.date < :END_DATE
AND   t1.id MEMBER OF intlist( 5, 6, 7 )

Or compare the list using LIKE:

SELECT *
FROM table t1
WHERE t1.date > :START_DATE
AND   t1.date < :END_DATE
AND   ',' || :ids || ',' LIKE '%,' || t1.id || ',%'

Or pass in a delimited string literal and split that:

SELECT *
FROM table t1
WHERE t1.date > :START_DATE
AND   t1.date < :END_DATE
AND   t1.id   IN ( SELECT TO_NUMBER( REGEXP_SUBSTR( :ids, 'd+', 1, LEVEL ) )
                   FROM   DUAL
                   CONNECT BY LEVEL <= REGEXP_COUNT( :ids, 'd+' ) );

(Or, a comma spearated list of numbers would work in the IN clause if you used a substitution variable instead of a bind variable.)