One exception handler for all packaged subprograms?

This question was submitted as a comment in one of my videos today:

Do we have to include an exception section for each individual subprogram or can we have a single handler for all subprograms?

The quick answer is: if you want an exception raised in a procedure or function defined in a package, you need to add an exception to that subprogram.

I can certainly see why this question would come up. A package body can have its own exception handler. Here's an example:

CREATE OR REPLACE PACKAGE pkg
   AUTHID DEFINER
IS
   PROCEDURE proc;
END;
/

CREATE OR REPLACE PACKAGE BODY pkg
IS
   PROCEDURE proc
   IS
   BEGIN
      RAISE NO_DATA_FOUND;
   END;
BEGIN
   NULL;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      DBMS_OUTPUT.put_line ('Was proc executed?');
END;
/

And it kinda, sorta looks like if I execute the following block, I will see "Was proc executed?" on my screen.

BEGIN
   pkg.proc;
END;
/

But I would be wrong. Instead, I will see:

ORA-01403: no data found
ORA-06512: at "QDB_PROD.PKG", line 6

But, but, but....what's going on here? I explicitly handle NO_DATA_FOUND right there at the bottom of the package.

What's going on is that the exception handler only looks like it is a handler for the entire package. In actually, it can only possibly handle exceptions raised by the executable code between the BEGIN and EXCEPTION keywords underneath the proc procedure.

This is called the initialization section of the package.  It is designed - you guessed it - initialize the state of the package (set values, perform QA checks, etc.). It runs once per session to initialize the package.

HOWEVER: in stateless environments like websites, this code may well execute each time a user references a package element (runs a subprogram, gets the value of a variable). So these days, it would be rare to find an initialization section in a package, and probably something to generally avoid.

The bottom line when it comes to exception handling for subprograms in a package is simple: you must include an exception section in each of those subprograms. The code that is executed in each of those subprograms could be shared. And should be. You should use a generic error logging API like the open source Logger, so that everyone handles, logs and re-raises exceptions in the same way.