Enhanced LISTAGG Oracle Database 12C release 2 (12.2)

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

Continue reading