Nullified Remains

Just recently a service request was opened with Oracle Support regarding the “when others then null” exception handler when it was found in an Oracle-supplied form for the Oracle Inventory application from the E-Business suite. It appears the person who opened the SR believes (and rightly so) this is not the most informative of exception handlers nor is it proper coding practice; the request was opened in hopes of disallowing such constructs. I originally posted:

“Sadly Oracle Support may not do anything about this for two reasons:

1) It’s valid code.
2) It’s not causing another error to surface.
“

Amending my post to reflect the actual nature of the SR it’s now clear why this was opened — Oracle should never issue forms or production code using this exception ‘non-handler’ so it’s understandable why Oracle Support filed a bug report (bug number 14237626) for it. Why is coding “when others then null” not the best idea? Let’s look at some examples and see what could be hidden behind that glorious construct. Creating a table used in a previous post:

SQL> --
SQL> -- Create a test table
SQL> --
SQL> create table pktest(
2       pk number,
3       val1 number,
4       val2 number,
5       val3 number
6 );

Table created.

SQL>
SQL> --
SQL> -- Add the primary key
SQL> --
SQL> alter table pktest add constraint pktestpk primary key (pk);

Table altered.

SQL>
SQL> --
SQL> -- Insert data
SQL> --
SQL> begin
2       for i in 1..25 loop
3             insert into pktest
4             values(i,i+1,i+2,i+3);
5       end loop;
6 end;
7 /

PL/SQL procedure successfully completed.

SQL>
SQL> --
SQL> -- Commit the data
SQL> --
SQL> commit;

Commit complete.

SQL>

Using this table/key combination let’s try some transactions that will generate errors; we’ll hide them by using “when others then null”:

SQL> --
SQL> -- Try and insert an existing key
SQL> -- value into the test table
SQL> --
SQL> -- Intentionally obscure the original
SQL> -- error
SQL> --
SQL> begin
2       insert into pktest values (1,2, 3, 4);
3 exception
4       when others then
5             null;
6 end;
7 /

PL/SQL procedure successfully completed.

SQL>

An insert was intentionally attempted that violates the primary key constraint yet no error was thrown — how lovely. The worst part of this is the code is declared to have run successfully; it appears that the insert was executed without error (which we know is NOT true) so the end user who ran this glorious piece of code has no idea his or her insert failed. These, of course, are not the only errors that can be hidden from view as other, more insidious errors can be ignored:

SQL>
SQL> --
SQL> -- Artificially generate some rather severe
SQL> -- errors
SQL> --
SQL> -- Ignore them in the exception handler
SQL> --
SQL> declare
  2          bad             exception;
  3          reallybad       exception;
  4          trulyreallybad  exception;
  5
  6          pragma exception_init(bad, -43); -- remote operation failed
  7          pragma exception_init(reallybad, -1019); -- unable to allocate memory on user side
  8          pragma exception_init(trulyreallybad, -1034); -- Oracle not available
  9  begin
 10          begin
 11                  raise bad;
 12          exception
 13                  when others then null;
 14          end;
 15          begin
 16                  raise reallybad;
 17          exception
 18                  when others then null;
 19          end;
 20          begin
 21                  raise trulyreallybad;
 22          exception
 23                  when others then null;
 24          end;
 25  end;
 26  /

PL/SQL procedure successfully completed.

SQL>

Notice that even these errors were not reported, including the ‘Oracle not available’ error indicating connection to the database failed resulting in nothing being executed. Oracle reported that the PL/SQL ran successfully (meaning without error) all because of the ‘when others then null’ error mis-handler.

All of this transpires due to a lack of knowledge of what errors could be expected, a bout of laziness on the part of the developer or both — “Gee, I don’t know what other errors to expect but they can’t be serious so I’ll ignore them.” Any error is serious to the end user as it prevents work from completing, and if those errors are ignored and the code block execution looks successful then, when problems arise because of missing data, the issue is harder to troubleshoot. Absent an error message, no matter how trivial that error may seem to the developer, the end user has no indication that the insert/update/delete failed and has nothing to report to the help desk. Codng such mis-handlers also is a disservice to the developer/programmer as they won’t know what caused the problem any more than the user who ran the code. Not knowing the cause makes the solution that much more difficult to find. In that case everybody loses.

