DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports Events Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
  1. DZone
  2. Data Engineering
  3. Databases
  4. LISTAGG Enhancements in Oracle

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.

Emrah Mete user avatar by
Emrah Mete
CORE ·
May. 10, 17 · News
Like (5)
Save
Tweet
Share
4.62K Views

Join the DZone community and get the full member experience.

Join For Free

In 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:

Image title

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

Image title

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.

Image title

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.

Image title

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;

Image title

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.

Database

Opinions expressed by DZone contributors are their own.

Popular on DZone

  • Java Development Trends 2023
  • Spring Cloud: How To Deal With Microservice Configuration (Part 1)
  • Using AI and Machine Learning To Create Software
  • Load Balancing Pattern

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com
  • +1 (919) 678-0300

Let's be friends: