Oracle PL/SQL Best Practices

Name

EXC-01: Verify preconditions using standardized assertion routines that raise violation exceptions.

Synopsis

Every time you write a program, you make certain assumptions. A user of your program doesn’t necessarily know about those assumptions. If you don’t “code defensively” and make sure that your assumptions aren’t violated, your programs can break down in unpredictable ways.

Use assertion routines to make it as easy as possible to validate assumptions in a declarative fashion. These routines, standardized for an entire application, take care of all the housekeeping: what to do when a condition fails, how to report the problem, and whether and how to stop the program from continuing.

Example

Here’s a simple assertion program that checks to see if a condition is TRUE. If the condition is FALSE or NULL, the procedure displays a message to the screen and then raises an exception (if so desired) with dynamic PL/SQL (this implementation relies on Oracle8i `s native dynamic SQL):

CREATE OR REPLACE PROCEDURE assert (
   condition_in IN BOOLEAN,
   message_in IN VARCHAR2,
   raise_exception_in IN BOOLEAN := TRUE,
   exception_in IN VARCHAR2
         := 'VALUE_ERROR'
)
IS
BEGIN
   IF    NOT condition_in
      OR condition_in IS NULL
   THEN
      pl ('Assertion Failure!');
      pl (message_in);

      IF raise_exception_in
      THEN
         EXECUTE IMMEDIATE 
           'BEGIN RAISE ' || exception_in || '; END;';
      END IF;
   END IF;
END assert;

With this program in place, you can easily, and in a declarative fashion, make sure that all inputs are hunky-dory before proceeding ...