RAISE_APPLICATION_ERROR's Forgotten Parameter

One of my current goals is to re-read the literature I have on PL/SQL and SQL. I'm going back to the documentation and my PL/SQL books to give myself a bit of a refresher course. One of the things I came across today is something I believe is not very well known.

The built in RAISE_APPLICATION_ERROR procedure has a little-known third parameter. 

RAISE_APPLICATION_ERROR ( 
   num            IN BINARY_INTEGER,
   msg            IN VARCHAR2,
   keeperrorstack IN BOOLEAN); 

The documentation states that for the keeperrorstack parameter "If you specify TRUE, PL/SQL puts error_code on top of the error stack. Otherwise, PL/SQL replaces the error stack with error_code."

Here's a quick script to demonstrate the difference between using TRUE and FALSE.

SQL> DECLARE
  2    PROCEDURE p2 IS
  3    BEGIN
  4      raise_application_error(
  5        num => -20002,
  6        msg => 'Error in P2',
  7        keeperrorstack => FALSE);
  8    END p2;
  9
 10    PROCEDURE p1 IS
 11    BEGIN
 12      p2;
 13    EXCEPTION
 14      WHEN OTHERS THEN
 15        raise_application_error(
 16          num => -20001,
 17          msg => 'Error in P1',
 18          keeperrorstack => FALSE);
 19    END p1;
 20  BEGIN
 21    p1;
 22  END;
 23  /
DECLARE
*
ERROR at line 1:
ORA-20001: Error in P1
ORA-06512: at line 15
ORA-06512: at line 21


SQL>
SQL> DECLARE
  2    PROCEDURE p2 IS
  3    BEGIN
  4      raise_application_error(
  5        num => -20002,
  6        msg => 'Error in P2',
  7        keeperrorstack => FALSE);
  8    END p2;
  9
 10    PROCEDURE p1 IS
 11    BEGIN
 12      p2;
 13    EXCEPTION
 14      WHEN OTHERS THEN
 15        raise_application_error(
 16          num => -20001,
 17          msg => 'Error in P1',
 18          keeperrorstack => TRUE);
 19    END p1;
 20  BEGIN
 21    p1;
 22  END;
 23  /
DECLARE
*
ERROR at line 1:
ORA-20001: Error in P1
ORA-06512: at line 15
ORA-20002: Error in P2
ORA-06512: at line 21

The two anonymous PL/SQL blocks are identical except for the exception handler in procedure P1. The second anonymous block passes a value of TRUE for the keeperrorstack parameter in the call to raise_application_error in P1's exception handler. The difference in the output is very important. As you can see from the first block's output, we've lost the error code from the first error that was raised in procedure P2. In the second block the error code appears. I don't know about you, but I'd find it much easier to debug the second block.

Giving yourself and fellow developers as much information as possible about errors is so very, very important. It's incredibly frustrating when you're given an error to debug and the code has been written to do nothing but hide the actual error!

Something to keep in mind when you're developing your error handling strategy for your applications.