CODEWITHSUNDEEP
sqloracle-database
Ronnie
Ronnie

Reputation: 1079

"ORA-01489:result of string concatenation is too long" (string is small)

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

Upvotes: 1

Views: 6641

Answers (2)

user330315
user330315

Reputation:

You will need to first reduce the number of rows that you are aggregating (as you guessed), and then apply the listagg() on the reduced number.

Something like this:

select locationid, 
       listagg(to_char(xposition||','||yposition), ',') within group (order by sequencenumber) ords
from (
  select locationid, 
         xposition, 
         yposition, 
         sequencenumber,
         count(sequencenumber) over (partition by locationid) as cnt
  from positionpoints 
) t
where cnt = 20
group by locationid;

Upvotes: 2

Egor Skriptunoff
Egor Skriptunoff

Reputation: 23737

Is Oracle performing concatenations even for the locations I have excluded with my HAVING clause, and reporting an error on these?

Yes.

Upvotes: 1

Related Questions