ORACLE-SQL: Define and use multi-string variable

Question

I'm currently using a database where some of the numbers are stored in a text format (fyi: external database, not possible to change).

My codes have grown over time so I started to use variables at the top of my code to avoid adjusting the code multiple times when running a new analysis

Example:

define var = '1234'

select * from products
where
  art_id = '&&var'
;

This gives code gives me all articles with the ID 1234 - keep in mind, the ID is stored as a text (hence the "'"s)

Now, what do I have to do when I want to query for multiple products?

This doesn't work:

define var = '1234','2345'

select * from products
where
  art_id in ('&&var')
;

==> I miss the products with the ID 2345

Also, this doesn't work:

define var = ''1234','2345''

select * from products
where
  art_id in (&&var)
;

==> Error: missing expression

What I obviously want to create is a case in which the where clause is constructed like this:

art_id in ('1234','2345')

Thanks!

Answer 1

You need to escape the quotes:

define var = '''1234'',''2345'''

For example:

SQL> define var = '''a'', ''b'''
SQL> select 1 from dual where 'a' in ( &&var);
old   1: select 1 from dual where 'a' in ( &&var)
new   1: select 1 from dual where 'a' in ( 'a', 'b')

         1
----------
         1

Please notice that here I used quotes to handle string values; if you had numeric values, you could avoid quoting:

SQL> define var = '123, 234'
SQL> select 1 from dual where 1 in (&&var);
old   1: select 1 from dual where 1 in (&&var)
new   1: select 1 from dual where 1 in (123, 234)

no rows selected

SQL> select 1 from dual where 123 in (&&var);
old   1: select 1 from dual where 123 in (&&var)
new   1: select 1 from dual where 123 in (123, 234)

         1
----------
         1