BULK COLLECT and COUNT() do not raise NO_DATA_FOUND

I thought I knew bulk operations well.

Today, while coding up a quick hunk of PL/SQL, the code did not behave as expected.

Basically, I’d had a senior moment and forgotten something that I think my novice DBA and developer readers might benefit from.

When you try to select some rows from a data structure using a filter, for which none of the rows in the structure are a match, in a SQL client tool, you’ll simply be told that you received 0 rows. But if you are doing the same select statement in PL/SQL to get the output INTO some waiting variable(s), Oracle will raise the NO_DATA_FOUND exception.

1	DROP TABLE my_emp;
2	 
3	CREATE TABLE my_emp AS SELECT * FROM hr.employees;
4	 
5	SET SERVEROUTPUT ON
6	DECLARE
7	   l_email   my_emp.email%TYPE;
8	   l_last_nm my_emp.last_name%TYPE;
9	BEGIN
10	   l_last_nm := '&&last_name';
11	   SELECT email
12	     INTO l_email
13	     FROM my_emp
14	    WHERE last_name = l_last_nm;
15	   dbms_output.put_line('Email Handle: ' || l_email);
16	EXCEPTION
17	   WHEN no_data_found THEN
18	      dbms_output.put_line('Could not find employee given last name: ' || l_last_nm);
19	END;
20	/

1	DROP TABLE my_emp;
2	 
3	CREATE TABLE my_emp AS SELECT * FROM hr.employees;
4	 
5	SET SERVEROUTPUT ON
6	DECLARE
7	   l_email   my_emp.email%TYPE;
8	   l_last_nm my_emp.last_name%TYPE;
9	BEGIN
10	   l_last_nm := '&&last_name';
11	   SELECT email
12	     INTO l_email
13	     FROM my_emp
14	    WHERE last_name = l_last_nm;
15	   dbms_output.put_line('Email Handle: ' || l_email);
16	EXCEPTION
17	   WHEN no_data_found THEN
18	      dbms_output.put_line('Could not find employee given last name: ' || l_last_nm);
19	END;
20	/

Which produces this output if we give it a non-existent employee’s last name of “Bogus”

SQL>
Table dropped
Table created
Could not find employee given last name: Bogus
PL/SQL procedure successfully completed

SQL>
Table dropped
Table created
Could not find employee given last name: Bogus
PL/SQL procedure successfully completed

This use of SELECT into a PL/SQL block is known as an explicit cursor, and you must code for the NO_DATA_FOUND exception if you use it.

This is a good thing.

Unfortunately, once a new developer gets used to this NO_DATA_FOUND exception, they tend to get carried away with it and use it for basic UPDATE and DELETE DML, which is useless. Instead use cursor attributes SQL%FOUND or SQL%ROWCOUNT to determine if the UPDATE or DELETE worked.

I’ve also inherited lots of code in the past that had little functions and SQL that performed “existence checks” to ensure the data is present before attempting to do DML on the keyed data. This approach is entirely unnecessary and produces code that will never be exercised.

SET SERVEROUTPUT ON
DECLARE
   l_count   INTEGER := 0;
   l_email   my_emp.email%TYPE;
   l_last_nm my_emp.last_name%TYPE;
BEGIN
   l_last_nm := '&&last_name';
   SELECT COUNT(*)
     INTO l_count
     FROM my_emp
    WHERE last_name = l_last_nm;
 
   IF (l_count > 0) THEN
      SELECT email
        INTO l_email
        FROM my_emp
       WHERE last_name = l_last_nm;
      dbms_output.put_line('Email Handle: ' || l_email);
   END IF;
 
EXCEPTION
   WHEN no_data_found THEN
      dbms_output.put_line('Could not find employee given last name: ' || l_last_nm);
END;

SET SERVEROUTPUT ON
DECLARE
   l_count   INTEGER := 0;
   l_email   my_emp.email%TYPE;
   l_last_nm my_emp.last_name%TYPE;
BEGIN
   l_last_nm := '&&last_name';
   SELECT COUNT(*)
     INTO l_count
     FROM my_emp
    WHERE last_name = l_last_nm;
 
   IF (l_count > 0) THEN
      SELECT email
        INTO l_email
        FROM my_emp
       WHERE last_name = l_last_nm;
      dbms_output.put_line('Email Handle: ' || l_email);
   END IF;
 
EXCEPTION
   WHEN no_data_found THEN
      dbms_output.put_line('Could not find employee given last name: ' || l_last_nm);
END;

Lines 21 to 23 will never be hit. Why? Because COUNT() does not raise the NO_DATA_FOUND exception. It returns 0 if no rows match the filter.

Well, my senior moment today was forgetting that BULK COLLECT behaves in a manner similar to COUNT().

The following anonymous block retrieves all attributes for employees who match the given last name.

Rewrite and add here: Anonymous Block 1

If we run it for “De Haan”, “King” and “Bogus”, in that order, we get the following output for all three attempts:

SQL>
Found employees:
Lex De Haan
PL/SQL procedure successfully completed

SQL>
Found employees:
Steven King
Janette King
PL/SQL procedure successfully completed

SQL>
PL/SQL procedure successfully completed

SQL>
Found employees:
Lex De Haan
PL/SQL procedure successfully completed

SQL>
Found employees:
Steven King
Janette King
PL/SQL procedure successfully completed

SQL>
PL/SQL procedure successfully completed

That last result surprised me. If there were no rows to put into the waiting collection, just like the simple explicit cursor INTO above, I was expecting it to raise the NO_DATA_FOUND exception and spit out the “Could not find…” sentence to the screen. What I had forgotten — and am sharing so you don’t have to learn the hard way — is that if the explicit cursor is using BULK COLLECT and no rows are found, it gives you an initialized empty collection, which we verify with the code listing below.

Rewrite and add here: Anonymous Block 2

If we run it and provide the last name of “Bogus”, it confirms that what BULK COLLECT does is initialize the collection and skip putting anything in it.

SQL>
Table dropped
Table created
The receiving collection is empty
PL/SQL procedure successfully completed

SQL>
Table dropped
Table created
The receiving collection is empty
PL/SQL procedure successfully completed

Knowing this, I was able to remove the exception section, and as a side effect, made the code simpler and correct.

Until we met again to ascend the database mountain!