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


 

HTML Text

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().

decode()

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)
FROM   SCHEDULED_CLASSES
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.

nvl()

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)
FROM   EMPLOYEES;

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