Month: October 2016

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.

DROP TABLE my_emp;

CREATE TABLE my_emp AS SELECT * FROM hr.employees;

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

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

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 attempted 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;

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.

SET SERVEROUTPUT ON
DECLARE
   l_count    INTEGER := 0;
   l_last_nm  my_emp.last_name%TYPE;
   TYPE       tr_emp_tab IS TABLE OF my_emp%ROWTYPE;
   lr_emp_tab tr_emp_tab;
BEGIN
   l_last_nm := '&&last_name';
   BEGIN
      SELECT *
        BULK COLLECT
        INTO lr_emp_tab
        FROM my_emp
       WHERE last_name = l_last_nm;

      IF (lr_emp_tab IS NOT NULL AND lr_emp_tab.count > 0) THEN
         dbms_output.put_line('Found employees:');
         FOR i IN lr_emp_tab.first .. lr_emp_tab.last LOOP
            dbms_output.put_line(lr_emp_tab(i).first_name || ' ' || lr_emp_tab(i).last_name);
         END LOOP;
      END IF;
   EXCEPTION
      WHEN no_data_found THEN
         dbms_output.put_line('Could not find any employees given last name: ' || l_last_nm);
   END;
END;
/

If we run it for “De Haan”, “King” and “Bogus”, we get the following output.

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.

SET SERVEROUTPUT ON
DECLARE
   l_count    INTEGER := 0;
   l_last_nm  my_emp.last_name%TYPE;
   TYPE       tr_emp_tab IS TABLE OF my_emp%ROWTYPE;
   lr_emp_tab tr_emp_tab;
BEGIN
   l_last_nm := '&&last_name';
   SELECT *
    BULK COLLECT
    INTO lr_emp_tab
    FROM my_emp
   WHERE last_name = l_last_nm;

   IF (lr_emp_tab IS NULL) THEN
      dbms_output.put_line('The receiving collection is atomically NULL');
   ELSE
      IF (lr_emp_tab.COUNT = 0) THEN
         dbms_output.put_line('The receiving collection is empty');
      END IF;
   END IF;

   IF (lr_emp_tab IS NOT NULL AND lr_emp_tab.count > 0) THEN
      dbms_output.put_line('Found employees:');
      FOR i IN lr_emp_tab.first .. lr_emp_tab.last LOOP
         dbms_output.put_line(lr_emp_tab(i).first_name || ' ' || lr_emp_tab(i).last_name);
      END LOOP;
   END IF;
END;
/

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

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

Live well!