Oracle SQL Variable: SELECT with STRING list of values in an IN clause
12,256
Enclose the DEFINE
value in double-quotes:
define letters = "'a','b','c','d'"
set verify on
select * from dual where dummy in (&letters);
old 1: select * from dual where dummy in (&letters)
new 1: select * from dual where dummy in ('a','b','c','d')
no rows selected
Note that the challenge is really how to DEFINE
a multi-word string, as it's the same problem:
SQL> define letters = I love kittens
SQL> define letters
DEFINE LETTERS = "I" (CHAR)
SQL> define letters = 'a','b','c','d'
SQL> define letters
DEFINE LETTERS = "a" (CHAR)
Double-quotes fix that:
SQL> define letters = "I love kittens"
SQL> define letters
DEFINE LETTERS = "I love kittens" (CHAR)
Just for completeness, the other way to set a define
variable is from a select
statement using the column new_value
approach. Historically this was provided for setting page headings dynamically in SQL*Plus reports, but it's handy in scripts. (It's also the only way to set the type to number
, not that it makes any difference.)
SQL> col letters new_value letters
SQL> select q'{'a','b','c','d'}' as letters from dual;
LETTERS
---------------
'a','b','c','d'
1 row selected.
SQL> def letters
DEFINE LETTERS = "'a','b','c','d'" (CHAR)
SQL> select 123.456 as letters from dual;
LETTERS
----------
123.456
1 row selected.
SQL> def letters
DEFINE LETTERS = 123.456 (NUMBER)
Author by
Peter Roman
Updated on July 26, 2022Comments
-
Peter Roman almost 2 years
This works correctly:
DEFINE numbers = '1,2,3,4,5'; SELECT * FROM table1 WHERE items IN (&numbers);
As does this:
SELECT * FROM table1 WHERE items IN ('a','b','c','d'); error
This returns a snytax ERROR:
DEFINE letters = 'a','b','c','d'; SELECT * FROM table1 WHERE items IN (&letters); -- result syntax ERROR
How can I use a
WHERE ... IN
clause using a list of strings created byDEFINE
?