aggregate string concat function, using xml

the earliest sql aggregate concat discussion i have found dates back to 1997. i have been searching for quite a while. some code involves creating a type, using create type .... another pgsql solution requires the creation of a user-defined function.

select deptno, '' || xmlagg(xmlelement(a, ename)) from emp
group by deptno

i am looking for some easy, quick and dirty method.

what do you guys think about this code? this code returns all the names in each department. the names are concatenated into one xml string. further processing may be done with string substitution, regular expression, or xml parser

i tried the above code on a larger database and quickly hit the 4k string length limit. so i have used a clob to handle the long string

select deptno, xmlagg(xmlelement(a, ename)).getClobVal() from emp
group by deptno

i tested this in jdbc and it worked

and then i tried this 3rd sql

select '' || xmlagg(xmlelement(a, ename)) over(partition by deptno), emp.* from emp

i got ora-29400 data cartridge error

any idea? many thx Received on Tue Oct 19 2004 - 08:31:01 CDT