This is the third post on error handling and error reporting I’ve written, two on properly reporting error text so the end user can talk intelligently to the help desk on what went wrong, and this one, on making sure there IS an error message to report. All three should be read as a set (in my opinion) so that error messages are reported and the text that the end user sees provides useful information to the service desk personnel. This falls, really, on the developers and programmers as the error handlers they code directly affect what the end users see when errors arise, and the error messages should be clear enough for a non-technical Oracle user to describe to the help desk representatives with whom they speak. A little effort on the development side goes a long way in making troubleshooting easier if, or when, the time comes.

Third time is a charm.

Compound Interest

Oracle 11g offers a new twist on triggers, the compound trigger, a trigger than can act both before and after an update, insert or delete has occurred. This makes possible the abilty in one trigger to perform processing similar to a stored procedure without having to write such a procedure to call from a traditional trigger. Compound triggers can be used to avoid the dreaded mutating table error or to process and accept or reject updates to a table based upon desired criteria. Before we look at such an example a description of how a compound trigger is constructed is in order.

Compound triggers can have up to four sections:

the BEFORE section
the BEFORE EACH ROW section
the AFTER EACH ROW section
the AFTER section

At least two of the sections must be included (including only one of the four would result in traditional trigger) and it does not matter which two of the sections are used. For example such a trigger can include a BEFORE EACH ROW section and an AFTER section; the two sections need not be ‘matched’ (BEFORE, BEFORE EACH ROW, for instance). Also the COMPOUND TRIGGER STATEMENT must be included so Oracle will recognize the above four constructs and treat them accordingly. The general syntax is:

create or replace trigger <trigger name>
for <insert|update|delete> <of column_name> on <tablename>
      COMPOUND TRIGGER
      <declare section>
      BEFORE
      <before section>
      BEFORE EACH ROW
      <before each row section>
      AFTER EACH ROW
      <after each row section>
      AFTER
      <after section>
END;
/

Since compound triggers are relatively new and many may not have had the opportunity to write or use them I have provided a working example. Setting the stage for this trigger HR has set a restriction on the size of a raise to be given; based on the department the raise cannot exceed 12 percent of the department average salary. A compound trigger can be used to process the raise amounts assigned. Such a compound trigger is shown below, along with several ways of executing the raises:

SQL> create or replace trigger check_raise_on_avg
  2  for update of sal on emp
  3  COMPOUND TRIGGER
  4    Twelve_Percent        constant number:=0.12;
  5
  6    -- Declare collection type and variable:
  7
  8    TYPE Department_Salaries_t  IS TABLE OF Emp.Sal%TYPE
  9                                  INDEX BY VARCHAR2(80);
 10    Department_Avg_Salaries     Department_Salaries_t;
 11    TYPE Sal_t             IS TABLE OF Emp.Sal%TYPE;
 12    Avg_Salaries                Sal_t;
 13    TYPE Deptno_t       IS TABLE OF Emp.Deptno%TYPE;
 14    Department_IDs              Deptno_t;
 15
 16    BEFORE STATEMENT IS
 17    BEGIN
 18      SELECT               AVG(e.Sal), NVL(e.Deptno, -1)
 19        BULK COLLECT INTO  Avg_Salaries, Department_IDs
 20        FROM               Emp e
 21        GROUP BY           e.Deptno;
 22      FOR j IN 1..Department_IDs.COUNT() LOOP
 23        Department_Avg_Salaries(Department_IDs(j)) := Avg_Salaries(j);
 24      END LOOP;
 25    END BEFORE STATEMENT;
 26
 27    AFTER EACH ROW IS
 28    BEGIN
 29      IF :NEW.Sal - :Old.Sal >
 30        Twelve_Percent*Department_Avg_Salaries(:NEW.Deptno)
 31      THEN
 32        Raise_Application_Error(-20000, 'Raise too large');
 33      END IF;
 34    END AFTER EACH ROW;
 35  END Check_Raise_On_Avg;
 36  /

