Term: NVL

Definition:
The Oracle PL/SQL NVL function is used to substitute a NULL value in a query with an alternate value. Note that the data type of the alternate value must be same or compatible with the data type of the column.

Example Syntax:

NVL(expression 1, expression 2)

If 'expression 1' is NULL, it returns 'expression 2', else it returns 'expression 1'.

The data type of the return output is same as that of 'expression 1'. If it is a character data type, then the second expression is also type casted to character type. If it is a number, then the second argument is type casted to the datatype which has higher numeric precedence.

Example Usage:

DECLARE

L_NUM VARCHAR2(100);

BEGIN

DBMS_OUTPUT.PUT_LINE(NVL(L_NUM,100));

DBMS_OUTPUT.PUT_LINE(NVL(L_NUM,'200'));

END;

/

100

200



PL/SQL procedure successfully completed.

If there is a mismatch in the type of the first argument and second argument, the following error is returned:

DECLARE

L_NUM NUMBER;

BEGIN

DBMS_OUTPUT.PUT_LINE(NVL(L_NUM,'null'));

END;

/

DECLARE

*

ERROR at line 1:

ORA-06502: PL/SQL: numeric or value error: character to number conversion error

ORA-06512: at line 4