Oracle PL/SQL Best Practices

Name

EXC-08: Avoid hard-coded exposure of error handling by using standard, declarative procedures.

Synopsis

The best way to achieve consistent, high-quality error handling throughout your application is to offer a set of predefined procedures that do the basic plumbing of error handling: record the error information if desired, propagate the exception, and so on.

It’s crucial then to make certain that development team members always and only use these procedures in their WHEN clauses.

Example

Here’s the kind of code you should never write inside an exception handler:

EXCEPTION
   WHEN NO_DATA_FOUND THEN 
      v_msg := 'No company for id ' || TO_CHAR (v_id);
      v_err := SQLCODE;
      v_prog := 'fixdebt';
      INSERT INTO errlog VALUES
         (v_err, v_msg, v_prog, SYSDATE, USER);

   
   WHEN OTHERS THEN
      v_err := SQLCODE;
      v_msg := SQLERRM;
      v_prog := 'fixdebt';
      INSERT INTO errlog VALUES
         (v_err, v_msg, v_prog, SYSDATE, USER);
      RAISE;

There are several problems with this code:

• Exposure of logging method. What if you change the structure of the table, or decide to write to a file instead? Every handler has to change.

• Hard-coded program names. This information is available from the built-in function DBMS_UTILITY.FORMAT_CALL STACK.

A better approach is to rely on predefined handlers. Here’s a rewrite of the same exception section:

EXCEPTION
   WHEN NO_DATA_FOUND THEN 
      err.handle (
        'No company for id ' || TO_CHAR (v_id),
        log => TRUE,
        reraise => FALSE);
   WHEN OTHERS THEN
      err.handle (log => TRUE, reraise => TRUE);

Benefits

All developers ...