Quick Search:
CODE
Oracle PL/SQL Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
 The Oracle PL/SQL NVL Function      [Return To Index] Jump to:  
  Looking for the original pages? (formerly called "Morgan's Library") You can find them here.

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      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us 62 users online    © 2009 psoug.org
PSOUG LOGIN
Username: 
Password: 
Forgot your password?