In previous releases there was always problem with function LISTAGG. If list of concatenated expression exceeds maximum length supported by VARCHAR2 type, then ORA-01489 is raised.

In new version Oracle Database Release 12.2 addressed this issue by adding extra clause ON OVERFLOW TRUNCATE to hide this error. In my opinion it’s not full solution but in some cases can be useful.

LISTAGG ( [ALL] <measure_column> [,<delimiter>] 
 [ON OVERFLOW TRUNCATE [truncate_literal] [WITH | WITHOUT COUNT] | 
  ON OVERFLOW ERROR]) 
WITHIN GROUP (ORDER BY <oby_expression_list>)

I recommend to read following article as well:

String concatenation aggregation methods

So in previous releases following statement returns error ORA-01489 if concatenated string is too big

SELECT LISTAGG(table_name, ',') WITHIN GROUP(ORDER BY table_name)
  FROM dba_tables 
 WHERE owner='SYS';

ORA-01489: result of string concatenation is too long

Now you can use following options:

• truncate the string with ON OVERFLOW TRUNCATE
  

SELECT LISTAGG(table_name, ',' ON OVERFLOW TRUNCATE) 
WITHIN GROUP(ORDER BY table_name) my_data
  FROM dba_tables 
 WHERE owner='SYS';

MY_DATA                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
-----------------------------
ACCESS$,ACLMV$,ACLMV$_REFLOG,
...
...
DBFS_HS$_CONTENTFNMAPTBL,DBFS_HS$_FS,...(1260)

• truncate the string and add extra own comment in the end ON OVERFLOW TRUNCATE ‘truncate-indicator’

 SELECT LISTAGG(table_name, ',' ON OVERFLOW TRUNCATE 'my comment') 
WITHIN GROUP(ORDER BY table_name) my_data
  FROM dba_tables 
 WHERE owner='SYS';

MY_DATA                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
-----------------------------
ACCESS$,ACLMV$,ACLMV$_REFLOG,
...
...
DBFS_HS$_CONTENTFNMAPTBL,DBFS_HS$_FS,my comment(1260)

Extra options for ON TRUNCATE OVERFLOW

• WITH COUNT indicates number of truncated values. The number is added in the end of returned string – DEFAULT value
• WITHOUT COUNT omits number of truncated values from the returned string

 SELECT LISTAGG(table_name, ',' 
                ON OVERFLOW TRUNCATE 'my comment' WITHOUT COUNT) 
WITHIN GROUP(ORDER BY table_name) my_data
  FROM dba_tables 
 WHERE owner='SYS';

MY_DATA                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
-----------------------------
ACCESS$,ACLMV$,ACLMV$_REFLOG,
...
...
DBFS_HS$_CONTENTFNMAPTBL,DBFS_HS$_FS,my comment

• ON OVERFLOW ERROR for this option it works as in previous releases and ORA-01489 is raised

SELECT LISTAGG(table_name, ',' ON OVERFLOW ERROR) 
WITHIN GROUP(ORDER BY table_name) 
  FROM dba_tables 
 WHERE owner='SYS';

ORA-01489: result of string concatenation is too long

Have a fun 🙂

Tomasz