Say I have this table:
ID|Col1|Col2|Col3
1|Text that has 4000 chars|Text2 that has 4000 chars|Text3 that has 4000 chars
2|Text4 that has 4000 chars|Text5 that has 4000 chars|Text6 that has 4000 chars
3|Text7 that has 4000 chars|Text8 that has 4000 chars|Text9 that has 4000 chars
I am using listagg like so:
SELECT id,
listagg(col1||col2||col3, ',') within group (order by id)
FROM table;
And I am encountering the error:
ORA-01489: result of string concatenation is too long
Upon researching, I found out using xmlagg could do the trick (link), but then realized the real problem lies in the concatenation of col1, col2 and col3 as it is only limited to 4000 chars, so doing xmlagg would still return the same error.
Has anyone figured this one out yet? Or there's no workaround for this? (link)
Update:
I updated the sample values on the table just to be clear (for Mr. Kumar to understand), and my expected output should be something like:
ID | Agg
1 | Text that has 4000 charsText2 that has 4000 charsText3 that has 4000 chars
2 | Text4 that has 4000 charsText5 that has 4000 charsText6 that has 4000 chars
3 | Text7 that has 4000 charsText8 that has 4000 charsText9 that has 4000 chars
Which apparently doesn't work.
I finally got it to work. What I did was to aggregate the columns before concatenating it, GROUP it as Mr. Kumar suggested and THEN aggregate them again to fix the ordering. Here's how:
WITH agg_tbl AS
(
SELECT id,
rtrim(xmlagg(xmlelement(e,col1,',').extract('//text()').GetClobVal(),',')||rtrim(xmlagg(xmlelement(e,col1,',').extract('//text()').GetClobVal(),',')||rtrim(xmlagg(xmlelement(e,col1,',').extract('//text()').GetClobVal(),',') long_text
FROM table
GROUP BY col1
)
SELECT rtrim(xmlagg(xmlelement(e,long_text,chr(13)).extract('//text()').GetClobVal(),',') agg_value
FROM agg_tbl;
So instead of this:
agg_value
Text that has 4000 charsText4 that has 4000 charsText7 that has 4000 chars
Text2 that has 4000 charsText5 that has 4000 charsText8 that has 4000 chars
Text3 that has 4000 charsText6 that has 4000 charsText9 that has 4000 chars
I am now getting my desired result:
agg_value
Text that has 4000 charsText2 that has 4000 charsText3 that has 4000 chars
Text4 that has 4000 charsText5 that has 4000 charsText6 that has 4000 chars
Text7 that has 4000 charsText8 that has 4000 charsText9 that has 4000 chars