Wednesday, 14 June 2017

LISTAGG Function Enhancements in Oracle Database 12c Release 2

The LISTAGG analytic function was introduced in Oracle 11g Release 2, making it very easy to perform string aggregations. The LISTAGG function has been enhanced in Oracle Database Release 2 (12.2), allowing it to handle overflow errors gracefully.

  • Setup


The examples in this article rely on the following test table.

CREATE TABLE EMP (
  EMPNO NUMBER(4,0), 
  ENAME VARCHAR2(10 BYTE), 
  JOB VARCHAR2(9 BYTE), 
  MGR NUMBER(4,0), 
  HIREDATE DATE, 
  SAL NUMBER(7,2), 
  COMM NUMBER(7,2), 
  DEPTNO NUMBER(2,0), 
  CONSTRAINT PK_EMP PRIMARY KEY (EMPNO)
  );
  
insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7369,'SMITH','CLERK',7902,to_date('17-DEC-80','DD-MON-RR'),800,null,20);
insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7499,'ALLEN','SALESMAN',7698,to_date('20-FEB-81','DD-MON-RR'),1600,300,30);
insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7521,'WARD','SALESMAN',7698,to_date('22-FEB-81','DD-MON-RR'),1250,500,30);
insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7566,'JONES','MANAGER',7839,to_date('02-APR-81','DD-MON-RR'),2975,null,20);
insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7654,'MARTIN','SALESMAN',7698,to_date('28-SEP-81','DD-MON-RR'),1250,1400,30);
insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7698,'BLAKE','MANAGER',7839,to_date('01-MAY-81','DD-MON-RR'),2850,null,30);
insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7782,'CLARK','MANAGER',7839,to_date('09-JUN-81','DD-MON-RR'),2450,null,10);
insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7788,'SCOTT','ANALYST',7566,to_date('19-APR-87','DD-MON-RR'),3000,null,20);
insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7839,'KING','PRESIDENT',null,to_date('17-NOV-81','DD-MON-RR'),5000,null,10);
insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7844,'TURNER','SALESMAN',7698,to_date('08-SEP-81','DD-MON-RR'),1500,0,30);
insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7876,'ADAMS','CLERK',7788,to_date('23-MAY-87','DD-MON-RR'),1100,null,20);
insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7900,'JAMES','CLERK',7698,to_date('03-DEC-81','DD-MON-RR'),950,null,30);
insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7902,'FORD','ANALYST',7566,to_date('03-DEC-81','DD-MON-RR'),3000,null,20);
insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7934,'MILLER','CLERK',7782,to_date('23-JAN-82','DD-MON-RR'),1300,null,10);
commit;

  • Default LISTAGG Functionality


Here we see a simple example of the LISTAGG function, producing a comma-separated list of employees for each department.

COLUMN employees FORMAT A40
SELECT deptno, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees
FROM   emp
GROUP BY deptno
ORDER BY deptno;

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

SQL>

If the concatenation results in a string longer than the return data type of the LISTAGG function an "ORA-01489" error is produced. In the following example we use a CROSS JOIN to force a large aggregation.

COLUMN employees FORMAT A40
SELECT deptno, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees
FROM   emp
       CROSS JOIN (SELECT level FROM dual CONNECT BY level <= 1000)
WHERE  deptno = 30
GROUP BY deptno
ORDER BY deptno;

FROM   emp
       *
ERROR at line 2:
ORA-01489: result of string concatenation is too long

This default behaviour is functionally equivalent to explicitly specifying the ON OVERFLOW ERROR overflow clause.

COLUMN employees FORMAT A40
SELECT deptno, LISTAGG(ename, ',' ON OVERFLOW ERROR) WITHIN GROUP (ORDER BY ename) AS employees
FROM   emp
       CROSS JOIN (SELECT level FROM dual CONNECT BY level <= 1000)
WHERE  deptno = 30
GROUP BY deptno
ORDER BY deptno;

FROM   emp
       *
ERROR at line 2:
ORA-01489: result of string concatenation is too long

SQL>

  • Handling Overflow Errors

In Oracle Database 12c Release 2 we can add the ON OVERFLOW TRUNCATE clause to handle overflow errors gracefully. By default the truncate literal is an ellipsis ('...') and a count of the overflow characters is included.

COLUMN employees FORMAT A70

SELECT deptno, LISTAGG(ename, ',' ON OVERFLOW TRUNCATE) WITHIN GROUP (ORDER BY ename) AS employees
FROM   emp
       CROSS JOIN (SELECT level FROM dual CONNECT BY level <= 1000)
WHERE  deptno = 30
GROUP BY deptno
ORDER BY deptno;

 DEPTNO EMPLOYEES
---------- ----------------------------------------------------------------------
     30      ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN

[removed]
         
               N,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,...(5339)

SQL>

We can specify our own truncate literal if we don't want to use an ellipsis. In the following example we are using a truncate literal of '~~~'.

COLUMN employees FORMAT A70

SELECT deptno, LISTAGG(ename, ',' ON OVERFLOW TRUNCATE '~~~') WITHIN GROUP (ORDER BY ename) AS employees
FROM   emp
       CROSS JOIN (SELECT level FROM dual CONNECT BY level <= 1000)
WHERE  deptno = 30
GROUP BY deptno
ORDER BY deptno;

 DEPTNO   EMPLOYEES
---------- ----------------------------------------------------------------------
   30         ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,

[removed]

                N,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,~~~(5339)

SQL>

We can also omit the count by adding WITHOUT COUNT. The default being the equivalent of explicitly using WITH COUNT.

COLUMN employees FORMAT A70

SELECT deptno, LISTAGG(ename, ',' ON OVERFLOW TRUNCATE '...' WITHOUT COUNT) WITHIN GROUP (ORDER BY ename) AS employees
FROM   emp
       CROSS JOIN (SELECT level FROM dual CONNECT BY level <= 1000)
WHERE  deptno = 30
GROUP BY deptno
ORDER BY deptno;

 DEPTNO   EMPLOYEES
----------  ----------------------------------------------------------------------
    30        ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,

[removed]
       
                N,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,...

SQL>