Handling errors in LISTAGG

The LISTAGG analytic function, introduced in Oracle 11g Release 2, greatly simplifies string aggregations within SQL queries.

SELECT pid, LISTAGG(ColumnName, ' ' ) WITHIN GROUP (ORDER BY seq) AS ColumnName
FROM B GROUP BY pid;

However, if the output of the above query exceeds 4000 characters, it triggers an error, specifically ORA-01489, indicating that the result of string concatenation is too long.

To address this limitation, Oracle Database Release 2 (12.2) enhanced LISTAGG with the ability to handle overflow errors gracefully, as demonstrated below:

SELECT pid, LISTAGG(ColumnName, ' ' ON OVERFLOW TRUNCATE ) WITHIN GROUP (ORDER BY seq) AS ColumnName
FROM B GROUP BY pid;

In this updated syntax, the output is restricted to 4000 characters, preventing the ORA-01489 error from being raised.

The ON OVERFLOW clause offers several options to manage overflow situations:

• ON OVERFLOW ERROR: This is the default behavior, triggering an error if the result overflows.
• ON OVERFLOW TRUNCATE 'StringYouLike': Appends ‘StringYouLike(Count)’ at the end of the truncated string.
• ON OVERFLOW TRUNCATE '': Displays the first 4000 characters without any additional terminating string.
• ON OVERFLOW TRUNCATE WITH COUNT: Appends the total character count at the end, for example, ‘…(5512)’.

References

Stack Overflow.