Tags

, , ,

As in earlier versions LISTAGG still returns a VARCHAR2 datatype with the known limitations (4 or 32 K). But instead of ending in an error on a size overflow we now have the chance to tell the function to truncate the return string in case it gets to long.

The syntax has changed slightly between the beta version of ORACLE 12.2 and the productive one, there some changes had to be done to this post.


LISTAGG default behaviour

The default behaviour is still the same resulting in an Oracle error on size overflow.

WITH test (id, str) as (SELECT MOD(rownum,7), RPAD('Text',10,'X') 
 FROM dual
 CONNECT BY ROWNUM < 10000)
SELECT id, LISTAGG(str,'-') WITHIN GROUP(ORDER BY 1) AS string_created
 FROM test 
 GROUP BY id
/ 

ORA-01489: result of string concatenation is too long

ON OVERFLOW TRUNCATE

The new option on overflow truncated leeds to a trunctated string having three dots at the end followed by the number of truncated characters in brackets.

The result lists only the end of the strings created.

WITH test (id, str) as (SELECT MOD(rownum,7), RPAD('Text',10,'X')
 FROM dual
 CONNECT BY ROWNUM < 10000)
SELECT id
     , LISTAGG(str,'-' ON OVERFLOW TRUNCATE) WITHIN GROUP(ORDER BY 1) AS string_created
 FROM test 
 GROUP BY id
/ 

ID STRING_CREATED
-- ------------------------------------------
 0 TextXXXXXX-TextXXXXXX-TextXXXXXX-...(1068)
 1 TextXXXXXX-TextXXXXXX-TextXXXXXX-...(1069)
 2 TextXXXXXX-TextXXXXXX-TextXXXXXX-...(1069)

Define overflow characters

We are free to define the text that should be shown as the overflow indicator. The text we want to see in case of an overflow is defined following the “ON OVERFLOW TRUNCATE” option.

WITH test (id, str) as (SELECT MOD(rownum,7), RPAD('Text',10,'X')
 FROM dual
 CONNECT BY ROWNUM < 10000)
SELECT id
     , LISTAGG(str,'-' ON OVERFLOW TRUNCATE ' [more] ') WITHIN GROUP(ORDER BY 1) AS string_created
 FROM test 
 GROUP BY id
/ 

ID STRING_CREATED
-- -----------------------------------------------
 0 TextXXXXXX-TextXXXXXX-TextXXXXXX- [more] (1068)
 1 TextXXXXXX-TextXXXXXX-TextXXXXXX- [more] (1069)
 2 TextXXXXXX-TextXXXXXX-TextXXXXXX- [more] (1069)

Omit the counting

The number of truncated characters can be omitted by adding WITHOUT COUNT (default is with count).

WITH test (id, str) as (SELECT MOD(rownum,7), RPAD('Text',10,'X')
 FROM dual
 CONNECT BY ROWNUM < 10000)
SELECT id
     , LISTAGG(str,'-' ON OVERFLOW TRUNCATE ' [more] ' WITHOUT COUNT) WITHIN GROUP(ORDER BY 1) AS string_created
 FROM test 
 GROUP BY id
/ 

ID STRING_CREATED
-- -----------------------------------------------
 0 TextXXXXXX-TextXXXXXX-TextXXXXXX- [more] 
 1 TextXXXXXX-TextXXXXXX-TextXXXXXX- [more] 
 2 TextXXXXXX-TextXXXXXX-TextXXXXXX- [more] 

Conclusion

Truncating the result of a LISTAGG function to avoid the overflow error is a pretty cool feature of Oracle 12.2 SQL. If you need to concatenate strings beyond the size limit of the VARCHAR2 datatype you will still need to write your own CLOB aggregate function but if it is enough to know, that there was more in the string before having concatenated it the on overflow truncate is really handy.