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'

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 );