PL/SQL User's Guide and Reference

Advantages and Disadvantages of Exceptions

Using exceptions for error handling has several advantages. Without exception handling, every time you issue a command, you must check for execution errors, as follows:

BEGIN
   SELECT ...
      -- check for 'no data found' error
   SELECT ...
      -- check for 'no data found' error
   SELECT ...
      -- check for 'no data found' error
   ...
END;

Error processing is not clearly separated from normal processing; nor is it robust. If you neglect to code a check, the error goes undetected and is likely to cause other, seemingly unrelated errors.

With exceptions, you can handle errors conveniently without the need to code multiple checks, as follows:

BEGIN
   SELECT ...
   SELECT ...
   SELECT ...
   ...
EXCEPTION
   WHEN NO_DATA_FOUND THEN  -- catches all 'no data found' errors
      ...
END;

Notice how exceptions improve readability by letting you isolate error-handling routines. The primary algorithm is not obscured by error recovery algorithms.

Exceptions also improve reliability. You need not worry about checking for an error at every point it might occur. Just add an exception handler to your PL/SQL block. If the exception is ever raised in that block (or any sub-block), you can be sure it will be handled.

Disadvantages

Using exceptions for error handling has two disadvantages. First, exceptions can trap only runtime errors. Therefore, a PL/SQL program cannot trap and recover from compile-time (syntax and semantic) errors such as table or view does not exist.

Second, exceptions can mask the statement that caused an error, as the following example shows:

BEGIN
   SELECT ...
   SELECT ...
   SELECT ...
   ...
EXCEPTION
   WHEN NO_DATA_FOUND THEN ...
      -- Which SELECT statement caused the error?
END;

Normally, this is not a problem. But, if the need arises, you can use a locator variable to track statement execution, as follows:

DECLARE
   stmt INTEGER := 1;  -- designates 1st SELECT statement
BEGIN
   SELECT ...
   stmt := 2;  -- designates 2nd SELECT statement
   SELECT ...
   stmt := 3;  -- designates 3rd SELECT statement
   SELECT ...
   ...
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      INSERT INTO errors VALUES ('Error in statement ' || stmt);
      ...
END;