LISTAGG - ON OVERFLOW TRUNCATE - Oracle 12c R2 enhancement

Oracle 11g introduced LISTAGG analytic function to aggregate the result set in multiple rows into one single column. LISTAGG helps us in solving various string aggregation problem like if someone wants to get comma-separated employee names by each department from EMP table he can simply write

NGARG> SELECT DEPTNO, 
  2            LISTAGG(ENAME, ',') WITHIN GROUP (ORDER BY ENAME) ENAME
  3      FROM EMP
  4     GROUP BY DEPTNO;

    DEPTNO EMPLOYEES
---------- --------------------------------------------------
        10 CLARK,KING,MILLER
        20 ADAMS,FORD,JONES,SCOTT,SMITH
        30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

So far so good, but what if the value returned by LISTAGG after string aggregation is too long to fit in the datatype, upto Oracle 12c R1 LISTAGG used to throw ORA-01489. For example if I want to get comma-separated table names in each schema.

I have inserted 500 duplicate rows for each employee in EMP table to replicate this behavior.
NGARG> INSERT INTO EMP
  2    SELECT ROWNUM, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
  3      FROM EMP,
  4           (SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 500);
7000 rows created.

NGARG> COMMIT;
Commit complete.

Now lets execute our previous SQL.
NGARG> SELECT DEPTNO,
  2           LISTAGG(ENAME, ',') WITHIN GROUP (ORDER BY ENAME) ENAME
  3      FROM EMP
  4     GROUP BY DEPTNO;
   FROM EMP
       *
ERROR at line 3:
ORA-01489: result of string concatenation is too long

In Oracle 12c R2, LISTAGG function has been enhanced to manage situations where the length of the concatenated string is too long. In Oracle Database 12c R2 provides us ON OVERFLOW TRUNCATE clause to handle ORA-01489 with LISTAGG.

Syntax for ON OVERFLOW TRUNCATE is
 - LISTAGG(<COLUMN>, '<DELIMITER>' ON OVERFLOW TRUNCATE '<TRUNCATION-INDICATOR>' <WITH/WITHOUT COUNT>) 
          WITHIN GROUP (ORDER BY <COLUMN>)
By default - TRUNCATION-INDICATOR is ellipsis (...) and WITH COUNT is default.

Let me show you the difference by executing following 3 SQLs
1. ON OVERFLOW TRUNCATE default behavior
NGARG> SELECT DEPTNO,
  2         LISTAGG(ENAME, ', ' ON OVERFLOW TRUNCATE)
  3     WITHIN GROUP (ORDER BY ENAME ) ENAME
  4    FROM EMP
  5   WHERE DEPTNO = 10
  6   GROUP BY DEPTNO;

    DEPTNO  ENAME
---------- -----------------------------------------------------------------------------------------------------------
        10 CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, 
           CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, 
           CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK,
           .
           .
           .           
           CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, KING, KING, KING, KING, KING, KING, 
           KING, KING, KING, KING, KING, KING, KING, KING, KING, KING, KING, KING, KING, KING, KING, KING, KING, KING, KING, 
           KING, KING, KING, KING, KING, KING, KING, KING, KING, KING, KING, KING, KING, ...(925)
By default LISTAGG gracefully truncated the extra data and added ellipsis with the count of remaining characters in parenthesis.

2. ON OVERFLOW TRUNCATE with different TRUNCATION-INDICATOR
NGARG> SELECT DEPTNO,
  2         LISTAGG(ENAME, ', ' ON OVERFLOW TRUNCATE)
  3     WITHIN GROUP (ORDER BY ENAME ) ENAME
  4    FROM EMP
  5   WHERE DEPTNO = 10
  6   GROUP BY DEPTNO;

    DEPTNO  ENAME
---------- -----------------------------------------------------------------------------------------------------------
        10 CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, 
           CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, 
           CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK,
           .
           .
           .           
           CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, KING, KING, KING, KING, KING, KING, 
           KING, KING, KING, KING, KING, KING, KING, KING, KING, KING, KING, KING, KING, KING, KING, KING, KING, KING, KING, 
           KING, KING, KING, KING, KING, KING, KING, KING, KING, KING, KING, KING, KING, ###(925)
Here LISTAGG truncated the extra data and added ### instead of ellipsis and the the count of remaining characters in parenthesis.

3. ON OVERFLOW TRUNCATE with WITHOUT COUNT
NGARG>  SELECT DEPTNO,
  2         LISTAGG(ENAME, ',' ON OVERFLOW TRUNCATE '...' WITHOUT COUNT)
  3     WITHIN GROUP (ORDER BY ENAME ) ENAME
  4    FROM EMP
  5    WHERE DEPTNO = 10
  6   GROUP BY DEPTNO;
    DEPTNO  ENAME
---------- -----------------------------------------------------------------------------------------------------------
        10 CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, 
           CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, 
           CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK,
           .
           .
           .           
           CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, CLARK, KING, KING, KING, KING, KING, KING, 
           KING, KING, KING, KING, KING, KING, KING, KING, KING, KING, KING, KING, KING, KING, KING, KING, KING, KING, KING, 
           KING, KING, KING, KING, KING, KING, KING, KING, KING, KING, KING, KING, KING, ...
As expected, here LISTAGG truncated the extra data and added ellipsis at end but count of remaining characters is not shown.

Just one last thing I wanted to add, the default behavior of LISTAGG is ON OVERFLOW ERROR, which you can add as clause, which means
  -  LISTAGG(ENAME, ',') WITHIN GROUP (ORDER BY ENAME) ENAME
will behave exactly same as
  -  LISTAGG(ENAME, ',' ON OVERFLOW ERROR ) WITHIN GROUP (ORDER BY ENAME) ENAME


Please do post your thoughts/comments about this new enhancements and the post. Enjoy the SQL :)


Related Posts:
- Top 15 new features of Oracle Database 12.2 for developers
- Oracle: New String Aggregation Techniques
- Oracle: Difference between wm_concat and ListAgg
- New Features for Developers in Oracle 11g
- Transpose Comma Separated Values in Table to Rows
- Get Nth Record from Comma Separated String
- Department wise Employee Ids Comma Separated using Analytical Functions
- SQL Interview Question Answers

5 comments:

  1. That's awesome improvement! Thanks a lot Nimish.

    ReplyDelete
  2. Nice. Thanks a lot. Can please share me the real time example of collection concept and its types. It will me a lot. This is my email ID: mpanneerselvam1105@gmail.com

    ReplyDelete
  3. I have couple of questions.
    1) What is the length of your result set? I mean the grouped column data length?
    2) what if the length of the result set is > 32767 characters? Is there a solution to fix this? if yes, how?

    ReplyDelete
  4. Very Helpful! Assuming code in example 2 needs adjustment? Looks the same as 1.

    ReplyDelete