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.

score 6 · Answer 1 · answered May 17 '16 at 17:30

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.

score 4 · Accepted Answer · answered May 17 '16 at 17:30

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.

i agree with you in that way, and allready have querry level exceptions who are there for good reasons, assign value to variable and so on. Package level exceptions were intended for loging purpose only, it's complicated to explain but i can't log exception on higher level, so i have to do it inside this package. thanks for your answer — BeRightBack, May 17 '16 at 21:53

Justin Cave · Answer 3 · 2016-05-17T17:33:50.473

A package is not an executable object so it can't handle exceptions for procedures and functions that are in the package. You'd need to handle the exception where it is generated.

It seems a bit unlikely that you really want to do exactly the same thing every time there is a particular exception thrown. Normally, you'd want the exception handler to be as close as possible to the code that is generating the error so that have the maximum amount of context possible. Often, that means having multiple exception handlers in a single procedure,

PROCEDURE p1 
AS
  ...
BEGIN
  BEGIN
    SELECT col1
      INTO l_var1
      FROM some_table
     WHERE <<something>>
  EXCEPTION
    WHEN no_data_found
    THEN
      l_var1 := null;
  END;

  <<do something>>

  BEGIN
    SELECT col2
      INTO l_var2
      FROM some_table2
     WHERE <<something>>
  EXCEPTION
    WHEN no_data_found
    THEN
      raise_application_error( -20001, 'Error, cannot find a row in some_table2' );
  END;

  ...
END;

PL/SQL Package level exception handling

3 Answers3