2

I have simple query like 

SELECT * FROM temp t WHERE t.id IN (:IDs)

When executed, it prompts me (Oracle SQL Developer) for entering value if IDs variable.

When I enter for example 169, everything runs smoothly, but when I try to enter multiple IDs, like 169,170,171, I get error Invalid Number even while putting it into ''.

I'm used to working with MS SQL and MySQL, so this is little confusing to me.

Anyone any suggestions.

Volt
  • 63
  • 1
  • 9

3 Answers3

2

The problem is the varying-IN list. In SQL Developer, when you are prompted to enter the value for the bind variable, you are simple passing it as 169,170,171 which it is not considering as a set of values.

What you could do is, have multiple binds -

SELECT * FROM temp t WHERE t.id IN (:ID1, :ID2)

When prompted, enter value for each bind.

UPDATE Alright, if the above solution looks ugly, then I would prefer the below solution -

WITH DATA AS
  (SELECT to_number(trim(regexp_substr(:ids, '[^,]+', 1, LEVEL))) ids
  FROM dual
    CONNECT BY instr(:ids, ',', 1, LEVEL - 1) > 0
  )
SELECT * FROM temp t WHERE it.d IN
  (SELECT ids FROM data
  )
/
Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
  • That's really ugly solution, since there can be hundreds of them and I need to pass them at once. :( – Volt Jan 12 '15 at 09:33
  • Another way is to use associative arrays. @Volt http://stackoverflow.com/questions/2885575/passing-an-array-of-data-as-an-input-parameter-to-an-oracle-procedure, assuming your query is in a procedure – Sathyajith Bhat Jan 12 '15 at 09:38
  • 1
    Also look at http://stackoverflow.com/questions/27738359/select-from-table-with-varying-in-list-in-where-clause/27739139#27739139 and https://lalitkumarb.wordpress.com/2015/01/02/varying-in-list-of-values-in-where-clause/ – Lalit Kumar B Jan 12 '15 at 09:52
0

If you put them into ", you get error. Oracle doesn't accept ". You should use just numbers without ". i.e: (169,170,171,...)

erakm
  • 106
  • 2
  • For both values: 1) 169,170,171 2) '169,170,171' I'm getting error "invalid number" – Volt Jan 12 '15 at 09:33
  • You can use first one,can't second one. If your ids are vharchar use like this ('169','170','171') – erakm Jan 12 '15 at 09:54
0

You can define a substitution variable as an array like so:

define IDS = (169,170,171);

and then use it like so:

SELECT * FROM temp t WHERE t.id IN &IDS;
CrazyBurrito
  • 105
  • 3
  • 8