Oracle 12C New Feature : Listagg function overflow improved

1. If the query exceeds 4000 char, oracle will throw error to caller.

SELECT
    LISTAGG(table_name,',')
        WITHIN GROUP(ORDER BY table_name)
FROM
    all_tables;

2. "On Overflow Truncate" option will truncate the char exceeds 4000 char, and suffix "..." & count of fields that were truncated.
   
SELECT
    LISTAGG(table_name,',' ON OVERFLOW TRUNCATE)
        WITHIN GROUP(ORDER BY table_name)
FROM
    all_tables;
 
3. "On Overflow Error" will throw the error in case of the concatenated string exceeds 4000 char

SELECT
    LISTAGG(table_name,',' ON OVERFLOW ERROR)
        WITHIN GROUP(ORDER BY table_name)
FROM
    all_tables;

4. Optionally, default message for overflow can be embedded as the end of result. ( example given below)

SELECT
    LISTAGG(table_name,',' ON OVERFLOW TRUNCATE '*** MORE DATA EXISTS ***' )
        WITHIN GROUP(ORDER BY table_name)
FROM
    all_tables;

5. The count suffix can be explicitly specified with "WITH COUNT" option. ( Through this is the default)

SELECT
    LISTAGG(table_name,',' ON OVERFLOW TRUNCATE '*** MORE DATA EXISTS ***' WITH COUNT )
        WITHIN GROUP(ORDER BY table_name)
FROM
    all_tables;

6. "Without count" will not suffix the count at the end of the string.

SELECT
    LISTAGG(table_name,',' ON OVERFLOW TRUNCATE '*** MORE DATA EXISTS ***' WITHOUT COUNT )
        WITHIN GROUP(ORDER BY table_name)
FROM
    all_tables;


Here is the video that talks about the feature usage.