"ORA-01489:result of string concatenation is too long" (string is small)
ronnie-mApr 12 2013 — edited Apr 12 2013Hi,
I am trying to use the listagg function as follows, but am getting ORA-01489: result of string concatenation is too long.
SELECT LOCATIONID, LISTAGG(TO_CHAR(XPOSITION||','||YPOSITION), ',') WITHIN GROUP (ORDER BY SEQUENCENUMBER) ords
FROM POSITIONPOINTS
GROUP BY LOCATIONID
HAVING COUNT(SEQUENCENUMBER) = 20;
When I try running this in Oracle Sql Developer, it displays the first 1550 rows, then reports the ORA-01489 error. In total 2612 rows should be returned, all ords values having a length of approximately 440 characters. An example of one of the rows that Sql Developer is returning is:
22372682 410434.801,551142.885,410434.784,551142.875,410439.801,551141.922,410439.991,551141.795,410439.293,551138.303,410438.531,551137.668,410429.768,551134.302,410427.228,551133.159,410426.212,551132.143,410425.196,551129.667,410421.957,551114.3,410414.972,551081.28,410413.639,551076.136,410412.94,551073.66,410412.94,551072.326,410413.639,551071.628,410415.798,551070.612,410416.369,551069.469,410416.877,551068.834,410433.23,551061.795
There are some LocationIDs in the PositionPoints table which have more than 20 entries (max is 254), and for these rows I would expect the concatenated string to be more than the max of 4000 characters. However, where count(sequencenumber) = 20, the concatenated string length would be less than 500. Is Oracle performing concatenations even for the locations I have excluded with my HAVING clause, and reporting an error on these?
I have tried running the query from both Oracle Sql Developer and SQL Plus.
Would be grateful if anyone could shed any light on this issue.
Thanks