Call (800) 766-1884 for Oracle support & training
Free Oracle Tips

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle




Miscellaneous Functions
Oracle Tips by Burleson

There are some commonly used functions that don’t fall neatly into any of the other categories—namely, decode() and nvl().


Of all the functions provided by SQL*Plus, decode() is perhaps the most useful. A call to decode() can accept up to 255 parameters. Calls to decode() must follow this basic syntax:

decode (expression, value, new value, default value);

The decode() call can only have one expression and one default value. Value and new value must be a pair of parameters. Consider the following call to decode():

SELECT decode (course_grade, 'A', 4,
                             'B', 3,
                             'C', 2,
                             'D', 1, 0)
WHERE  ssn           = '999999999'
AND    course_number = 2103;

This function is the closest equivalent to a case statement that can be found in SQL. The value and new value parameters don’t have to be literal values; however, they can be expressions such as mathematical operations and function calls.

In the event that you try to port SQL statements between Oracle and another relational database, you should be aware that decode() is not an ANSI standard SQL function.


The nvl() function is used to substitute a NULL value with another value. The most common example of a call to nvl() looks like this:

SELECT nvl (base_salary, 20000)

This would replace any NULL value retrieved by the query with the number 20000.

This is an excerpt from the book "High Performance Oracle Database Automation" by Jonathan Ingram and Donald K. Burleson, Series Editor.


Oracle performance tuning software 

Oracle performance tuning book


Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
Oracle training & performance tuning books



Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter