1

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. ;)

MT0
  • 143,790
  • 11
  • 59
  • 117
Volt
  • 63
  • 1
  • 9
  • 1
    You can find the answer here: http://betteratoracle.com/posts/20-how-do-i-bind-a-variable-in-list – Galcoholic May 23 '17 at 07:59
  • Man, thanks a lot. That's a solution I was looking for. – Volt May 23 '17 at 08:31
  • 1
    Have you encountered *any* language, that on being passed a single string parameter will decide to (automatically) inspect the contents, observe commas, and instead decide to treat it as multiple separate parameters? (and yet, strangely, people *keep* expecting this to be the behaviour in SQL) – Damien_The_Unbeliever May 23 '17 at 08:36
  • @Damien_The_Unbeliever It would behave like that if a substitution variable was used instead of a bind variable. – MT0 May 23 '17 at 08:46

2 Answers2

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.)

MT0
  • 143,790
  • 11
  • 59
  • 117
0

Are IDs NUMBERS or VARCHAR2?

This simple case works for me. Please try it:

select id from
(
select 1 id from dual
union
select 2 id from dual
union
select 3 id from dual
)
where id in (:IDS)
OracleDev
  • 446
  • 3
  • 8