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
Get Advanced Oracle PL/SQL Programming with Packages now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.