Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

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)
)
/
1 rows affected

select * from tab_mydata
/
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