COLLECT function in Oracle

You will understand this with this example.

create table test(a varchar2(10),b number);
insert into test values('a',1);
insert into test values('a',2);
insert into test values('a',3);
insert into test values('b',1);
insert into test values('b',2);
insert into test values('b',3);

CREATE or replace type test_t AS TABLE OF number;

select a,CAST(COLLECT(b) as test_t ) from test group by a;

A CAST(COLLECT(B)ASTEST_T)
--------------------------------------------------------------------------------
a TEST_T(1, 2, 3)
b TEST_T(1, 2, 3)

Lastly,if the column is varchar2 type,then create type of varchar2.

CREATE or replace type test_t AS TABLE OF varchar2(200);