Advanced Oracle PL/SQL Programming with Packages

6.1. Null Substitution Value

It has been my experience that when I display a variable that is NULL, I want to see some evidence of that fact. In fact, I have found that in several places in PL/Vision I want to be able to substitute a NULL value for some other string. Rather than hard code that value into my package, I added the ability in the PLV package to decide what the substitution value would be with the set_nullval procedure:

PROCEDURE set_nullval (nullval_in IN VARCHAR2);

The default value for NULL substitutions is the string NULL. You can obtain the current setting of the substitution value by calling the nullval function:

FUNCTION nullval RETURN VARCHAR2;

The p package makes use of the substitute value for NULLs. When you call p.l to display text that RTRIMs to a NULL, it will automatically display the string that you have specified for substitution. In the following example, I set the NULL substitution value to N/A and then demonstrate its use in a call to the p.l procedure.

SQL> exec PLV.set_nullval ('N/A');
SQL> VARIABLE v NUMBER;
SQL> exec p.l (:v);
N/A