create type t_num is table of number;
/
create type v_num is varray(5) of number;
/
Create a table to store these collection types. Use the nested table clause to prevent ORA-22913: must specify table name for nested table column or attribute error message.
create table tab_h (
txt varchar2(20),
nums_x t_num,
nums_5 v_num
)
nested table nums_x store as tab_n;
Find the heap table and the nested table in the data dictionary:
select
obj.object_name obj_name,
obj.object_type obj_type,
tab.nested
from
user_objects obj left join
user_tables tab on obj.object_name = tab.table_name
where
obj.object_name in ('TAB_H', 'TAB_N');
Insert a few records into the heap table:
insert into tab_h values ('four', t_num(1,2,3,4), v_num(4 ));
insert into tab_h values ('two' , t_num(3,1 ), v_num(1,2,3,4,5));
Select these values. first from the «column» nums_x:
select
txt,
t.column_value
from
tab_h, table(tab_h.nums_x) t;
And then also from the column nums_5:
select
txt,
v.column_value
from
tab_h, table(tab_h.nums_5) v;