Clarity : ORA-01489: result of string concatenation is too long when query uses ListAGG

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

We have a query using List agg to aggregate project names. This query is running fine in SQL Developer and not running when deployed as a query in Clarity PPM.

Cause

This is Caused because of the length of Aggregated values in Listagg. By default the maximum length is 4000 characters and if it exceeds this we face this issue.

Environment

Release : 15.x

Component : CA PPM STUDIO

Resolution

Use XMLAGG to aggregate the values instead of LISTAGG.

 

https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions215.htm