That happens when we want to aggregate/concatenate text fields and the summary length exceeds the 4000 limit.
There are various scenarios what do you want to achieve:
cut the result to 4000 characters
return as a CLOB
more complex tasks such as cut particular long parts of the text.
Following solution solves the first case, i.e. return first 4000 characters in the case of exceeding.
Original statement:
Select
L1.GroupID
,listagg(L1.PartOfTheText,',')within group(order by L1.TextOrderNR)
from SourceTable L1
group by L1.GroupID;
Solution:
With L1 as(
SELECT
SRC.GroupID
,SRC.NoteTypeID
,SRC.TextOrderNR
,sum(length(SRC.PartOfTheText)+1)over(partition by SRC.GroupID order by SRC.TextOrderNR,SRC.rowid) as LENNY
FROM SourceTable SRC
)
Select
L1.GroupID
,listagg(L1.PartOfTheText,',')within group(order by L1.TextOrderNR)
from L1
where LENNY<=4001
group by L1.GroupID
;
Explanation:
We eliminate parts of text exceeding 4000 characters totally using analytical function of length.
We add rowid into the order by clause to assure cummulativeness in the case of TextOrderNR equality.
We add 1 into each piece length to conseder delimiters length.
We use 4001 as a limit because last delimiter won't appear.
Solution to use substring of exceeding part too:
With L1 as(
SELECT
SRC.GroupID
,SRC.NoteTypeID
,SRC.TextOrderNR
,sum(length(SRC.PartOfTheText)+1)over(partition by SRC.GroupID order by SRC.TextOrderNR,SRC.rowid)-(length(SRC.PartOfTheText)+1) as LENNY
FROM SourceTable SRC
)
Select
L1.GroupID
,listagg(substr(L1.PartOfTheText,1,4000-LENNY),',')within group(order by L1.TextOrderNR)
from L1
where LENNY<4000
group by L1.GroupID
;
Explanation:
We trim the part of text exceeding 4000 characters using analytical function of length but we subtract length of the current piece.
We use less than 4000 as a limit to assure there would be at least one character from the last trimmed piece.