Oracle SQL: Use IN operator with inline array

Question

Is it possible to define an inline array to use with the IN operator in Oracle SQL 12c? Pseudo code:

SELECT *
FROM T_AUDIO_PLAYERS
WHERE NAME IN {'foo', 'bar'};

Background: I have a Java tool, that's reading in valid .SQL files and replaces SQL-Variables like :NAME before execution.

Could you please elaborate what you mean by user defined array? You mean dynamic IN list? Also, {} is invalid syntax, you need to use (). — Lalit Kumar B
– Lalit Kumar B, Commented Nov 23, 2015 at 8:37
I just meant inline, without the need to declare the array elsewhere. — Boern
– Boern, Commented Nov 23, 2015 at 8:45
You can't declare the IN list to use it dynamically in SQL. You need to simply provide the static values at run time, WHERE NAME IN ('foo', 'bar'). — Lalit Kumar B
– Lalit Kumar B, Commented Nov 23, 2015 at 8:49
Oracle has no array data type for SQL (it has arrays in PL/SQL, but that is something different) — user330315
– user330315, Commented Nov 23, 2015 at 9:28

swebbo · Accepted Answer · 2015-11-23 08:40:15Z

2

I am not sure if I understand your question correctly. However, if you change your {-brackets to (-brackets, this should already work:

SELECT *
FROM T_AUDIO_PLAYERS
WHERE NAME IN ('foo', 'bar');

answered Nov 23, 2015 at 8:40

swebbo

3081 silver badge7 bronze badges

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

Comments

Muhammad Muazzam · Accepted Answer · 2015-11-23 09:36:58Z

0

You can use LISTAGG function like

SELECT *
FROM T_AUDIO_PLAYERS
WHERE NAME IN (select LISTAGG() ...);

More detail here

answered Nov 23, 2015 at 9:36

Muhammad Muazzam

2,8006 gold badges37 silver badges64 bronze badges