How to handle exception in listagg() function

Question

I am using listagg in my script

listagg(' |' || aktiv.AKTIVITÄT_NR || ' |' || aktiv.AKTIVITÄT_KÜRZEL  || ' |' || aktiv.AKTIVITÄT_BESCHREIBUNG || ' |' || aktiv.AKTIVITÄT_ERWARTETES_ERGEBNIS|| ' |' ||  CHR(10)) within group (order by aktiv.AKTIVITÄT_NR)) as activity 

When listagg exceed 4000 bytes all script fail. How can I handle exception and for this record insert e.x. NULL and go to the next record without fail.

Answer 1

Sorry, I use my own example to show the idea, because I simply have no German layout and your tables as well:

with src as (/* overflow */
             select 1 id, level lv
             from dual
             connect by level <= 10000
             union all
             /* fitting */
             select 2, level lv
             from dual
             connect by level <= 10
             union all
             select 3, level lv
             from dual
             connect by level <= 5)
select listagg(case when length_ <= 4000 then lv end,',') within group (order by lv)
from (select id,lv,sum(length(lv) + 1) over (partition by id) - 1 length_ from src)
group by id

The idea:

1. src is your table, id is the value you are grouping by, level is your value
2. this subquery select id,lv,sum(length(lv) + 1) over (partition by id) - 1 length_ from src gathers the future length of listagg result, + 1 is made for delimeter ',', - 1 is done for the last delimeter which is not used
3. expression listagg(case when length_ <= 4000 then lv end,',') within group (order by lv) checks if the length is less than allowed value (4000), if it is overflown it returns null

I hope this solves your problem.

Answer 2

I think you should count SUM of LENGTH of strings for each group. And then use CASE to handle if this sum length > 4000. In the following query I join original table and a table with SUM(LENGTH) for each group. Try this:

select t.id,
       CASE WHEN (TL.SumLen<=4000)
              THEN LISTAGG(t.Str,',') 
                   WITHIN GROUP (ORDER BY Str)
                          OVER (PARTITION BY t.ID)
            ELSE NULL
       END
FROM t 
JOIN 
(
  SELECT Id, SUM(LENGTH(str||',')) SumLen
         FROM t 
         GROUP BY ID
 ) TL on T.id=TL.id

SQLFiddle demo