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