Oracle PL/SQL Best Practices

Name

EXC-11: Use WHEN OTHERS only for unknown exceptions that need to be trapped.

Synopsis

Don’t use WHEN OTHERS to grab any and every error. If you know that a certain exception might be raised, include a handler for that specifically.

Example

Here’s an exception section that clearly expects a DUP_VAL_ON_INDEX error to be raised but that buries that information in WHEN OTHERS:

EXCEPTION
   WHEN OTHERS
   THEN
      IF SQLCODE = -1
      THEN
         update_instead (...);
      ELSE
         err.log;
         RAISE;
      END IF;

Here’s a much better approach:

EXCEPTION
   WHEN DUP_VAL_ON_INDEX
   THEN
      update_instead (...);
   WHEN OTHERS
   THEN
      err.log;
      RAISE;

Benefits

Your code more clearly states what you expect to have happen and how you want to handle your errors. That makes the code easier to maintain and enhance.

You avoid hard-coding error numbers in your checks against SQLCODE.