ORCL - sys_connect_by_path

I really want to share this cool use of sys_connect_by_path function!

To deal the unknown length of concatenating string, I need to concatenate multiple rows into 1 string without decaring at certain length such as varchar2(X).

So here is the magic, hope you like it...

select ltrim(sys_connect_by_path(names, ','),',') jonit_entities from (
select names, row_number() over (order by names) rn, count(*) over () cnt
from (select entity.pref_mail_name names
from prospect_entity P, entity
where P.Prospect_Id = &id
and P.ID_NUMBER = entity.id_number
order by P.PRIMARY_IND desc)
)
where rn = cnt
start with rn = 1
connect by prior rn = rn-1;

cheers, for detail information about sys_connect_by_path, please check here.