Oracle 12c R2 - LISTAGG y OVERFLOW

En Oracle 11gR2 se introdujo una nueva función analítica, LISTAGG, la cual ordena los datos dentro de cada grupo especificado en la cláusula ORDER BY y luego concatena los valores de la columna o expresión seleccionados.

Por ejemplo, si queremos obtener una lista de las tablas a las que tenemos acceso, junto con una lista de todas las columnas, ordenadas por nombre de columna, de cada una de tablas, podemos ejecutar la siguiente consulta:

SELECT table_name,
       LISTAGG(column_name, ',') WITHIN GROUP 
                  (ORDER BY column_name) Columns
FROM all_tab_cols
GROUP BY table_name;

Lo cual produce el siguiente resultado:

Pero el problema surge cuando la lista concatenada de valores supera el tamaño máximo que el lenguaje SQL soporta en Oracle, el cual es 4000 bytes. En el ejemplo, siguiente, repetimos 50 veces los datos para aumentar el tamaño del resultado de LISTAGG, obteniendo el siguiente error:

SELECT table_name,
       LISTAGG(column_name, ',') WITHIN GROUP 
                  (ORDER BY column_name) Columns
FROM all_tab_cols
    CROSS JOIN (SELECT level FROM dual CONNECT BY level <=50)
GROUP BY table_name;

ORA-01489: result of string concatenation is too long
01489. 00000 -  "result of string concatenation is too long"
*Cause:    String concatenation result is more than the maximum size.
*Action:   Make sure that the result is less than the maximum size

Oracle 12cR2 introduce una sintaxis ampliada para la función LISTAGG, la cual nos permite obviar el error, mostrando sólo los primeros 4000 bytes que la función obtiene, sin producir un error, como podemos ver a continuación (aquí "cortamos' la cadena concatenada en dos pedazos para poder ver el principio y fin de la misma):

SELECT x.table_name,
       SUBSTR(LISTAGG(x.column_name, ',' ON OVERFLOW TRUNCATE) 
            WITHIN GROUP (ORDER BY x.column_name),1,30) AS "Comienza con...",
       SUBSTR(LISTAGG(x.column_name, ',' ON OVERFLOW TRUNCATE)
            WITHIN GROUP (ORDER BY x.column_name),-30) AS "Termina con..."
FROM all_tab_cols x
    CROSS JOIN (SELECT level FROM dual CONNECT BY level <=50)
GROUP BY x.table_name;

Como vimos en el ejemplo anterior, la opción "ON OVERFLOW TRUNCATE" por defecto corta el resultado hasta el valor anterior cuyo tamaño no exceda los 4000 bytes, agrega "..." a continuación del último valor que puede ser mostrado, y luego muestra entre paréntesis la cantidad de caracteres que fueron truncados.

Podemos reemplazar los puntos suspensivos con cualquier cadena que deseemos utilizar como indicador para informar que el texto ha sido truncado, de la siguiente forma:

ON OVERFLOW TRUNCATE '///'

Tambien podemos indicarle que no deseamos saber el numero de caracteres truncados, de la siguiente forma:

ON OVERFLOW TRUNCATE WITHOUT COUNT

La sintaxis completa de la función LISTAGG en oracle 12c Release 2 puede encontrarse aqui.