Five Situations where NVL must be used

When I was undergoing my green field training on SQL and PL/SQL basics, I was introduced to the NVL built-in. A simple built in with 2 parameters that will return the first parameter if it is not NULL and return second parameter otherwise. Being new to database programming I thought it can be handy some times. Some times? Well in my last PL/SQL code of 1000 lines, I used 13 of them. So for me this is the most used built-in.

Here I am trying to explain five situations where we must use NVL. To be honest, I learned most of them in the hard way. That is after defects were reported on my code during testing.

1) Date Range checking

I made this mistake back when I accessed value from a value set for one of my programs for first time

SELECT flex_value
FROM fnd_flex_values
WHERE flex_value_set_id = &value_set_id
AND SYSDATE BETWEEN start_date_active AND end_date_active;

The comparison with NULL always leads to FALSE. The correct statement will be

SELECT flex_value
FROM fnd_flex_values
WHERE flex_value_set_id = &value_set_id
AND SYSDATE BETWEEN NVL (start_date_active, SYSDATE - 1)
AND NVL (end_date_active, SYSDATE + 1);

2. Comparison using Not Equal To<>

This is similar to the first case, but is less obvious.

Consider the following block

DECLARE
a NUMBER := 5;
BEGIN
IF (a <> 5)
THEN
DBMS_OUTPUT.put_line (a ' is not equal to 5');
ELSE
DBMS_OUTPUT.put_line (a ' is equal to 5');
END IF;
END;

Perfect? Even I thought so. But not when 'a' is NULL.

DECLARE
a NUMBER := NULL;
BEGIN
IF (a <> 5)
THEN
DBMS_OUTPUT.put_line (a ' is not equal to 5');
ELSE
DBMS_OUTPUT.put_line (a ' is equal to 5');
END IF;
END;

Output says " is equal to 5". The point is NULL is neither equal to not not equal to any other value. So null<>5 returns false and else part of the condition works. The correct code will be

DECLARE
a NUMBER := NULL;
BEGIN
IF (NVL (a, -1) <> 5)
THEN
DBMS_OUTPUT.put_line (a ' is not equal to 5');
ELSE
DBMS_OUTPUT.put_line (a ' is equal to 5');
END IF;
END;


3. Summary Functions

The data in my table 'table1' is

ID	AMOUNT
1	20
2	70
3	40
4
5	60
6	80
7	100

Now if I want to take the sum of records with id 8, well that record does not exist and mathematically 0 is the desired result.

SELECT SUM (amount)
FROM table1
WHERE ID = 8;

But the above SELECT will return null instead. So an NVL must be added to solve this.

SELECT NVL (SUM (amount), 0)
FROM table1
WHERE ID = 8;


4.Arithmetical Expressions

If any value in the calculation is NULL, the entire result becomes null regardless of other values. Try this code.

CREATE OR REPLACE PROCEDURE calculate (
num1 NUMBER,
num2 NUMBER
)
IS
a NUMBER := num1;
b NUMBER := num2;
BEGIN
DBMS_OUTPUT.put_line ('a+b=' (a + b));
DBMS_OUTPUT.put_line ('a-b=' (a - b));
DBMS_OUTPUT.put_line ('a*b=' (a * b));
DBMS_OUTPUT.put_line ('a/b=' (a / b));
END calculate;


The procedure can be called as

BEGIN
calculate (10, 5);
END;

The output is

a+b=15
a-b=5
a*b=50
a/b=2

Now suppose 'a' is null

BEGIN
calculate (null, 5);
END;

All outputs becomes NULL.

a+b=
a-b=
a*b=
a/b=

To avoid this we have to replace the possible NULL values with appropriate values using NVL. Rewrite the procedure as

CREATE OR REPLACE PROCEDURE calculate (
num1 NUMBER,
num2 NUMBER
)
IS
a NUMBER := NVL (num1, 0);
b NUMBER := NVL (num2, 0);
BEGIN
DBMS_OUTPUT.put_line ('a+b=' (a + b));
DBMS_OUTPUT.put_line ('a-b=' (a - b));
DBMS_OUTPUT.put_line ('a*b=' (a * b));
DBMS_OUTPUT.put_line ('a/b=' (a / b));
END calculate;

And we get the intended output.

a+b=5
a-b=-5
a*b=0
a/b=0

5.Looping through VARRAY

Consider the following program which print the contents of a varray. For our test, create a new type

CREATE OR REPLACE TYPE varray_type AS VARRAY (10) OF VARCHAR2 (20);

Now create a database procedure which will accept a varray as parameter and display the contents.

CREATE OR REPLACE PROCEDURE print_varray (
my_varray IN varray_type
)
IS
BEGIN
FOR i IN my_varray.FIRST .. my_varray.LAST
LOOP
DBMS_OUTPUT.put_line (my_varray (i));
END LOOP;
END;

Now test our procedure

DECLARE
my_varray varray_type := varray_type ('Rahul',
'Ameer',
'Jay'
);
BEGIN
print_varray (my_varray);
END;

This will print the output in the dbms_output as expected.

Rahul
Ameer
Jay

All well and good. But if the varray is initialized dynamically and it happens to contain no elements.

DECLARE
my_varray varray_type := varray_type ();
BEGIN
print_varray (my_varray);
END;


I expect the program to complete without printing anything. However I get a PL/SQL numeric or value error. Well, when varray

is not initialized with any data, both my_varray.FIRST and my_varray.LAST becomes NULL which can't be a loop limiting variable
throwing the error. So NVL must be used around both of them.

CREATE OR REPLACE PROCEDURE print_varray (
my_varray IN varray_type
)
IS
BEGIN
FOR i IN NVL (my_varray.FIRST, 0) .. NVL (my_varray.LAST, -1)
LOOP
DBMS_OUTPUT.put_line (my_varray (i));
END LOOP;
END;

This solves this issue.

So the summary is that before all calculations and comparisons we must ensure that the variables used in the process are not NULL.