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.
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:
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 Dynamic SQL
Imagine you need to select all of the values from every table in your schema that begin with EMP. In the diagram below, you will 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;
spool run_emp.sql;
select "select * from "||table_name|| " ; "
from dba_tables
where
table_name like 'EMP%';
spool off;
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.
The default file name extension of files generated by the SPOOL command is .lst. To change this extension, specify a spool file containing a period (.).
For example:
SQL> SPOOL query.txt
Here is the output from the command in the diagram:
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 with Oracle SQL
Oracle BIFs are especially useful for retrieving Oracle table columns where a transformation is required.
We generally see a BIF under the following conditions:
Using BIFs with Character Datatypes
With character datatypes, we commonly see BIFs used to transform character strings to remove case sensitivity. For example, here we can query on the last_name column without being concerned about case sensitivity:
select customer_stuff
from
customer
where upper(last_name) = 'JONES' ;
Transforming characters: Oracle provides the to_number, to_date, upper, lower, and substr BIFs for transforming character data at retrieval time.
Oracle date datatype: One of the most common uses of Oracle BIFs is the transformation of the Oracle date datatype. As you know, the Oracle date datatype stores both 1) the date and 2) the time down to the hundredths of a second.
Because of this high degree of precision, it is difficult to convert the date datatype to a character. However, we do have the nls_date_format session variable, which can be used to change an Oracle SQL statement's display format for all dates. Surprisingly, the nls_date_format also affects the execution plan for SQL statements.
Transforming dates: The to_char BIF is extremely useful for transforming Oracle date datatypes.
The to_char BIF is used with dates to extract specific days, months, and years.
Of course, you know that the remedy for the problem of non-index usage when a query contains a BIF is to create a function-based index to match the predicate in the where clause of the SQL. However, there are some subtle surprises when using BIFs.
Let us take a closer look at each of these transformation types and see how they change SQL execution.
The `TO_CHAR` function is a powerful tool for formatting dates in Oracle SQL, including extracting individual components like days, months, and years.
Here's how it works:
Syntax:
TO_CHAR(date_value, 'format_model')
date_value: The date you want to format or extract components from.
format_model: A string specifying how you want the date displayed.
Format Model Elements for Date Extraction:
| Element | Description | Example |
| ---------- | -------------------------------------------------------- | -------------------- |
| DD | Day of the month (01-31) | 'DD' -> 15 |
| D | Day of the week (1-7) | 'D' -> 3 |
| DY | Abbreviated name of the day (Sun, Mon, etc.) | 'DY' -> Wed |
| DAY | Full name of the day (Sunday, Monday, etc.) | 'DAY' -> Wednesday |
| MM | Month number (01-12) | 'MM' -> 08 |
| MON | Abbreviated name of the month (Jan, Feb, etc.) | 'MON' -> Aug |
| MONTH | Full name of the month (January, February, etc.) | 'MONTH' -> August |
| YY | Two-digit year | 'YY' -> 24 |
| YYYY | Four-digit year | 'YYYY'-> 2024 |
Example:
SELECT TO_CHAR(SYSDATE, 'DD') AS "Day",
TO_CHAR(SYSDATE, 'MM') AS "Month",
TO_CHAR(SYSDATE, 'YYYY') AS "Year"
FROM DUAL;
Important Note: The displayed values of elements like the day of the week or the month name will depend on your database's language settings
(NLS parameters).
