Clarity PPM query with ListAGG throws ORA-01489 when over 4000 characters
search cancel

Clarity PPM query with ListAGG throws ORA-01489 when over 4000 characters

book

Article ID: 193308

calendar_today

Updated On: 04-30-2024

Products

Clarity PPM On Premise Clarity PPM SaaS

Issue/Introduction

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

An error is thrown:

ORA-01489: result of string concatenation is too long 

Environment

Release : All Supported Clarity Versions

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.

Resolution

Workaround:

  • Use XMLAGG to aggregate the values instead of LISTAGG.

Additional Information