Oracle SQL: Alternative to aggregate large texts (when exceeding Listagg limit)

Question

I have a simple Select query that aggregates one column containing large texts. The following worked for me with small texts but I am now exceeding the Listagg character limit (4000 bytes ?).

I am very new to Oracle and couldn't find a proper solution for this online that I could apply here.

Can someone tell me the best alternative to this ?

My Query (simplified):

SELECT
    m.S_ID AS SID
    , LISTAGG
    (
        'ITEM NO.: ' || m.ITEM || 
        ' -nl-ARTICLE: ' || a.ARTICLE || 
        ' -nl-NET: ' || m.NET || 
        ' -nl-TAX: ' || NVL(m.TAX, 0) || 
        ' -nl-GROSS: ' || (m.NET + m.TAX),
        ' -nl--nl-'
    ) WITHIN GROUP (ORDER BY m.S_ID) AS Details
    /* ... */
FROM 
    myTable m
/* ... */

Many thanks for any help with this,
Mike

Answer 1

One of possible method.

select xmlagg(xmlelement(xxx,'ITEM NO.: ' || m.ITEM || 
        ' -nl-ARTICLE: ' || a.ARTICLE || 
        ' -nl-NET: ' || m.NET || 
        ' -nl-TAX: ' || NVL(m.TAX, 0) || 
        ' -nl-GROSS: ' || (m.NET + m.TAX),
        ' -nl--nl-'||',<-separator').extract('//text()') order m.S_ID).getClobval() from mytable
group by ...

2nd method. oracle allows to creat own aggregation function user defined aggregation function