LISTAGG Enhancements in Oracle

In this article, we will use the optimizations that come with Oracle 12c Release 2 for the LISTAGG function that we use frequently in the code that we have developed.

The LISTAGG function gathers the values in a column of data in a single row according to the grouping criteria given in the SQL statement. Now let's see how it works in the current situation.

Let's get the following data:

SELECT MOD (LEVEL, 4) grp, 
       LEVEL 
       || '0000'      AS col 
FROM   dual 
CONNECT BY LEVEL < 10; 

Let's now group the data we created above according to the GRP column and write the values in COL column side by side. (We'll use LISTAGG to do this.)

SELECT grp, 
       Listagg (col, '; ') 
         within GROUP (ORDER BY col DESC) col 
FROM   (SELECT MOD (LEVEL, 4) grp, 
               LEVEL 
               || '0000'      AS col 
        FROM   dual 
        CONNECT BY LEVEL < 10) 
GROUP  BY grp; 

Yes, we can easily accomplish this with the LISTAGG function. Now let's enlarge our sample data set a bit and examine the result.

SELECT   grp, 
         Listagg (col, '; ') within GROUP (ORDER BY col DESC) col 
FROM     ( 
                SELECT mod (level, 4) grp, 
                       level 
                              || '0000' AS col 
                FROM   dual connect BY level < 10000) 
GROUP BY grp;ORA-01489: result OF string concatenation IS too long

When the data size grows, the merge process has to be done on a large dataset. Thus, we got the above error and could not continue our process. The LISTAGG enhancement that comes with Oracle 12c R2 is a solution to these problems and prevents us from getting these errors.

Now let's run the same query by adding the ON OVERFLOW TRUNCATE statement that comes with 12c R2.

SELECT   grp, 
         listagg (col, '; ' on overflow TRUNCATE) within GROUP (ORDER BY col DESC) col 
FROM     ( 
                SELECT mod (level, 4) grp, 
                       level 
                              || '0000' AS col 
                FROM   dual connect BY level < 10000) 
GROUP BY grp;

Our question was successfully concluded without error. In the concatenation result, for the overflowing part, and the number of total characters truncated to the end was written. Let's continue to look at the different options that come with the enhancement now.

SELECT   grp, 
         listagg (col, '; ' on overflow TRUNCATE 'TAŞMA') within GROUP (ORDER BY col DESC) col 
FROM     ( 
                SELECT mod (level, 4) grp, 
                       level 
                              || '0000' AS col 
                FROM   dual connect BY level < 10000) 
GROUP BY grp;

In the above example, after the statement ON OVERFLOW TRUNCATE, we specify which part of the overflow should be written and execute the query. At the end of the query, we see that the word we have specified is at the very end.

Another use option is to prevent the truncated character count from being written at the end. We use the phrase WITHOUT COUNT in it.

SELECT   grp, 
         listagg (col, '; ' on overflow TRUNCATE 'TAŞMA' without count) within GROUP (ORDER BY col DESC) col
FROM     ( 
                SELECT mod (level, 4) grp, 
                       level 
                              || '0000' AS col 
                FROM   dual connect BY level < 10000) 
GROUP BY grp;

The result is displayed without specifying the number of characters to be interrupted as shown.

As a result, if we do not wish to encounter overflow errors in situations where LISTAGG is used, it will be very useful for us to use the new options.