Sort result from xmlagg(xmlelement(...)

Question

I got this SQL query:

 select rtrim(extract(xmlagg(xmlelement(e, column_name || ',')),
 '/E/text()').getclobval(), ',') from all_tab_columns
 where OWNER = 'TESTER' AND TABLE_NAME = 'H4_POSIT';

I using this instead of LISTAGG(column_name, ',') because the result is going to exceed the limit of varchar2 (>4000). Now I am asking myself whether it is possible to sort the result like LISTAGG does it.

So when having columns FERA, BAUT, CHECK_ID, ... I'd like them to be returned as: BAUT,CHECK_ID,FERA, ...

I am using Oracle Server and my framework doesn't allow me to work with PL/SQL.

Answer 1

XMLAGG supports ordering on its own (see https://docs.oracle.com/database/121/SQLRF/functions251.htm):

SELECT
 rtrim(
 extract(
 xmlagg(
 xmlelement(e, column_name || ',') ORDER BY column_name
 ),
 '/E/text()')
 .getclobval (),
 ',')
FROM
 all_tab_columns
WHERE
 owner = 'TESTER' AND table_name = 'H4_POSIT'

• MMM: Thanks for the alternative.

Answer 2

You can use a subquery and simply sort the columns before you pass it to the xml function. A simple solution.

 select rtrim(extract(xmlagg(xmlelement(e, column_name || ',')),
'/E/text()').getclobval(), ',') from 
(select * from all_tab_columns
 where OWNER != 'TESTER' AND TABLE_NAME=upper('H4_POSIT') 
 order by COLUMN_NAME );