COLLECT function in oracle

'COLLECT' ... its really an useful function. We can easily understand the use of this with the following 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);