For NVARCHAR2
and VARCHAR2
maximum size is 4000 bytes, or 32767 bytes if the MAX_STRING_SIZE
initialization parameter is set to EXTENDED
. This is useful you have to allocate more data to a variable.
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)’.
What are your thoughts on this post?
I’d love to hear from you! Click this link to email me—I reply to every message!
Also use the share button below if you liked this post. It makes me smile, when I see it.