Describe the purpose of built-in functions with Oracle SQL.
Since this portion of the performance tuning series is primarily concerned with improving the performance of programs and queries, we will confine our discussion to those Oracle built-in functions that can be used to make Oracle SQL queries run faster, although there are many other built-in functions available within Oracle.
There are several advantages to using OracleBIFs. Foremost, BIFs are language extensions, and therefore execute very quickly, far faster than using an external procedural program to perform this function. The only disadvantage to BIFs is that they are intrinsic to Oracle and cannot be ported to other databases such as DB2 or Informix.

Essentially, Oracle built-in functions (BIFs) are called immediately after the row data has been retrieved from the table and before the data has been displayed to the user, as the SlideShow below illustrates.

The goal and purpose of the built-in functions is to make SQL queries do more than just deliver the data. Built-in functions can reformat and manipulate Oracle data in ways that would be very difficult to achieve in a procedural program.

For example, the following query uses the built-in DECODE function to change row variable into more meaningful data:

Select
   Emp_name,
   To_char(hire_date, ‘DD-MON-YYYY’),
   DECODE (state_code, ‘AZ’, ’Arizona’,
                       ‘CA’, ’California’,
                       ‘NC’, ’North Carolina’,
                       ‘WY’, ’Wyoming’,
                             ’NONE’)
from employee;
Barrett   03-Mar-1996    California
Burleson  04-JAN-1993    North Carolina
Dahlstrom 06-Feb-1999    NONE
3 rows selected.