8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
LISTAGG Function Enhancements in Oracle Database 12c Release 2 (12.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.
Related articles.
- LISTAGG Analytic Function
- LISTAGG Analytic Function in 11g Release 2
- LISTAGG DISTINCT in Oracle Database 19c
- Analytic Functions : All Articles
Setup
The examples in this article rely on the following test table.
--DROP TABLE emp PURGE; CREATE TABLE emp ( empno NUMBER(4) CONSTRAINT pk_emp PRIMARY KEY, ename VARCHAR2(10), job VARCHAR2(9), mgr NUMBER(4), hiredate DATE, sal NUMBER(7,2), comm NUMBER(7,2), deptno NUMBER(2) ); INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20); INSERT INTO emp VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30); INSERT INTO emp VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30); INSERT INTO emp VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20); INSERT INTO emp VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30); INSERT INTO emp VALUES (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30); INSERT INTO emp VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10); INSERT INTO emp VALUES (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,NULL,20); INSERT INTO emp VALUES (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10); INSERT INTO emp VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30); INSERT INTO emp VALUES (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,NULL,20); INSERT INTO emp VALUES (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30); INSERT INTO emp VALUES (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20); INSERT INTO emp VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),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,ALLEN,ALLEN,ALLE N,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,AL LEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN, [removed] N,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,AL LEN,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,ALLEN,ALLE N,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,AL LEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN, [removed] N,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,AL LEN,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,ALLEN,ALLE N,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,AL LEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN, [removed] N,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,AL LEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,... SQL>
Quick Links
The "*" indicates the function supports the full analytic syntax, including the windowing clause.
For more information see:
- LISTAGG Function
- LISTAGG Analytic Function
- LISTAGG Analytic Function in 11g Release 2
- LISTAGG DISTINCT in Oracle Database 19c
- Analytic Functions : All Articles
Hope this helps. Regards Tim...