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 :

CREATE OR REPLACE FUNCTION calltype (code_in IN VARCHAR2) 
   RETURN VARCHAR2
IS
   retval VARCHAR2(100) := NULL;
BEGIN
   PLV.assert 
      (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'));
EMERGENCY
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:

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

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
THEN
   p.l ('The code cannot be null!');
   RAISE VALUE_ERROR;
ELSE
   act_on_code (code_in);
END IF;

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)
THEN
   ...

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

v_hiredate BETWEEN TRUNC (SYSDATE) AND TRUNC (SYSDATE+1)

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)
BEGIN
   PLV.assert_inrange (newsal_in, 0, personnel.max_salary);
   UPDATE emp
      SET sal = newsal_in
    WHERE empid = emp_in;
END;

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.