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!

Aleksej · Accepted Answer · 2017-01-26 15:36:58Z

4

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

edited Jan 26, 2017 at 15:36

answered Jan 26, 2017 at 15:31

Aleksej

23.1k6 gold badges38 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

buddemat · Accepted Answer · 2021-06-28 08:52:02Z

1

The correct answer will be like this:

DEFINE var = "'1234','2345'";

SELECT * FROM products
WHERE art_id IN (&var)
;

--> 2 lines are selected.

edited Jun 28, 2021 at 8:52

buddemat

5,33016 gold badges37 silver badges61 bronze badges

answered Jun 24, 2021 at 21:43

ora_guru

111 bronze badge

Collectives™ on Stack Overflow

ORACLE-SQL: Define and use multi-string variable

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related