#

| Chapter 6 PLV: Top-Level Constants and Functions |

## 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 | Description
--- | ---
`assert` | Generic assertion routine. You pass it a Boolean expression or value and assert tests to see if that expression is TRUE.
`assert_inrange` | 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`
`assert_notnull` | 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:

>     PROCEDURE assert
>        (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`:

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

6.2 Setting the PL/Vision Date Mask |  | 6.4 PLV Utilities

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