LISTAGG Enhancements in Oracle
Learn how to use the LISTAGG function, which gathers the values in a column of data in a single row according to the grouping criteria given in the SQL statement.
Join the DZone community and get the full member experience.
Join For FreeIn 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.
Opinions expressed by DZone contributors are their own.
Comments