Lesson 6	Using other Oracle-supplied extensions to Oracle SQL
Objective	Describe the purpose of some advanced built-in functions.

Using other Oracle-supplied extensions to Oracle SQL

The combination of Oracle's SQL*Plus and the BIFs provides a complete programming environment.
Let us conclude our discussion of Oracle SQL extensions with a review of some of the more obscure and powerful SQL BIFs.

String manipulation

Many of the extensions to Oracle SQL can save the programming staff a great deal of external computation and the GREATEST and LEAST functions are great for evaluating character string functions.

SQL> SELECT GREATEST ('Bach', 'Brahms', 'Grieg')
  2  "Greatest" FROM DUAL;
Greatest
---------
Grieg

String Translation

In addition to the DECODE function, we also have the TRANSLATE BIF to translate individual characters.
In the following example, we transpose letters and characters. We change 0 to 9, A to Z, and so on:

SELECT TRANSLATE('BURLEEZE', '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',
'9876543210zyxwvutsrqponmlkjihgfedbca')
  "Translate example"
  from dual;
Translate
--------
yfiovhlm

Date translation

In addition to the MONTHS_BETWEEN BIF, we have a wealth of other powerful date translation BIFs within Oracle SQL. In the following example, we use the LAST_DAY BIF to compute the last day of the specified month and the number of days between today and the end of the month:

SELECT SYSDATE,
LAST_DAY(SYSDATE) "Last",
LAST_DAY(SYSDATE) - SYSDATE "Days Left"
FROM DUAL;
SYSDATE   Last       Days Left
--------- --------- ----------
12-OCT-99 31-OCT-99         19

Dynamic SQL

Another nice feature of Oracle SQL is the ability to generate and execute SQL statements. In Oracle8i, we now have true dynamic SQL inside PL/SQL and Java, but in all versions of Oracle you can use SQL*Plus to create and execute SQL statements.

Oracle Supplied Extension

Imagine you need to select all of the values from every table in your schema that begin with EMP. In the MouseOver below, you’ll see that you can use the SPOOL command to write the data dictionary query to a file called run_emp.sql. Once created, you then execute the file by stating @run_emp.sql.

spool run_emp.sql;
select "select * from "||table_name|| " ; "
from dba_tables
where 
  table_name like 'EMP%';
spool off;

Spool the output of the command into run_emp.sql.
Execute the SQL that uses the “||” function to create the syntax for our table display.
Stop spooling.
Now execute the SQL in the file that you have just created by entering @run_emp.

spool run_emp.sql;

Other Supplied Extensions
Here is the output from the command in the MouseOver:

SQL> Select * from emp_dept;
Marketing
Finance
IS
3 rows selected.
SQL> Select * from employee
Norris Chuck
Ellison Larry
Carleton Fiorina
3 rows selected.

User-defined functions

You can also write your own user-defined functions, and use them in all SQL.
You can write these user-defined SQL functions in PL/SQL or Java and provide BIF functionality that is not available in the standard Oracle SQL BIFs. These user functions can appear in an SQL statement anywhere that other Oracle BIFs are placed. Obviously writing your own SQL functions is beyond the scope of this class, but it is worth knowing that Oracle SQL can be customized in an almost infinite variety of ways. The next lesson concludes this module.

Using BIFs Oracle SQL

Before you move on, click link below to read about using built-in functions.
Using BIFs Oracle SQL