LISTAGG DEDUPLICATION - A better ListAgg Function: Enhancement in Oracle 19C

I was pondering since this weekend on what should I include in my blog until I found something exciting about the listagg, A built in function in oracle. Let us see what enhancement has been included on this function in oracle in 19c.

ListAgg is one the favorite choice of an interviewer, but very few of us would know that

With ORACLE 19c another gap in the listagg feature was filled. Now we are able to do a native distinct operation on the values in the list, so that the list no longer contains duplicates.

In prior releases we had to deduplicate the values before using them in the listagg, which was rather dreadful when doing other group functions in the same query.

Lets Explore with use cases.

Create table Products (PRODUCT_ID number, PROD_CATEGORY VARCHAR2(100), PRODUCT_SUBCATEGORY VARCHAR2(100));
 
 
 
INSERT INTO PRODUCTS ( PRODUCT_ID, PROD_CATEGORY, PRODUCT_SUBCATEGORY ) VALUES(1,'PHOTO', 'Camcorders');
INSERT INTO PRODUCTS ( PRODUCT_ID, PROD_CATEGORY, PRODUCT_SUBCATEGORY ) VALUES(2,'PHOTO', 'Camera Batteries');
INSERT INTO PRODUCTS ( PRODUCT_ID, PROD_CATEGORY, PRODUCT_SUBCATEGORY ) VALUES(3,'PHOTO', 'Camcorders');
INSERT INTO PRODUCTS ( PRODUCT_ID, PROD_CATEGORY, PRODUCT_SUBCATEGORY ) VALUES(4,'PHOTO', 'Camera Batteries');
INSERT INTO PRODUCTS ( PRODUCT_ID, PROD_CATEGORY, PRODUCT_SUBCATEGORY ) VALUES(5,'PHOTO', 'Camera Batteries');
INSERT INTO PRODUCTS ( PRODUCT_ID, PROD_CATEGORY, PRODUCT_SUBCATEGORY ) VALUES(6,'PHOTO', 'Camera Media');
INSERT INTO PRODUCTS ( PRODUCT_ID, PROD_CATEGORY, PRODUCT_SUBCATEGORY ) VALUES(7,'PHOTO', 'Camera Media');
INSERT INTO PRODUCTS ( PRODUCT_ID, PROD_CATEGORY, PRODUCT_SUBCATEGORY ) VALUES(8,'PHOTO', 'Camera Batteries');
INSERT INTO PRODUCTS ( PRODUCT_ID, PROD_CATEGORY, PRODUCT_SUBCATEGORY ) VALUES(9,'PHOTO', 'Camera Media');
INSERT INTO PRODUCTS ( PRODUCT_ID, PROD_CATEGORY, PRODUCT_SUBCATEGORY ) VALUES(10,'Electronics', 'Game Consoles');
INSERT INTO PRODUCTS ( PRODUCT_ID, PROD_CATEGORY, PRODUCT_SUBCATEGORY ) VALUES(11,'Electronics', 'Game Consoles');
INSERT INTO PRODUCTS ( PRODUCT_ID, PROD_CATEGORY, PRODUCT_SUBCATEGORY ) VALUES(12,'Electronics', 'Y Box Accessories');
INSERT INTO PRODUCTS ( PRODUCT_ID, PROD_CATEGORY, PRODUCT_SUBCATEGORY ) VALUES(13,'Electronics', 'Y Box Accessories');
INSERT INTO PRODUCTS ( PRODUCT_ID, PROD_CATEGORY, PRODUCT_SUBCATEGORY ) VALUES(14,'Electronics', 'Y Box Games');
INSERT INTO PRODUCTS ( PRODUCT_ID, PROD_CATEGORY, PRODUCT_SUBCATEGORY ) VALUES(15,'Electronics', 'Y Box Games');

I have used a table with much duplicate records now to have this done in earlier releases.

select PROD_CATEGORY
 , listagg(PRODUCT_SUBCATEGORY ,' | ') as SubCategories
 from (select distinct PROD_CATEGORY, PRODUCT_SUBCATEGORY 
 from PRODUCTS)
  group by PROD_CATEGORY;
 

Now with oracle 19 c things has become a bit easy

 
select PROD_CATEGORY,
 listagg(distinct PRODUCT_SUBCATEGORY, ' | ') as SubCategories
 from PRODUCTS
 group by PROD_CATEGORY;

Exceptions Illusions.

declare
   l_Variable number;
   no_data_found EXCEPTION;
begin
   select employee_id
     into l_variable
     from employees
    where l_name = 'TYRION';
exception
    when no_data_found
       then sys.dbms_output.put_line('Tyrion does not exist');
end;
------------------------------------------------------------------------
Error report -
ORA-01403: no data found
ORA-06512: at line 5
01403. 00000 -  "no data found"
*Cause:    No data was found from the objects.
*Action:   There was no data from the objects which may be due to end of fetch.

Hold a minute…why does it raise an exception? There is an exception handler that should take care of no_data_found … well yes … but … That is a user defined exception which is different from the named exception that was automatically raised by Oracle. It shares the same name but it is different.

Now how about this?

begin

  declare
    l_Variable number;
    no_data_found EXCEPTION;
  begin
    select employee_id
      into l_variable
      from employees
     where last_name = 'TYRION';
  exception
    when no_data_found then
      sys.dbms_output.put_line('Tyrion does not exist');
  end inner;
exception
  when no_data_found then
    sys.dbms_output.put_line('Yes, Tyrion really does not exist');
end outer;

This time we get the message

Yes, Tyrion really does not exist

because in the outer block the no_data_found exception declared in the inner block is not known and therefore we fall back to Oracles named exception NO_DATA_FOUND which is handled in the outer block.