1

I have a query that looks like this:

select * from foo where id in (:ids)

where the id column is a number.

When running this in TOAD version 11.0.0.116, I want to supply a list of ids so that the resulting query is:

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

The simple minded approach below gives an error that 1,2,3 is not a valid floating point value. Is there a type/value combination that will let me run the desired query?

CLARIFICATION: the query as shown is how it appears in my code, and I am pasting it into TOAD for testing the results of the query with various values. To date I have simply done a text replacement of the bind variable in TOAD with the comma separated list, and this works fine but is a bit annoying for trying different lists of values. Additionally, I have several queries of this form that I test in this way, so I was looking for a less pedestrian way to enter a list of values in TOAD without modifying the query. If this is not possible, I will continue with the pedestrian approach.

toad variable prompt

jww
  • 97,681
  • 90
  • 411
  • 885
pierus
  • 283
  • 4
  • 14
  • 1
    possible duplicate of [sqlplus - using a bind variable in "IN" clause](http://stackoverflow.com/questions/4973809/sqlplus-using-a-bind-variable-in-in-clause) – OldProgrammer Feb 11 '14 at 20:15
  • I don't see how this is a duplicate of that question. Are you saying that Gerrat's answer that "You can't use comma-separated values in one bind variable" applies to the TOAD dialog above in the same way it applies to the PL/SQL block shown there? – pierus Feb 11 '14 at 20:30
  • That is correct. It has nothing to do with TOAD. You either have to use other methods to "break apart" the string into individual data items (as shown in duplicate answer) or use separate variables in the query such as, "in (:v1,:v2,:v3...); – OldProgrammer Feb 11 '14 at 20:34
  • Fair enough. If you want to post that as an answer I will accept it. – pierus Feb 11 '14 at 20:48

1 Answers1

2

As indicated by OldProgrammer, the Gerrat's answer that "You can't use comma-separated values in one bind variable" in the indicated thread correctly answers this question as well.

pierus
  • 283
  • 4
  • 14