PL/SQL Package level exception handling

Question

I have rather simple question: is exception handling possible at the package level? And if yes, how to implement it?

My package has procedures and functions in it, and in case of, let's say, a NO_DATA_FOUND exception I want to do the same thing in all of my procedures and functions.

So my question is: can I write

WHEN NO_DATA_FOUND THEN

just once and use that same lines for NO_DATA_FOUND exceptions in all my procedures/functions, or do I have to write that exception handler in every procedure/function.

Answer 1

No, you can't handle an exception globally across all procedure/functions in a package.

The exception handler documentation says:

An exception handler processes a raised exception. Exception handlers appear in the exception-handling parts of anonymous blocks, subprograms, triggers, and packages.

Which makes it sound like you can; but the 'packages' reference there is referring to the initialisation section of the create package body statement:

But that section "Initializes variables and does any other one-time setup steps", and is run once per session, when a function or procedure in the package is first invoked. Its exception handler doesn't do anything else.

If you really want similar behaviour then you can put that into its own (probably private) procedure and call that from the exception handler on each procedure/function. Which might save a bit of typing but is likely to mask what is really happening, if you're trying to log the errors, say. It's probably going to be simpler and better to have specific exception handling, even if that causes some repetition.

Answer 2

No, it's not possible. I expect that that's not in the language because it's not consistent with proper and intended use of exception handlers.

The general rule of thumb that I apply is: "if you don't have something specific and helpful to do in response to an exception, don't catch it".

If NO_DATA_FOUND is expected and OK in a given situation and you can ignore it and/or assume a default value for the data, then you'd want to catch and handle that (and a package-level handler wouldn't help, because your handling would be situation-dependent). In all other cases, you don't want to catch the NO_DATA_FOUND -- it represents a true exception: something that shouldn't have happened, something outside your design assumptions. Let those propagate up to the top-level, who can log them and/or report them to the client.

But maybe you'd get better answers if you explained what it is you'd want the package-level exception handler to do.