LISTAGG Exception

Based on Oracle, LISTAGG orders data with each group specified in the ORDER BY clause and then concatenates the values of the measure column. It is a built in function that enables us to perform string aggregation. This Function is easy to learn and understand with examples. To demo the purpose of LISTAGG Function, we will be using  emp table from baniya schema. Here’s how the data on emp table looks like.

SELECT deptno, ename from baniya.emp;

Output:

Example 1: Display all the last name of employees in a single line with comma separated list from deptno 20 and 30 only.

SQL Query:

SELECT LISTAGG(ename, ', ') 
         WITHIN GROUP( ORDER BY empno) AS "Employee Last Names",
       MIN(hiredate) 
FROM   baniya.emp 
WHERE  deptno IN (30, 20); 

Output:

Example 2:  Display comma delimited last name of all the employees from each department and then order the last name by employee number.

SQL Query:

SELECT deptno, 
       Listagg(ename, ',') 
         within GROUP( ORDER BY empno) AS "Last Names" 
FROM   baniya.emp 
GROUP  BY deptno 
ORDER  BY deptno;

Output:

Note: You can replace comma delimited with anything that you like, it could be semi column, pipe, empty space, back slash etc.

Recently, I was assigned to a ticket to work on a Function as to why it is not working as designed. The Function had a single query using LISTAGG Function and an Exception block to catch no data found exception. An email has to be send out to the development team when the code hits the exception block. The task is to identify and fix as why the functions is not sending out any email when process data that does not exist.

To explain the problem from the Function, I will use data from employee table and convert function into an anonymous block to simply the stuff. Our employee table does not have anyone with a last name TEST. The exception block should send email to developer team when processing employee that does not exist on employee table.When processing ename with TEST, we are NOT hitting NO_DATA_FOUND exception. Below is the PL/SQL and output for what I just explained.

SET serveroutput ON;DECLARE 
  v_employee_count NUMBER; 
  v_deptno         NUMBER := 0; 
  v_ename_list     VARCHAR2(350); 
  v_ename := ‘test’; 
BEGIN 
  SELECT   listagg(ename, ',') within GROUP( ORDER BY empno) AS "Last Names" 
  INTO     v_ename_list 
  FROM     baniya.emp 
  WHERE    ename = v_ename; 
   
  dbms_output.Put_line('There is at least one employee in deptno ' 
  ||v_deptno); 
  dbms_output.put_line(‘employee exist’); 
EXCEPTION 
WHEN no_data_found THEN 
  dbms_output.Put_line('There are NO employee in deptno ' 
  ||v_deptno); 
  --custom function to send email to developer team for research 
  Send_exception_email(developer_14@abc.com, deptno, v_ename); 
  dbms_output.put_line(‘email sent successfully!!’);  
END;
/

Output:

PL/SQL procedure successfully completed.

There is at least one employee in deptno 0

Employee exist

The NO_DATA_FOUND exception does not work with LISTAGG when you don’t use it in a query that uses group by Function. The output from the PL/SQL block shows that the employee with ename TEST exist. Do you think employee with last name TEST exist? No, it does not. To research and troubleshoot this issue, I tried running just the SQL query to check the output.

SELECT Listagg(ename, ',') 
         within GROUP( ORDER BY empno) AS "Last Names" 
--          INTO v_ename_list     
FROM   baniya.emp 
WHERE  ename LIKE ‘TEST’;

Output:

The above query displayed the result as NULL. Null is different than no data found. NO_DATA_FOUND does not work with the LISTAGG. You will need a IF ELSE to catch the Exception. I recommend you to review your LISTAGG Function and Exception block and ensure the Expectations are being handled properly.

In our example 2, we displayed the comma separated ename list for each dept. number. Let’s find out to check if this query handles no_data_found Exception. This query uses LISTAGG and GROUP BY clause to display data for each dept. We don’t have anyone in dept zero. Let’s use dept no zero to the PL/SQL block.

set serveroutput ON; 
DECLARE 
    v_employee_count NUMBER; 
    v_deptno         NUMBER := 0; 
    v_ename_list     VARCHAR2(350); 
BEGIN 
    SELECT deptno, 
           Listagg(ename, ',') 
             within GROUP( ORDER BY empno) AS "Last Names" 
    INTO   v_deptno, v_ename_list 
    FROM   baniya.emp 
    GROUP  BY deptno 
    HAVING deptno = v_deptno 
    ORDER  BY deptno; 

    dbms_output.Put_line('There is atleast one employee in deptno ' 
                         ||v_deptno); 
EXCEPTION 
    WHEN no_data_found THEN 
      dbms_output.Put_line('There are NO employee in deptno ' 
                           ||v_deptno); 
END; 
/ 

Output:

PL/SQL procedure successfully completed.

There are NO employee in deptno 0

The query throws an Exception for no_data_found and displays the appropriate message from dbms_output package. Why it worked? When running the query from body, it didn’t display NULL, instead it returned nothing meaning no data found.

SELECT deptno, 
       Listagg(ename, ',') 
         within GROUP( ORDER BY empno) AS "Last Names" 
FROM   baniya.emp 
GROUP  BY deptno 
HAVING deptno = 0 
ORDER  BY deptno;

Output:
No Data Found..

LISTAGG is a great Function for string aggregation and a care must be given when using the EXCEPTION. Where possible, we should test all our exceptions.