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.