select distinct
rv.view_alias data_object,
listagg(rf.folder_name,'; ') within group (order by rf.folder_name) over (partition by rv.view_id) data_object_folders,
rv.schema_name,
rv.db_view_name db_view,
case when rv.description<>rv.view_alias and rv.description<>rv.db_view_name then rv.description end description,
decode(rv.view_type,'D','Database Object','S','SQL Query') view_type,
xxen_util.user_name(rv.created_by) created_by,
xxen_util.client_time(rv.creation_date) creation_date,
xxen_util.user_name(rv.last_updated_by) last_updated_by,
xxen_util.client_time(rv.last_update_date) last_update_date,
lower(replace(regexp_replace(rv.db_view_name,'([^_]{1})[^_]*','\1'),'_')) alias,
rv.view_id
from
rx_views rv,
rx_folder_association rfa,
rx_folders rf
where
1=1 and
rv.view_id=rfa.view_id(+) and
rfa.folder_id=rf.folder_id(+)
order by
rv.schema_name,
rv.db_view_name |