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

Corporate Oracle Training
Custom Oracle Training
Oracle New Features Training
Advanced Oracle DBA Classes
Oracle Tuning Courses
Oracle Tips & Tricks
Oracle Training Links
Oracle Training Links
Oracle Training Links

We are top for USA Oracle Training Clients


Free Oracle Tips


Free Oracle App Server Tips


Oracle support

Oracle training

Oracle tuning


Remote Oracle

Custom Oracle Training


  Oracle Concepts by Burleson Consulting

Understanding Oracle SQL Built-In Functions

Ever since the advent of relational databases and the SQL language, database vendors have been at odds with the concept of standardization.  Every database vendor desires to be ANSI compliant with regard to their relational database and the structure of their SQL, but vendors also have a proprietary interest in extending the functionality of their database so that they can be better than their competition.

This market reality has led to the development of SQL extensions.  Within the Oracle 10g database, these SQL extensions are called built-in functions, or BIFs.  Oracle BIFs have been introduced to extend the functionality of SQL, provide date arithmetic functions, and also allow for the creation of customized functions within SQL statements.

Oracle has introduced over 50 different built-in functions into their database engine, and many of these powerful functions allow for automatic data transformation inside the SQL query.

Using decode and case functions

The most powerful of all of the BIFs are the decode and case functions.  The decode and case functions are used within the Oracle database to transform data values for one value to another. 

One of the most amazing features of the case the decode statements is that they allow us to create an index on data column values that do not exist in the Oracle database.

Oracle started with the decode statement and later refined it in Oracle 10g, morphing it into the case statement.

Let’s take a look at how the decode statement works.  The decode statement was developed to allow us to transform data values at retrieval time.  For example, say we have a column named REGION, with values of N, S, W and E.  When we run SQL queries, we want to transform these values into North, South, East and West.  Here is how we do this with the decode function:

   decode (

Note that decode starts by specifying the column name, followed by set of matched-pairs of transformation values.  At the end of the decode statement we find a default value.  The default value tells decode what to display if a column values is not in the paired list.

We can also use the decode statement to count the number of distinct values within a data column, such as the report below: 

-------------------- ---------- ---------- ---------- ------------- ---------- 
Big City                      0          1          0             1          0 
Book Press                    0          0          0             1          0 
Bookhouse                     2          0          0             1          1 
Desk Top                      0          0          1             0          0 
Learning Works                1          0          1             0          0 
Mammoth House                 1          1          0             1          0 
Mountain                      0          2          0             0          0 
Nitpick And Sons              1          0          1             1          0 
Star Books                    0          0          1             1          0

Here is the SQL to create this report in your sample database:

   initcap(substr(pub_name,1,20))            publisher_name,
   sum(decode(book_type,'computer',1,0))     computer,
   sum(decode(book_type,'fiction',1,0))      fiction,
   sum(decode(book_type,'management',1,0))   management,
   sum(decode(book_type,'miscellaneous',1,0)) miscellaneous,
   sum(decode(book_type,'music',1,0))        music
   publisher p,
   book      b
   p.pub_key = b.pub_key
group by

This is a very important SQL statement because it demonstrates the nesting of BIFs and the use of decode for counting values.  Let’s take a closer look.

1 – Transformation of publisher name - In the above SQL we see that we are selecting the first 20 characters of the pub_name (substr(pub_name,1,20)) and then passing this result to the initcap function to make the words display in title case.

initcap(          <== change to title case
   substr(        <== take the sub-string
      pub_name,   <== of the pub_name column
      1,          <== starting at column 1
      20          <== for 20 columns

2 – Transformation  of book type - When we decode book_type, note that we are transforming the value to a 1 if the column is found, and a 0 if it is not found. 

   book_type,         <== Column name
   ‘computer, 1       <== Matched pair – if computer, then 1
   0                  <== default value if not computer

Once we have converted the column to a 0-1 numeric value, we pass the entire clause to the sum function, and add-up the numbers.

As we can see, the decode function is convoluted and hard to write.  Oracle added the case function to SQL starting in Oracle 10g to simplify this type of data transformation. The case statement is an easier for of the decode statement.  In its simplest form, the case statement is used to return a value when a match is found:

  (CASE commission_pct
    WHEN 0.1 THEN ‘Low’
    WHEN 0.15 THEN ‘Average’
    WHEN 0.2 THEN ‘High’
    ELSE ‘N/A’
  END ) Commission

In the above example, we see that we display “Low” when the commission percent is .1, “Average” when it is .15, and “High” when it is .2.  This syntax is far more readable than the cryptic decode function.

The case statement can also be used to perform complex Boolean matches. A more complex version is the Searched CASE expression where a comparison expression is used to find a match:

    WHEN job_id LIKE 'SA_MAN'  AND salary < 12000  THEN '10%'
    WHEN job_id LIKE 'SA_MAN'  AND salary >= 12000 THEN '15%'
    WHEN job_id LIKE 'IT_PROG' AND salary < 9000   THEN '8%'
    WHEN job_id LIKE 'IT_PROG' AND salary >= 9000  THEN '12%'
  END ) Raise

In this example we see that we test for specific job titles and salaries, and display a percentage based upon these values.

Custom BIFs

One of the most exciting features of SQL is the ability to create your own BIFs. In an exercise in this tutorial, you will create a custom BIF and use it in your SQL statements.  This ability to create your own BIFs is extremely powerful because it give you the ability to extend SQL in any way that you desire.

For more details, see the "Easy Oracle Series" a set of books especially designed by Oracle experts to get you started fast with Oracle database technology.

Download your Oracle scripts now:

The definitive Oracle Script collection for every Oracle professional DBA


Oracle performance tuning book



Oracle performance tuning software

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


email BC:

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

Oracle® is the registered trademark of Oracle Corporation.