-- Oracle 12c: Enhancements of LISTAGG Function
-- The query below will result in error (ORA-01489) if the size object list is greater than 
-- 4000 characters (or even 32767).

-- 11g Syntax (will work if object_list size is less than 4000 characters)

SELECT
 object_type,
 LISTAGG(object_name,'; ')
   WITHIN GROUP (ORDER BY object_id DESC) 
 object_list,
 COUNT(*) object_count
FROM all_objects
GROUP BY (object_type);

-- Oracle 12c Release 2 (12.2) syntax
-- ON OVERFLOW TRUNCATE: will result in output shown as ellipsis with count (default)
-- i.e. => ... (count) showing the overflow
-- The output can also be customized if necessary such as 
-- ON OVERFLOW TRUNCATE ',;,;'

SELECT
 object_type,
 LISTAGG(object_name,'; ' ON OVERFLOW TRUNCATE)
   WITHIN GROUP (ORDER BY object_id DESC) 
 object_list,
 COUNT(*) object_count
FROM all_objects
GROUP BY (object_type);

-- Show error when overflow occurs (greater than 4000 or 32727)

SELECT
 object_type,
 LISTAGG(object_name,'; ' ON OVERFLOW ERROR)
   WITHIN GROUP (ORDER BY object_id DESC) 
 object_list,
 COUNT(*) object_count
FROM all_objects
GROUP BY (object_type);

-- Other options
-- ON OVERFLOW TRUNCATE '...' WITH COUNT
-- This syntax will show how many were excluded using "WITH COUNT"
-- and "WITHOUT COUNT" will just show "..."

SELECT
 object_type,
 LISTAGG(object_name,'; ' ON OVERFLOW TRUNCATE '...' WITH COUNT) 
   WITHIN GROUP (ORDER BY object_id DESC) 
 object_list,
 COUNT(*) object_count
FROM all_objects
GROUP BY (object_type);