Quick Search:
 
 The Oracle PL/SQL NVL Function      [Return To Index] Jump to:  

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



Related Links:

Related Code Snippets:
  • NVL: Replace NULL values - The function NVL replaces null values with a stated value. In this exam...
  • NVL - Returns a Value if the Expression IS NULL
 
   Home |    Search |    Code Library |    Sponsors |    Privacy |    Terms of Use |    Contact Us © 2003 - 2024 psoug.org