How to write an array literal in SQL?-CodePudding

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?

CodePudding user response：

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