Managing very long lists in 12.2 with LISTAGG

Module 1

Introduction

The LISTAGG function was introduced in Oracle RDBMS 11gR2 and based on the number of posts across various forums and blogs, it is widely used by developers.

When using LISTAGG on very large data sets it is possible to create a list that is too long and this causes the following overflow error to be generated:

ORA-01489: result of string concatenation is too long.

The primary objective of this tutorial is to explain the changes that have been made to LISTAGG so that developers, within application and SQL code, can control the process by which overflows (ORA-01489 errors) are managed.

Module 2

Creating employee table

First step is to create our employee table which we will use to demonstrate the new LISTAGG syntax.

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)
);

Module 3

Add employee data

Next step is to add data to our employee table

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;

Module 4

Building a long list of values

To demonstrate the key new features of the 12.2 LISTAGG syntax we are going to use the SQL MODEL clause, which was introduced in Oracle 10g, to build a new column containing a very long string - essentially we are going to repeatedly concatenate the employee name.

You can see that we have created a new column within our resultset called "longname" and this is based on the data from the ename column within our employee table. Note that we have defined the datatype of this new column to use the new 32K varchar2 feature.

To simplify our example let's convert the above example into a view

CREATE OR REPLACE VIEW empln AS
(SELECT * 
FROM EMP
 MODEL 
   PARTITION BY (deptno) DIMENSION BY (empno) 
   MEASURES (ename en, CAST('' AS VARCHAR2(32000)) AS longname)
   RULES ITERATE (1200) (longname[ANY] = longname[cv()]||en[cv()]||'-')
);

Now we are ready to explore the new 12.2 LISTAGG syntax...

Module 5

Using LISTAGG

In this first example we will use the pre-12.2 syntax to show what happens if LISTAGG tries to return a value that exceeds 4K characters, or in this particular case the 32J limit.

We will group the data by department number and use LISTAGG to create a concatenated list of values based on the column "longname" that we created in the previous step:

SELECT
 deptno,
 LISTAGG(longname, '; ') WITHIN GROUP (ORDER BY EMPNO)
FROM empln
GROUP BY deptno;

Notice that we get the standard overflow error:

ORA-01489: result of string concatenation is too long

Module 6

Stopping ORA-01489 errors

With 12.2, LISTAGG has an enhanced syntax which allows developers to control what happens if we exceed the 4K character limit or in this case the 32K character limit. Aw with previous versions an error can returned and this is the default behaviour.

However, we can now also trap the error using the new ON OVERFLOW TRUNCATE syntax

Note: In the code below I have chopped-up the string returned by LISTAGG so that you don't have to scroll across 32,000 characters to see the effect on the new keywords

SELECT
 deptno,
 SUBSTR(LISTAGG(longname, ';' ON OVERFLOW TRUNCATE) WITHIN GROUP (ORDER BY EMPNO), 1, 50) AS "Starts...",
 '........',
 SUBSTR(LISTAGG(longname, ';' ON OVERFLOW TRUNCATE) WITHIN GROUP (ORDER BY EMPNO), -50) AS "...Ends"
FROM empln
WHERE deptno = 20
GROUP BY deptno;

Note that where the contents of a row is truncated we add three dots to indicate that truncation has occurred and there is a count of the number of values missing (truncated) from the list.

Module 7

Changing the truncation indicator

By default when a truncation occurs we indicate this using three dots at the end of the string (...). It is possible to change this by adding your own truncation characters after the ON OVERFLOW TRUNCATE keywords. Here are replacing the three dots with an exclamation mark (!).

SELECT
 deptno,
 SUBSTR(LISTAGG(longname, ';' ON OVERFLOW TRUNCATE '!') WITHIN GROUP (ORDER BY EMPNO), 1, 50) AS "Starts...",
 '........',
 SUBSTR(LISTAGG(longname, ';' ON OVERFLOW TRUNCATE '!') WITHIN GROUP (ORDER BY EMPNO), -50) AS "...Ends"
FROM empln
WHERE deptno = 20
GROUP BY deptno;

Module 8

Removing the count of missing values

The final set of new keywords control whether the count of truncated values is shown or not shown. By default, the count is shown and this can be explicitly stated using the WITH COUNT syntax.

Similarly, it is possible to remove the count by using the WITHOUT COUNT keywords after the on overflow... keywords.

SELECT
 deptno,
 SUBSTR(LISTAGG(longname, ';' ON OVERFLOW TRUNCATE WITHOUT COUNT) WITHIN GROUP (ORDER BY EMPNO), 1, 50) AS "Starts...",
 '........',
 SUBSTR(LISTAGG(longname, ';' ON OVERFLOW TRUNCATE WITHOUT COUNT) WITHIN GROUP (ORDER BY EMPNO), -50) AS "...Ends"
FROM empln
WHERE deptno = 20
GROUP BY deptno;