Monday 29 July 2013

Concatenate CLOB's without using PL/SQL functions

The following SQL concatenates clobs from a table into a
single clob without the use of PL/SQL functions.

create table t_clobs (c clob)
/
insert into t_clobs values (to_clob('A'));
insert into t_clobs values (to_clob('B'));
insert into t_clobs values (to_clob('C'));
commit;


select xmlserialize
        (
          content
            xmlagg(xmlelement(c, c)).extract('//text()') as clob
        ) as clobs_concatenated
from t_clobs;



SQL> select xmlserialize(content
  2           xmlagg(xmlelement(c, c)).extract('//text()')
  3           as clob
  4         ) as concatenated
  5  from t_clobs;

CONCATENATED
-----------------------------------------------------------
ABC