Code:
SQL> create or replace type test_list as object
(
a number,
b varchar2(30)
);
/
Type created.
SQL> create or replace type tb_list as table of test_list;
/
Type created.
SQL> create table test_tbl_list
as
select ceil(rownum/3) rn, object_id, object_name
from all_objects where rownum < 6;
Table created.
SQL> select
a.rn,
cast(multiset(select b.object_id a,
b.object_name b
from test_tbl_list b
where b.rn = a.rn) as tb_list) lst
from (select distinct rn rn from test_tbl_list) a
;
RN LST(A, B)
---------- --------------------------------------------------------------------------------------------------------------
1 TB_LIST(TEST_LIST(89, 'ACCESS$'), TEST_LIST(1283, 'ALL_ALL_TABLES'), TEST_LIST(1244, 'ALL_ARGUMENTS'))
2 TB_LIST(TEST_LIST(1506, 'ALL_ASSOCIATIONS'), TEST_LIST(1162, 'ALL_CATALOG'))
In any other cases u have to write addition function on pl/sql for build list of values.