Skip to content
Advertisement

How to write an array literal in SQL?

Is it possible to use an array literal in Oracle, such as:

SELECT [1,2,3] FROM dual
# [1,2,3] -- int[]

Or:

SELECT ([1,2,3])[0] FROM dual
# 1 -- int

Or what’s the simplest (i.e., possible not in PL/SQL?) way to do this?

Advertisement

Answer

if you wanta flexible list, y an use a type like this

create type t_inttable as table of int
/
create table tab_mydata (
  id         number(10),
  intlist   t_inttable
)
nested table intlist store as ntab_intlist
insert into tab_mydata values(
  1, 
  t_inttable(1,2,3)
)
/
select * from tab_mydata
/
ID | INTLIST
-: | :------
select t.id, x.column_value from tab_mydata t, table(intlist) x
/
ID | COLUMN_VALUE
-: | -----------:
 1 |            1
 1 |            2
 1 |            3
select t.id, x.column_value from tab_mydata t, table(intlist) x
where x.column_value like 3
/
ID | COLUMN_VALUE
-: | -----------:
 1 |            3

db<>fiddle here

User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement