The NVL used to replace NULL value with another value. To know more about NULL read post Oracle NULL
Syntax:-
NVL(expression,replace_value)
Note:- expression and replace_value must be compatible data types.
Examples :-
Consider below table SALARY.
Name Type
———- ————
NO NUMBER
SAL NUMBER
| NO | SAL |
| 1 | 2000 |
| 2 | 3000 |
| 3 | NULL |
| 4 | NULL |
| 5 | 1000 |
1) Applying NVL to sal column
select no,NVL(sal,0) from salary;
NO NVL(SAL,0)
———- —————-
1 2000
2 3000
3 0
4 0
5 1000
2) NVL with in-compatible data type not allowed
select NVL(sal,’no salary’) from salary where no =3;
ERROR at line 1:
ORA-01722: invalid number
Reason : sal column is number and trying to convert to string.
It is always a good practice to apply NVL for null columns.
Oracle NVL Function replace NULL values with a given value in the result of a query. Oracle NVL Function takes two parameters. If expression of first parameter is not null then NVL returns the expression of first parameter, otherwise returns the expression of second parameter. See details: http://www.rahinur.com/oracle-functions/oracle-nvl-function.html