Trigger created.

SQL> select empno, sal from emp;

     EMPNO        SAL
---------- ----------
      7369        800
      7499       1600
      7521       1250
      7566       2975
      7654       1250
      7698       2850
      7782       2450
      7788       3000
      7839       5000
      7844       1500
      7876       1100
      7900        950
      7902       3000
      7934       1300

14 rows selected.

SQL>
SQL> update emp set sal=sal*1.10 where empno = 7369;

1 row updated.

SQL>
SQL> select empno, sal from emp;

     EMPNO        SAL
---------- ----------
      7369        880
      7499       1600
      7521       1250
      7566       2975
      7654       1250
      7698       2850
      7782       2450
      7788       3000
      7839       5000
      7844       1500
      7876       1100
      7900        950
      7902       3000
      7934       1300

14 rows selected.

SQL>
SQL> rollback;

Rollback complete.

SQL>
SQL> update emp set sal=sal*1.08 where deptno = 20;

5 rows updated.

SQL>
SQL> select empno, sal from emp;

     EMPNO        SAL
---------- ----------
      7369        864
      7499       1600
      7521       1250
      7566       3213
      7654       1250
      7698       2850
      7782       2450
      7788       3240
      7839       5000
      7844       1500
      7876       1188
      7900        950
      7902       3240
      7934       1300

14 rows selected.

SQL>
SQL> rollback;

Rollback complete.

SQL>

Does the trigger reject raises? It certainly does:

SQL> update emp set sal=sal*1.10 where deptno = 30;
update emp set sal=sal*1.10 where deptno = 30
       *
ERROR at line 1:
ORA-20000: Raise too large
ORA-06512: at "BING.CHECK_RAISE_ON_AVG", line 30
ORA-04088: error during execution of trigger 'BING.CHECK_RAISE_ON_AVG'

SQL>
SQL> select empno, sal from emp;

     EMPNO        SAL
---------- ----------
      7369        800
      7499       1600
      7521       1250
      7566       2975
      7654       1250
      7698       2850
      7782       2450
      7788       3000
      7839       5000
      7844       1500
      7876       1100
      7900        950
      7902       3000
      7934       1300

14 rows selected.

SQL> update emp set sal=sal*1.10 where empno = 7698;
update emp set sal=sal*1.10 where empno = 7698
       *
ERROR at line 1:
ORA-20000: Raise too large
ORA-06512: at "BING.CHECK_RAISE_ON_AVG", line 30
ORA-04088: error during execution of trigger 'BING.CHECK_RAISE_ON_AVG'

SQL>
SQL> select empno, sal from emp;

     EMPNO        SAL
---------- ----------
      7369        800
      7499       1600
      7521       1250
      7566       2975
      7654       1250
      7698       2850
      7782       2450
      7788       3000
      7839       5000
      7844       1500
      7876       1100
      7900        950
      7902       3000
      7934       1300

14 rows selected.

SQL>

The first rejected update unfortunately disallowed all of the raises based on the failure of a few; the second update shows one employee where a 10 percent raise would be greater than 12 percent of the departmental average salary. Of course it is usually rare to see such a large raise given throughout an entire department so such occurrences would be few as raises are usually processed (outside of cost-of-living adjustments) on an individual basis.

Please note that doing the above in a traditional trigger would have resulted in a mutating table error since the table being updated cannot be queried during the update; all successful raises were processed and no such error was thrown.

Compound triggers are a nice addition to an already robust database system; they may not be commonplace but having them available certainly makes application development simpler as business rules that may be unenforceable using a regular trigger can be successfully implemented. They may be considered as ‘specialty tools’ in the database realm but remember that plumbers, builders and mechanics also have tools they only use once in a while and when the situation arises where a compound trigger can be useful it’s good to have them around.

So when do I get my raise?