XMLAgg to create CLOB

Use XmlAgg function to build up a dynamic string over the 4000 character limit, by using .getClobVal() to create a CLOB string


--get rid of the final UNION ALL
select
regexp_replace
(
nSQL,
'(^.*) UNION ALL $',
'\1'
) vSQL
from
(--append each line to a CLOB
select
replace
(
replace
(
XmlAgg(XmlElement("line", aSql) order by aSql desc nulls last).getClobVal(),
'',
''
),
'',
' '
) as nSql
from
(--select a line for each view to be selected from
select
'select ' ||
rule_skey||' rule_skey,'||
'key,' ||
'data,' ||
'dq_flag ' ||
'from ' ||
view_name ||
' UNION ALL' aSQL
from
dim_rule
where
active_flag = 'Y'
)
)