Oracle NVL function

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.

One response to “Oracle NVL function”

  1. 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