Advanced Oracle PL/SQL Programming with Packages

Advanced Oracle PL/SQL Programming with PackagesSearch this book
Previous: 6.2 Setting the PL/Vision Date MaskChapter 6
PLV: Top-Level Constants and Functions
Next: 6.4 PLV Utilities

6.3 Assertion Routines

PL/Vision provides a set of generic routines you can use in your own programs to assert the validity of your program's assumptions. Just about every piece of software you write makes assumptions about the data it manipulates. For example, parameters may have only certain values or be within a certain range; a string value should have a certain format; an underlying data structure is assumed to have been created. It's fine to have such rules and assumptions, but it is also very important to verify or "assert" that none of the rules is being violated.

The cleanest way to perform this task is to call a prebuilt assertion routine (see Chapter 20 in Oracle PL/SQL Programming). The PLV package offers a variety of procedures to allow you to validate assumptions in the most natural possible manner. In all cases, if the assumption is violated the assertion program will take up to two actions:

  1. Display a message if provided. This string is optional and the default for the string is NULL.

  2. Raise the assertion_failure exception. You can then handle this exception in the program that called the assertion routine, or you can let the exception terminate that program and propagate to the enclosing block.

The PLV assertion routines come in the following flavors:

Procedure Name



Generic assertion routine. You pass it a Boolean expression or value and assert tests to see if that expression is TRUE.


Generic assertion routine to check date and numeric ranges. You provide the value to be checked along with start and end values. If the value (either date or number) does not fall within the specified range, assert_inrange


Generic assertion routine to check that the specified value is NOT NULL.

The assert procedure is the most generic of the assertion routines. It is called, in fact, by the other assertion routines. The header for assert is as follows:

   (bool_in IN BOOLEAN, stg_in IN VARCHAR2 := NULL);

6.3.1 Using the assert Procedure

Let's take a look at how to incorporate assertion routines in your code, and then examine the impact. The following procedure translates a code into a description string. There are only three valid codes, an assumption that is validated by the call to PLV.assert:

   retval VARCHAR2(100) := NULL;
      (code_in IN ('E', 'C', 'I'), 'Enter E C or I...');
   IF code_in = 'E'
   THEN retval := 'EMERGENCY';
   ELSIF code_in = 'C'
   THEN retval := 'COMPLAINT';
   ELSIF code_in = 'I'
   THEN retval := 'INFORMATION';
   END IF;
   RETURN retval;
END calltype;

Notice that I pass a complex Boolean expression as an argument to the assert routine. This may seem odd at first glance, but you will get used to it quickly. A program's Boolean argument can be a literal, a variable, or an expression.

Now we will try using the calltype function by embedding it in calls to the p.l procedure so that we can see the results.

In the first call to calltype below, I pass a valid code and p.l displays the correct returned description. In the second call to calltype, I pass an invalid code, J. As a result, the assertion routine displays the message as specified in the function and then raises the exception, which goes unhandled.

SQL> exec p.l(calltype('E'));
SQL> exec p.l(calltype('J'));
Enter E C or I...
ERROR at line 1:
ORA-06510: PL/SQL: unhandled user-defined exception

The error is displayed as a "user-defined exception" because PLV.assert raised the assertion_failure exception, which is not a system exception. You can trap for that exception as shown below:

   p.l (calltype ('J'));
   WHEN PLV.assertion_failure
      p.l ('Invalid call type code');

6.3.2 Asserting NOT NULL

The other assertion routines are designed to handle specific kinds of assertions that programmers must commonly handle. The assert_notnull routine, for example, allows you to easily make sure that an argument to a program is NOT NULL.

Without an assertion routine, you will write variations of code like this over and over again in your programs:

IF code_in IS NULL
   p.l ('The code cannot be null!');
   act_on_code (code_in);

With PLV.assert_notnull, you simply attempt to assert the rule. If the code "passes," you move on to your action:

PLV.assert_notnull (code_in);
act_on_code (code_in);

You save on the typing and your indentation flattens out, thereby improving the readability of your program.

PLV offers four overloadings of assert_notnull, so you can pass it Booleans, strings, dates, and numbers.

6.3.3 Asserting "In Range"

The range assertion routines will probably save you the most code and provide a higher level of coverage of problem data. PLV offers two overloaded assert_inrange programs: one for dates and one for numbers.

The date range assertion routine header is:

PROCEDURE assert_inrange
   (val_in IN DATE,
    start_in IN DATE := SYSDATE,
    end_in IN DATE := SYSDATE+1,
    stg_in IN VARCHAR2 := NULL,
    truncate_in IN BOOLEAN := TRUE);

The first three arguments should be clear: You provide the value you want to check, as well as the start and end dates. Notice that the default start date is SYSDATE or "now" (at midnight) and the default end date is SYSDATE+1 or "tomorrow" (at midnight). The fourth argument, stg_in, is the optional string for display.

The fifth parameter, truncate_in, allows you to specify whether or not you want the end-point dates to be truncated. When a date is truncated (with the default mask, which is your only option in assert_inrange), the time portion is removed. The default setting for this argument is to perform truncation. I offer this option because in many cases when developers want to perform date range checks, they do not want to have to deal with the time component. That aspect of a date variable can, in fact, cause "obviously correct" dates to fail.

The default values of assert_inrange for dates is designed to allow you to assert with a minimum of typing that a date falls on the current day. Consider this call to the assertion program:

IF PLV.assert_inrange (v_hiredate)

If no other arguments are specified, then PLV checks to see if


which, given the way TRUNC works, asks: "Is hiredate between midnight of last night and midnight of the coming night?" In other words, does v_hiredate fall anywhere during the current day?

The numeric assert_inrange is more straightforward. As you can see from the header below, there is no truncate argument. It simply checks to see if the specified number falls within the specified range.

PROCEDURE assert_inrange
   (val_in IN NUMBER,
    start_in IN NUMBER,
    end_in IN NUMBER,
    stg_in IN VARCHAR2 := NULL);

The following procedure updates the salary of an employee, but only if the new salary does not exceed the maximum salary allowed in the system (returned by the personnel package max_salary function):

PROCEDURE update_salary 
   (emp_in IN emp.empno%TYPE, newsal_in IN NUMBER)
   PLV.assert_inrange (newsal_in, 0, personnel.max_salary);
   UPDATE emp
      SET sal = newsal_in
    WHERE empid = emp_in;

If you are careful and consistent in your use of assertion programs like those in the PLV package, your programs will be more robust and less likely to fail in unpredictable ways.

Previous: 6.2 Setting the PL/Vision Date MaskAdvanced Oracle PL/SQL Programming with PackagesNext: 6.4 PLV Utilities
6.2 Setting the PL/Vision Date MaskBook Index6.4 PLV Utilities

The Oracle Library Navigation

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.

Library Home Oracle PL/SQL Programming, 2nd. Ed. Guide to Oracle 8i Features Oracle Built-in Packages Advanced PL/SQL Programming with Packages Oracle Web Applications Oracle PL/SQL Language Pocket Reference Oracle PL/SQL Built-ins Pocket Reference