Beginning PHP and Oracle From Novice to Professional by W. Jason Gilmore and Bob Bryla Chapter 40

Ensuring database availability is a critical skill you need even if your Oracle Database XE instance is used by a small group of developers in your department. Many types of database failures are beyond
your control as a DBA, such as disk failures, network failures, and user errors. This emphasizes the need to prepare in advance for all of these potential failures after assessing the cost of database down- time versus the effort required to harden your database against failure. Many of these failures, as you might expect, require you to work closely with the server system administrators and network admin- istrators to minimize the impact. You need to promptly receive notification when failures occur, or a warning when they are about to occur.
In this chapter, we start by presenting you with Oracle’s recommended best practices for ensuring the recoverability of your database when, not if, you have a database failure. If your database is a production database that must be available continuously, these requirements are mandatory. On the other hand, if your database is for development, an occasional backup may suffice. However, by using Oracle’s best practices, your downtime will be minimal in the event of a failure, giving you more time to focus on PHP application development instead of data recovery.
Next, we show you how to back up your database, using the Oracle Database XE scripts. Once you have backed up your database, you will need to know how to recover the database from a media failure such as a missing or corrupted datafile.

Backup and Recovery Best Practices

Oracle recommends several techniques you can use to ensure database availability and recoverability. Many of these techniques are automatically implemented when you install Oracle Database XE. However, there are a couple of places where you can tweak the default configuration to improve the recoverability further. We discuss these tweaks in the sections that follow.
Before we dig in to the recoverability and availability techniques, it is important to know the
types of failures you may encounter in your database so that you may respond appropriately when they occur. Database failures fall into two broad categories: media failures and nonmedia failures.
Media failures occur when a server disk or a disk controller fails and makes one or more of your database’s datafiles unusable (see Chapter 28 for an overview of Oracle Database XE’s storage struc- tures). After the hardware error is resolved (e.g., the server administrator replaces the disk drive), it is your responsibility to restore the corrupted or destroyed datafiles from a disk or tape backup. As the price of disk space falls, the added level of convenience and speed of disk makes tape backups less desirable except for archival purposes.
Nonmedia failures include all other types of failures. Here are the most common types of nonmedia failures and how you will deal with them:

687

• Statement failure: Your SQL statement fails because of a syntax problem, or your permissions do not allow you to execute the statement. The recovery process for fixing this error is relatively easy: use the correct syntax or obtain permissions on the objects in the SQL statement.
• Instance failure: The entire database fails due to a power failure, server hardware failure, or a bug in the Oracle software. Recovery from this type of failure is automatic: once the server hardware failure is fixed or the power is restored, Oracle Database XE uses the online redo log files to ensure that all committed transactions are recorded in the database’s datafiles. In the case of a possible Oracle software bug, your next step after restarting the database is to inves- tigate whether there is a patch file or a workaround for the software bug.
• Process failure: A user may be disconnected from the database due to a network connection failure or an exceeded resource limit (such as too much CPU time). The Oracle Database XE back- ground processes automatically clean up by freeing the memory used by the user connection and roll back any uncommitted transactions started during the user’s session.
• User error: A user may drop a table or delete rows from a table unintentionally.

Multiplexing Redo Log Files

As you remember from Chapter 28, the online redo log files are a key component required to recover from both instance failure and media failure. By default, Oracle Database XE creates the minimum number of redo log files (two). When the first redo log file fills with committed transactions, subse- quent transactions are written to the other redo log file. Whether you have two, three, or more redo log files, Oracle writes to the log files in a circular fashion. Thus, if you have ARCHIVELOG mode enabled, Oracle can write new transactions to the next redo log file while Oracle archives the previous online
log file. (We show you how to enable ARCHIVELOG mode in a coming section.)

■Note The terms redo log file and online redo log file are often used interchangeably. However, the distinction is
important when you are comparing online redo log files to archived (offline) redo log files.

To prevent loss of data if you lose one of the online redo log files, you can multiplex, or mirror, the redo log files. In other words, each redo log file, whether there are two, three, or more, has one or more identical copies. These copies are maintained automatically by Oracle processes. Writing to a specific log file occurs in parallel with all other log files in the group. While there is a very slight performance hit when the Oracle processes must write to two copies of the redo log file instead of just one, the slight overhead is easy to justify compared to the recovery time (including lost committed transactions) if a nonmultiplexed online log file is lost due to a hardware failure or other error. To see the current status of the online redo log files, start at the Oracle Database XE home page and navigate
to Administration ➤ Storage. In the Tasks section on the right side of the page, click View Logging
Status and you will see the names and status of the online redo log files. By default, Oracle Database
XE creates two online redo log files, as you can see in Figure 40-1.
Notice the directory path for the redo log files:

/usr/lib/oracle/xe/app/oracle/flash_recovery_area

Figure 40-1. Online redo log file status

This area, as you might surmise, is known as the Flash Recovery Area. The Flash Recovery Area automates the management for backups of all types of database objects such as multiplexed copies of the control file and online redo log files, archived redo log files, and datafiles. You specify the loca- tion of the Flash Recovery Area along with a maximum size, and Recovery Manager (RMAN) manages files within this area. You define the location and size of the Flash Recovery Area with two initializa- tion parameters. From the SQL command-line prompt run this command:

show parameter db_recov

You will see all parameters in the database that begin with db_recov:

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------ db_recovery_file_dest string /usr/lib/oracle/xe/app/oracle/
flash_recovery_area
db_recovery_file_dest_size big integer 10G

You can also view these parameters from the Home ➤ Administration ➤ About page in the Oracle Database XE GUI. To ensure prompt and easy recovery of any database object, your Flash Recovery Area should be large enough to hold at least one copy of all datafiles, incremental backups, online redo log files, control files, and any archived redo log files required to restore a database from the last full or incremental backup to the point in time of a media failure. You can check the status of the Flash Recovery Area by querying the dynamic performance view V$RECOVERY_FILE_DEST:

select name, space_limit, space_used from v$recovery_file_dest;

NAME SPACE_LIMIT SPACE_USED
-------------------------------------------------- ------------- ----------
/usr/lib/oracle/xe/app/oracle/flash_recovery_area 10737418240 851753472

Of the 10GB of space available in the Flash Recovery Area, less than 900MB is used. Multiplexing these redo log files is easy; the only catch is that there is no GUI interface available
for this operation—you must use a couple of SQL commands. You will put the multiplexed redo log files in the directory /u01/app/oracle/onlinelog. This file system is on a separate disk drive and a separate controller from the redo log files shown earlier in Figure 40-1. Connect as a user with SYSDBA privileges, and use these SQL statements:

alter database add logfile member
'/u01/app/oracle/onlinelog/g1m2.log' to group 1;
alter database add logfile member
'/u01/app/oracle/onlinelog/g2m2.log' to group 2;
Notice that you do not need to specify a size for the new redo log file group members; all files within the same redo log file group must have the same size, so Oracle automatically uses the file size of the files within the existing group. After you run these statements, you revisit the Database Logging page shown earlier in Figure 40-1, and you now see the same log file groups but with each having a multiplexed member, as shown in Figure 40-2.

Figure 40-2. Multiplexed online redo log files

Multiplexing Control Files

As you may remember from Chapter 28, the control file maintains the metadata for the physical structure of the entire database. It stores the name of the database, the names and locations of the tablespaces in the database, the locations of the redo log files, information about the last backup of each tablespace in the database, and much more. It may be one of the smallest yet most critical files in the database. If you have two or more multiplexed copies of the control file and you lose one, it is a very straightforward recovery process. However, if you have only one copy and you lose it due to corruption or hardware failure, the recovery procedure becomes very advanced and time consuming.
By default, Oracle Database XE creates only one copy of the control file. To multiplex the control
file, you need to follow a few simple steps. First, identify the location of the existing control file using the Home ➤ Administration ➤ About Database page, or use the following query:

select value from v$parameter where name = 'control_files';

On Linux you will see something similar to the following:

VALUE
-----------------------------------------------------
/usr/lib/oracle/xe/oradata/XE/control.dbf

The next step is to alter the SPFILE (see Chapter 28 for a discussion on types of parameter files) to add the location for the second control file. We will use the location /u01/app/oracle/controlfile to store the second copy of the control file. Here is the SQL statement you use to add the second location:

alter system set control_files =
'/usr/lib/oracle/xe/oradata/XE/control.dbf',
'/u01/app/oracle/controlfile/control2.dbf' scope=spfile;

Be sure to use SCOPE=SPFILE here, as in the example, since you cannot dynamically change the
CONTROL_FILES parameter while the database is open. Next, you must shut down the database as follows:

shutdown immediate

On Linux, you use your favorite GUI or operating system command line to make a copy of the first control file in the second location:

cp /usr/lib/oracle/xe/oradata/XE/control.dbf \
/u01/app/oracle/controlfile/control2.dbf

Finally, restart the database using this command at the SQL> prompt:

startup

Checking the dynamic performance view V$PARAMETER again, you can see that there are now two copies of the control file:

VALUE
--------------------------------------------------------------------------------
/usr/lib/oracle/xe/oradata/XE/control.dbf,
/u01/app/oracle/controlfile/control2.dbf

As with the members of a redo log file group, any changes to the control file are made to all copies. As a result, the loss of one control file is as easy as shutting down the database (if it is not down already), copying the remaining copy of the control file to the second location, and restarting the database.

Enabling ARCHIVELOG Mode

A database in ARCHIVELOG mode automatically backs up a filled online redo log file after the switch to the next online redo log file. Although this requires more disk space, there are two distinct advantages to using ARCHIVELOG mode:

• After media failure, you can recover all committed transactions up to the point in time of the media failure if you have backups of all archived and online redo log files since the last backup, the control file from the most recent backup, and all datafiles from the last backup.
• You can back up the database while it is online. If you do not use ARCHIVELOG mode, you must shut down the database to perform a database backup. This is an important consideration when you must have your database available to users 24 hours a day, 7 days a week.

By default, an Oracle Database XE installation is in NOARCHIVELOG mode. If your database is used primarily for development and you make occasional full backups of the database, this may be suffi- cient. However, if you use your database in a production environment, you should use ARCHIVELOG mode to ensure that no user transactions are lost due to a media failure. To enable ARCHIVELOG mode, perform the following steps. First, connect to the database with SYSDBA privileges, and shut down the database:

shutdown immediate

Next, start up the database in MOUNT mode. This mode reads the contents of the control file and starts the instance but does not open the datafiles:

startup mount

ORACLE instance started.

Total System Global Area 146800640 bytes Fixed Size 1257668 bytes Variable Size 88084284 bytes Database Buffers 54525952 bytes Redo Buffers 2932736 bytes Database mounted.

Next, enable ARCHIVELOG mode with this command:

alter database archivelog;

Finally, open the database:

alter database open;

The Oracle Database XE home page’s Usage Monitor section now indicates the new status of the database, as you can see in Figure 40-3.

Figure 40-3. Database status after enabling ARCHIVELOG mode

After you perform one full backup of the database, the archived and online log files will ensure that you will not lose any committed transactions due to media failure. In addition, to save disk space you can purge (or move to tape and then purge) all archived redo log files and previous backups created before the full backup. Only those archived redo log files created since the last full backup are needed to recover the database when a media failure occurs; the combination of a full backup and subsequent archived redo log files will ensure that you will not lose any committed transactions. The previous full backups and subsequent archived redo log files created before the latest full backup will only be useful if you need to restore the database to a point in time before the most recent full backup.

Backing Up the Database

Now that you have multiplexed your online redo log files, multiplexed your control files, and enabled ARCHIVELOG mode in your database, you are ready for your first full backup of the database. Any media recovery operation requires at least one full backup of the database, even if you are not in ARCHIVELOG mode. (Remember that an instance failure requires only the online redo log files for recovery.) You can back up manually, or schedule an automatic backup at regular intervals. We cover both of these scenarios in the following sections.

Manual Backups
Whether you are using Linux or Windows as your operating system, performing a manual backup is very straightforward. Under Linux, start with the Applications menu under Gnome, or the K menu if you’re using KDE, select Oracle Database 10g Express Edition ➤ Backup Database. For Windows, from the Start menu, select Programs ➤ Oracle Database 10g Express Edition ➤ Backup Database. In both cases, a console window launches so that you can interact with the backup script. This inter-
action occurs only if you are not in ARCHIVELOG mode. The backup script warns you that Oracle will shut down the database before a full backup can occur.
For a full backup under Linux, the output in the console window looks similar to if not exactly
like this:

Doing online backup of the database. Backup of the database succeeded.
Log file is at /usr/lib/oracle/xe/oxe_backup_current.log.
Press ENTER key to exit

The script output identifies the log file location. Oracle keeps the two most recent log files. The previous log file is at this location:

/usr/lib/oracle/xe/oxe_backup_previous.log

The log file contains the results of one or more RMAN sessions. After the backup completes, RMAN deletes all obsolete backups. By default, Oracle only keeps the last two full backups. If you are using Windows as your host operating system, the backup logs reside in these locations:

C:\ORACLEXE\APP\ORACLE\PRODUCT\10.2.0\SERVER\DATABASE\OXE_BACKUP_CURRENT.LOG C:\ORACLEXE\APP\ORACLE\PRODUCT\10.2.0\SERVER\DATABASE\OXE_BACKUP_PREVIOUS.LOG

Automatic Backups

Scheduling automatic backups is very straightforward. Oracle Database XE provides a script for each platform that you can launch using your favorite scheduling program, such as the cron program under Linux or the Scheduled Tasks wizard under Windows.
For Linux, the script is located here:

/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/config/scripts/backup.sh

For Windows, the script is located here:

C:\oraclexe\app\oracle\product\10.2.0\server\BIN\BACKUP.BAT

The log files for each platform are located in the same location as if you ran the scripts manually.

Recovering Database Objects

Eventually disaster will strike and you will lose one of your key database files, either a datafile, a control file, or an online redo log file, due to a hardware failure or an administrator error. In the following scenario, one of the datafiles is accidentally deleted and you must recover the database back to the point of time where the database failed due to the missing datafile.
In a default Oracle Database XE installation, you have four datafiles. You can use the dynamic performance view V$DATAFILE to identify these datafiles:

select name from v$datafile;

NAME
--------------------------------------------------
/usr/lib/oracle/xe/oradata/XE/system.dbf
/usr/lib/oracle/xe/oradata/XE/undo.dbf
/usr/lib/oracle/xe/oradata/XE/sysaux.dbf
/usr/lib/oracle/xe/oradata/XE/users.dbf

The system administrator performs some routine disk space reclamation and accidentally deletes one of the datafiles on the Linux server:

rm /usr/lib/oracle/xe/oradata/XE/users.dbf

You immediately get phone calls from your users because all user tables are stored in the USERS tablespace, which in turn is stored in the operating system file /usr/lib/oracle/xe/oradata/XE/ users.dbf. A user reports seeing the error message shown in Figure 40-4 when she tries to browse the contents of one of her tables.

Figure 40-4. User error messages after the loss of a datafile

Your first thought is that there must be some error other than a missing datafile, so you first try to shut down and restart the database to see what happens. The startup messages look normal at first, but then after the database is mounted you see an error message similar to the following:

ORACLE instance started.

Total System Global Area 146800640 bytes Fixed Size 1257668 bytes Variable Size 88084284 bytes Database Buffers 54525952 bytes Redo Buffers 2932736 bytes Database mounted.
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/usr/lib/oracle/xe/oradata/XE/users.dbf'

You decide that a database recovery is your only option. From either the Windows or Linux GUI interface, select Restore Database from the same menu where you selected Backup Database, as noted earlier in the chapter, to back up the database. A command window opens to ensure that you
know a shutdown must occur to restore the database to its previous state:

This operation will shut down and restore the database. Are you sure [Y/N]?

After you type Y, the restore operation proceeds with no further intervention other than to confirm that the operation is complete:

This operation will shut down and restore the database. Are you sure [Y/N]?y
Restore in progress...
Restore of the database succeeded.
Log file is at /usr/lib/oracle/xe/oxe_restore.log. Press ENTER key to exit

The restore operation automatically starts the database after completion. If you are curious as to which RMAN commands were used to recover from the media failure (deleted datafile), you can look in the log file identified by the script at /usr/lib/oracle/xe/oxe_restore.log.
When the user reloads the Web page containing her Oracle Database XE session, she suddenly sees the table she was attempting to browse, as shown in Figure 40-5.
The user didn’t even have to log out and log back in; when the database was back up (after being
shut down and restarted several times, including an attempt by the DBA to shut down and restart), refreshing the page logged the user back in behind the scenes and kept her on the page where she left off. She was none the wiser about the multiple database shutdowns and restarts; all of her committed transactions are still in the database as well.

Figure 40-5. Refreshed Web page after media recovery

Summary

This chapter gave you the basics for backing up and recovering your database. Although backup and recovery operations are not the most glamorous of tasks compared to application development, a database that is down because of a disk failure quickly becomes highly visible to upper management when the PHP developers (including yourself) are not able to store and retrieve application data. Implementing Oracle’s best practices to ensure database availability include multiplexing redo log files and control files, enabling ARCHIVELOG mode to ensure recoverability from media failure, and leveraging the Flash Recovery Area to quickly recover from media failure or user error.

Beginning PHP and Oracle From Novice to Professional by W. Jason Gilmore and Bob Bryla Chapter 39

Rarely is your database self-contained. You may have to create a spreadsheet for the accounting department so it can merge data in the database with its existing spreadsheets, or you may need to
import a text file generated from a reporting or tracking tool into one of your database tables. There- fore, you need to be fluent in the use of Oracle Database XE’s import and export capabilities.
In this chapter, we show you a couple of ways to export data from your database tables to an external destination using the SQL*Plus SPOOL command, and of course the similar options available in the GUI. On the flip side, we show you how to use the Oracle Database XE GUI tools to import data from a text file or a spreadsheet.

Exporting Data

Most likely, the departments at your company use a variety of tools to manage their data, such as Excel for spreadsheets or a custom Java application that uses text files for input or output. Invariably, they need data from your database. You have a number of tools available to satisfy these requests, ranging from the very basic SPOOL command in SQL*Plus to the convenience of the export options available in the Oracle Database XE GUI.

Using the SPOOL Command

If you have ever used the command-line SQL*Plus utility, you may have wondered how to capture the output from the SQL commands you type, short of using a GUI-based cut-and-paste utility. The SPOOL command simplifies this process.
In our example, the IT department employees are overworked, so the employee relations depart-
ment is giving each IT department employee free movie tickets. Therefore, you must capture employee information for employees in the IT_PROG department and send it to the employee relations depart- ment in a format suitable for import into Microsoft Excel so the employee relations department can track the movie ticket expenses. First, connect to Oracle Database XE as the HR user as follows:

sqlplus hr/hr

SQL*Plus: Release 10.2.0.1.0 - Production on Sun Mar 18 20:59:09 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

SQL>

675

Typically when you use SQL*Plus for ad hoc queries, you want to see column headers. In this case, you do not need column headers or a row count summary to import into Excel, so you use the SET command to turn these off:
set heading off set feedback off

To see all SET options within SQL*Plus, just type HELP SET. Finally, you want to capture the output to a file, so you use the SPOOL command to specify the destination location for the output file:

spool /tmp/it_empl.csv

Next, you run the query as follows, inserting commas between fields to make the file suitable for importing into Excel as a CSV formatted file:

select employee_id || ',' || last_name || ',' || first_name || ',' || email from employees
where job_id like 'IT_%';

Finally, turn off the SPOOL command as follows:

SPOOL OFF

Note the || operator in the SELECT statement; it is the concatenation operator in an Oracle expres- sion. The || operator combines the variables on each side of the operator into a single string value. If the variables on either side of the operator are not a VARCHAR2 or a CHAR variable (such as NUMBER or DATE), the variables are converted to a VARCHAR2 value before concatenating them.
The output file from the query looks like this:

103,Hunold,Alexander,AHUNOLD
104,Ernst,Bruce,BERNST
105,Austin,David,DAUSTIN
106,Pataballa,Valli,VPATABAL
107,Lorentz,Diana,DLORENTZ

SQL> spool off

The only other required step before you send the file to the employee relations department is to trim out the blank lines and the line that has the SPOOL OFF command.

Exporting Using GUI Utilities
As you might expect, the Oracle Database XE GUI interface can produce the same results as the SPOOL command. From the Oracle Database XE home page, navigate to Utilities ➤ Data Load/Unload ➤ Unload ➤ Unload to Text. You will see the page shown in Figure 39-1.

■Note For external applications or systems that support it, Oracle Database XE can export your tables to XML
format in addition to a flat file text format.

Figure 39-1. The Oracle Database XE administration home page

Select the schema you want to export from. Since you are exporting an HR table and you are logged in as the HR user, the default is appropriate. Click the Next button and select the EMPLOYEES table, as shown in Figure 39-2. Click the Next button.

Figure 39-2. Selecting the table to export to text format (CSV or TXT)

In the dialog shown in Figure 39-3, click each column to export—in this case, EMPLOYEE_ID,
LAST_NAME, FIRST_NAME, and EMAIL.

After you click the Next button, specify other options for the exported file, such as the character that separates each column, whether to enclose each column with another character such as double quotes, and the output file format (DOS or Unix). Be sure to specify the file format corresponding to your browser’s platform. If Oracle Database XE is running on Linux, but your browser is running on Windows, specify DOS as the platform. By checking the Include Column Names box, the first line of the exported file contains the column names corresponding to the exported columns. In the example shown in Figure 39-4, you specify a comma as the separator and the file format as DOS.

Figure 39-4. Specifying the format of the text output

When you click the Unload Data button shown in Figure 39-4 on a Windows platform, Windows prompts you to either open the file with the default application (in this case, Notepad) or to save the file. In Figure 39-5, the Firefox Web browser asks you what to do with the file. Accept the default, open the file directly with Notepad, and click OK.

Figure 39-5. Specifying the format of the text output

Figure 39-6 shows the exported EMPLOYEES data in a Notepad window.

Figure 39-6. Text output of the EMPLOYEES table using the Oracle Database XE GUI

If you are using a browser on Linux, the export procedure is nearly identical until you click the Unload Data button. For the Firefox browser, you see the same prompt as you do on Windows except for the viewer application. As you can see in Figure 39-7, Linux does not have Windows Notepad.

Figure 39-7. Saving the exported text output using Firefox on Linux

The default text editor on this Linux workstation is gedit, and as you can see in Figure 39-8, the exported text file looks identical to the text file you exported from a Windows-based browser.

Figure 39-8. Viewing the exported EMPLOYEES table on Linux

Whether you use the SQL*Plus SPOOL command or the GUI depends on a couple of factors. The SPOOL command is a bit more work because you have to write a query, but you can filter your query as you please. The Oracle Database XE GUI export does not have a filtering option but gives you a few more options such as headers and target platform format (DOS or Unix/Linux).

Importing Data

Your life would be a lot easier if everyone used an Oracle database. Exchanging data would be consider- ably easier using Oracle Database XE’s native export and import commands (using these commands is beyond the scope of this book). The reality is that you will need to import data into your database from a variety of sources, such as text files, spreadsheets, and other database and application formats, such as XML.
In the following example, a legacy application collects anonymous comments about other
employees on a Web page and saves them in a spreadsheet. The spreadsheet contains only the employee number, the date of the comment, and the comment itself. To help management more accurately interpret the comments, you must import this spreadsheet data into the database and

join the new table to the existing EMPLOYEES table to pull the employee name and e-mail address. The spreadsheet that we will import is EmployeeComments.csv and you can see it in Figure 39-9.

Figure 39-9. Employee comments spreadsheet

From the Oracle Database XE home page, navigate to Utilities ➤ Data Load/Unload ➤ Load and you will see the options, shown in Figure 39-10: Load Text Data, Load Spreadsheet Data, and Load XML Data.

Figure 39-10. Data Load import type options

Click Load Spreadsheet Data and you see the options in Figure 39-11. You will load this spread- sheet to a new table and the spreadsheet will be loaded from an external file instead of using the operating system’s cut-and-paste function.

After you click the Next button, shown in Figure 39-11, you specify the location of your spread- sheet as shown in Figure 39-12 as well as the field delimiter and whether the first row of the spreadsheet contains the column names. Reviewing the contents of the employee comments spreadsheet, shown in Figure 39-9, you surmise that the spreadsheet’s first row contains well-constructed column names, so you leave the First Row Contains Column Names checkbox checked.

Figure 39-12. Data Load source format options

After you click the Next button, shown in Figure 39-12, you finalize the data import by specifying the new table name as well as the datatypes for each column to import. Oracle Database XE makes a first guess as to the columns’ datatypes. In the dialog shown in Figure 39-13, you specify EMPLOYEE_COMMENTS as the table name and adjust the datatypes to match the data in the spreadsheet. Oracle Database XE provides a few sample rows of the spreadsheet to help you determine the datatype and whether to import the column at all.

Figure 39-13. Data Load table name and column name format options

When you click the Next button shown in Figure 39-13, you see the last screen before the data import occurs and you will specify the primary key of your new table. You do not want to use EMPLOYEE_NUMBER as the primary key because the imported table may have more than one comment per employee. Therefore, you direct Oracle Database XE to create a new column, EMPLOYEE_COMMENTS_NUMBER, for the primary key, as shown in Figure 39-14. Oracle Database XE uses a new sequence, EMPLOYEE_COMMENTS_SEQ, to populate the primary key column. See Chapter 30 for more information on how to create and use sequences.

Figure 39-14. Data Load table name and column name format options

The moment you have been waiting for has finally arrived. When you click the Load Data button, Oracle Database XE creates the table and loads the spreadsheet data into the table. Figure 39-15 shows the status of the table including how many rows imported successfully and unsuccessfully.

Figure 39-15. Data Load results and status

Browsing to Home ➤ Object Browser, you see the table EMPLOYEE_COMMENTS among the other tables owned by HR in Figure 39-16.

Figure 39-16. Browsing the contents of the EMPLOYEE_COMMENTS table

You can now use the table EMPLOYEE_COMMENTS as you would any other table in your schema. For example, to show the name and e-mail address of the employees referenced in the EMPLOYEE_COMMENTS table, you can join the EMPLOYEES table to the EMPLOYEE_COMMENTS table using this query:

select employee_number, last_name, first_name, email, comment_date, comment_text
from employees e join employee_comments ec
on e.employee_id = ec.employee_number order by employee_number, comment_date
;

You can see the results of the query in Figure 39-17.

Figure 39-17. Query results from joining EMPLOYEES to EMPLOYEE_COMMENTS

Summary

This chapter gave you a whirlwind tour of some of the basic ways you can get data into your database from other data sources as well as export data to text files, spreadsheets, or other databases. Although you should be able to use the Oracle Database XE GUI utilities on a regular basis to import and export text files and spreadsheets, we also showed you how to use the SPOOL command with the SELECT statement when you can’t get to a Web browser and you want to perform your export operation using a batch job.
In the next and final chapter we make sure you can easily back up your data as well as recover your data in the event of a disaster of some type—when, not if, some kind of failure occurs in your database.

Beginning PHP and Oracle From Novice to Professional by W. Jason Gilmore and Bob Bryla Chapter 38

In Chapter 30, we presented table constraints such as PRIMARY KEY and CHECK. In that same chapter, we introduced unique indexes as a way to enforce a PRIMARY KEY constraint. In addition to using
indexes to enforce constraints, you can use indexes to boost the performance of queries significantly by reducing the amount of time needed to retrieve rows from a table instead of reading every row in the table to find the row or rows you are looking for. However, too many indexes on a table can be just as bad as not enough.
In this chapter, we delve more deeply into how to use indexes most effectively, how to manage indexes, and how to monitor index usage. Finally, we show how you can use the Oracle Database XE GUI to see the structure of the indexes in the database and create domain indexes, another type of Oracle index.

Understanding Oracle Index Types

Also in Chapter 30, we introduced two types of indexes: B-tree and bitmap. They both accomplish a common goal: reducing the amount of time required to retrieve rows from a table. However, they are constructed differently, and you choose one or the other based on the existing and expected type and distribution of the data in the column or columns to be indexed. Unless your tables are very small, your queries will benefit from indexed columns. Traversing an index to find a particular row or many rows using the conditions in the WHERE clause will typically take less time than reading every row of the table itself.
Indexes are both logically and physically independent of the rows in the indexed tables. The
indexes themselves can be dropped and added without affecting the table data or the queries you use against the tables (except for affecting the performance of the query). Oracle automatically main- tains entries in an index as rows in the indexed table are added, modified, or deleted. When you drop a table, Oracle drops all associated indexes as well.

■Note We discuss another type of index, a domain index, later in this chapter in the section “Using Oracle Text.”

In the following sections, we give you a bit more detail about B-tree and bitmap indexes and how they are constructed. B-tree indexes have several subtypes. We identify and explain each of the subtypes and when to use them.

661

B-tree Indexes

B-tree indexes are the most common type of index; they are created by default if you do not specify a type in the CREATE INDEX statement. B-tree, which stands for balanced-tree, looks like an inverted tree with two types of blocks: branch blocks and leaf blocks. Figure 38-1 provides a high-level view of a B-tree index with a depth of three levels: two for the branch blocks and one for the leaf blocks. The leaf blocks are always one level deep. Branch blocks contain partial keys and pointers to other branch blocks or leaf blocks. The leaf blocks contain the pointers (ROWIDs) to the actual row of data containing the indexed column or columns.

Figure 38-1. A B-tree index

The performance of a B-tree index is consistent regardless of the row you are searching for. Because the tree is always balanced, the search of the tree for a given column’s key value will always traverse the same number of levels in the tree to find the leaf block with the row you are looking for.
Here are the different types of B-tree indexes:

• Unique: By default, Oracle creates a nonunique index unless you specify the UNIQUE keyword in your CREATE INDEX statement. As the name implies, there are no duplicate values in a unique index. Oracle uses unique indexes to enforce a primary key (PK) constraint.
• Reverse: A reverse key index stores key values in reverse order. If an indexed column contains ascending values, a reverse key index may improve performance by reducing the contention on a particular leaf block. For example, a regular index will likely store the row pointers for rows with values 101456, 101457, and 101458 in the same leaf block. In contrast, a reverse key index will most likely store row pointers for 654101, 754101, and 854101 in different leaf blocks.
• Function-based: A function-based index is created on an expression containing one or more columns in a table instead of just the columns themselves. For example, you may create a function-based index on UPPER(LAST_NAME) to facilitate case-insensitive searches on the LAST_NAME column while avoiding a full table scan.
• Index-organized: An index-organized table (IOT) is a special type of B-tree index that stores both the index and the data within the same database segment. This may save many I/O opera- tions for lookup tables, tables with only a few columns, or tables that are relatively static.

Bitmap Indexes

A bitmap index uses a string of binary ones and zeros to represent the existence or nonexistence of a particular column value in any row of a table. For each distinct value of a column in a table, a bitmap index stores a string of binary ones and zeros with a length of the number of rows in the table. This makes your index storage requirements very low as long as the cardinality (the number of distinct values) of the column is low.
Queries using AND and OR conditions that compare several columns with bitmap indexes are very efficient. This also applies to joining multiple tables on columns with bitmap indexes. Table 38-1 shows a few rows from the EMPLOYEES table along with the bitmap indexes for the GENDER column, a typical low-cardinality column. Since the cardinality of the GENDER column is 2, the index maintains
two bitmaps. For any given row, only one bit in the corresponding bitmaps is a 1; the rest are 0.

Table 38-1. Bitmap Index on Gender in the EMPLOYEES Table

Employee Name Gender Bitmap for M Bitmap for F
Karen Colmenares F 0 1
Adam Fripp M 1 0
Shanta Vollman F 0 1
Julia Nayer F 0 1
Irene Mikkilineni F 0 1
Laura Bissot F 0 1
Steven Markle M 1 0
Alexander Khoo M 1 0
Oliver Tuvault M 1 0

Creating, Dropping, and Maintaining Indexes

You use the CREATE INDEX statement to create a B-tree or bitmap index. The basic syntax looks like this:

CREATE [BITMAP | UNIQUE] INDEX indexname
ON tablename (column1, column2, ...) [REVERSE];

If you do not specify BITMAP, Oracle assumes a B-tree index. The UNIQUE keyword ensures that the index will not contain duplicate values. The REVERSE keyword creates a reverse key index, discussed in the previous section. The name of the index must be unique among all indexes within a schema (user). However, the namespace for indexes is different from the namespace for table names. This means you could create an index named EMPLOYEES on the LAST_NAME column of the EMPLOYEES table. This may lead to confusion, though, especially if you want to have more than one index on the EMPLOYEES table. One possible naming convention is to include the table name, the column name, and the index type in the index name, as in this example:

CREATE INDEX employees_last_name_ix ON employees(last_name);

Dropping an index is quite intuitive if you are familiar with other Oracle Database XE statements. Use the DROP INDEX statement like this:

DROP INDEX employees_last_name_ix;

Using the Oracle Database XE GUI makes it even easier to create an index; no knowledge of syntax is required. However, it is good to know the syntax when you are (infrequently) stuck with only a SQL command-line interface. Start at the Oracle Database XE home page, click Object Browser, and select Indexes in the drop-down box at the top of the left navigation pane. Logged in as the user HR, you will see the indexes owned by HR. Clicking the EMP_EMP_ID_PK index name in the left navigation area shows you the details for the primary key (unique) index on the EMPLOYEE_ID column of the EMPLOYEES table in Figure 38-2.

Figure 38-2. Browsing indexes owned by HR

In the following scenario, your queries against the EMPLOYEES table seem to be slow (or at least your users tell you they are slow). You suspect it is because you might not have a column indexed. Here is a typical management query against the EMPLOYEES table:
select * from employees where salary > 5000 order by salary desc;

Entering this query in the SQL Commands window and clicking the Explain tab shows you how Oracle Database XE accesses each of the tables in the query, along with a list of the indexed columns and the table columns, as you can see in Figure 38-3.

Figure 38-3. Using the Explain function on the SQL Commands page

It seems clear from the Explain tab on the SQL Commands window that Oracle Database XE reads the entire EMPLOYEES table when you filter by the SALARY column. This line in the Query Plan section of Figure 38-3 spells it out for you:

TABLE ACCESS FULL EMPLOYEES

Therefore, you decide to create a nonunique B-tree index on the SALARY column. From the Create drop-down box shown earlier in Figure 38-2, select Index. Enter the EMPLOYEES table name in the Table Name box, or select it from the drop-down button to the right of the box. You will see the page shown in Figure 38-4. For Type of Index, be sure that the Normal radio button is selected. We talk about text-based indexes in the “Using Oracle Text” section later in this chapter. Click the Next button.

Figure 38-4. Specifying the table name

You decide to keep Oracle’s suggested name for the index as EMPLOYEES_IDX1, as shown in Figure 38-5. The index will not be unique (many employees will have the same salary), and you select SALARY as the indexed column in the Index Column 1 drop-down box.
Click the Next button and you see a confirmation page. Click Finish to create the index. The new
index appears in the list on the Object Browser page shown in Figure 38-6.

Figure 38-5. Specifying indexed columns

Figure 38-6. Reviewing the details of the new index, EMPLOYEES_IDX1.

Monitoring Index Usage

Too many indexes on a table is bad for two reasons. First, an index occupies disk space that might otherwise be better used elsewhere. Second, indexes must be updated whenever you add, delete, or modify rows in a table. So how can you be sure that an index is even used? As of Oracle9i, you can use the dynamic performance view V$OBJECT_USAGE (see Chapter 35 for more information on dynamic performance views) to track whether an index has been used for a given time period.
To turn on monitoring, use the ALTER INDEX <index name> MONITORING USAGE command on the new index, as follows:

ALTER INDEX employees_idx1 MONITORING USAGE;

Right after running this command, check the view V$OBJECT_USAGE to make sure the index is being monitored:

SELECT index_name, table_name, monitoring, used, start_monitoring
FROM v$object_usage WHERE index_name = 'EMPLOYEES_IDX1';

The results from this query are shown in Figure 38-7. Notice that the column USED will be set to YES whenever Oracle uses the index to access rows in the EMPLOYEES table when you use SALARY in the WHERE clause; initially, this column is set to NO until the index is used.

Figure 38-7. Querying V$OBJECT_USAGE for index status

Now that the index is being monitored, wait a day, or long enough for the regular business cycles to complete at least once, and query this view again. If the USED column has a value of YES, as in Figure 38-8, you should probably keep the index.

Figure 38-8. Querying V$OBJECT_USAGE for index status after index usage

In any case, once you determine the index usage statistics, turn off the monitoring of the index using the NOMONITORING keyword:

ALTER INDEX employees_idx1 NOMONITORING USAGE;

Oracle incurs a slight overhead for every access to the EMPLOYEES table if one of its indexes is being monitored; so if you do not need to monitor it, turn it off. Note also that since V$OBJECT_USAGE is a dynamic performance view, its contents are not retained after the database is shut down and restarted.

Using Oracle Text

A standard index, like the ones we created in previous chapters and earlier in this chapter, helps you quickly access numeric values in one or more columns in the database. For text fields, you can create an index on the entire text field. When you query a text field, Oracle uses the index when you specify a comparison operator in the WHERE clause such as the following query against the TICKET table created in Chapter 37:

SELECT ticket_id, username, title, description FROM ticket
WHERE title = 'Login problems';

However, what if you want to search for any ticket with the word problems in the title? You could certainly use the LIKE clause, as follows:

SELECT ticket_id, username, title, description FROM ticket
WHERE title LIKE '%problems';

The problem is Oracle can leverage an index only if the search string is exact or the wildcard character is at the end of the search string, as in these examples:

WHERE title LIKE 'Login problems'; WHERE title LIKE 'Login%';

Otherwise, if the wildcard is at the beginning of the string, Oracle cannot leverage an index. Here is an example of a search string with the wildcard character at the beginning:

WHERE title LIKE '%problems';

Oracle cannot use an index on the TITLE column and will perform a full table scan to find the requested search string. The same performance hit occurs when you want to perform a case-insensitive search. Oracle makes it easy to create indexes on relatively static unstructured text documents such as Microsoft Word documents, Web sites, or digital libraries. To address searches beyond the capa- bilities of basic Oracle indexes, you can use an advanced indexing feature called Oracle Text. For example, Oracle Text can easily search for the word key near the word stuck in a sentence within a document or a Web page, excluding sentences with the word printer near the word stuck; this partic- ular search is useful if you want to find keyboard issues and not printer jams in your support ticket database.
An Oracle Text index is another category of indexes called domain indexes, which typically are
supported by PL/SQL packages and involve much more logic and processing overhead than B-tree or bitmap indexes. Oracle Text queries usually consist of words or phrases. Numeric or date/timestamp columns are best indexed by standard B-tree or bitmap indexes introduced earlier in the chapter. Although a complete discussion of Oracle Text is beyond the scope of this book, you can get a good feel for the capabilities of Oracle Text by trying out the examples in the following paragraphs.
By default, no nonprivileged accounts have access to Oracle Text, so you will have to run a few SQL statements for the user that will create the Oracle Text indexes. The first step is to use a privileged account to grant the role CTXAPP to the HR account, as follows (see Chapter 31 for more information on privileges and roles):

GRANT CTXAPP TO HR;

Next, grant privileges on the Oracle Text packages to the HR account using these GRANT statements:

GRANT EXECUTE ON CTXSYS.CTX_CLS TO hr; GRANT EXECUTE ON CTXSYS.CTX_DDL TO hr; GRANT EXECUTE ON CTXSYS.CTX_DOC TO hr; GRANT EXECUTE ON CTXSYS.CTX_OUTPUT TO hr; GRANT EXECUTE ON CTXSYS.CTX_QUERY TO hr; GRANT EXECUTE ON CTXSYS.CTX_REPORT TO hr; GRANT EXECUTE ON CTXSYS.CTX_THES TO hr; GRANT EXECUTE ON CTXSYS.CTX_ULEXER TO hr;

The HR account user is now free to create and drop any type of Oracle Text index without any further intervention from the DBA. To create an Oracle Text index, the syntax is similar to a standard CREATE INDEX with the addition of the INDEXTYPE clause:

CREATE INDEX indexname
ON tablename (column) INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS ('context_parameter1 context_parameter2 . . . ')
;

■Note The CONTEXT index type is an index on a single text column. The other available index types in the CTXSYS
package are CTXCAT for combinations of a text field and one or more other columns, and CTXRULE to associate English-language query terms with document categories—for example, associating vanilla, chocolate, and straw- berry with the category ice cream.

The PARAMETERS clause is specific to the index type. These parameters can specify the type of text stored, how to search the text, and whether the text index is refreshed automatically when the value of the indexed text column changes due to an UPDATE or INSERT.
For our problem ticket table in Chapter 37, we will create an Oracle Text index on the
DESCRIPTION column using this CREATE INDEX statement with no parameters:

CREATE INDEX ticket_desc_ix ON ticket(description) INDEXTYPE IS CTXSYS.CONTEXT;
You search a CONTEXT index by using the CONTAINS clause, as in this example:

select * from ticket
where contains(description,'sTuCk') > 0;

The results of the query are shown in Figure 38-9. Note that the search string can be any case;
by default, the search is case insensitive.

Figure 38-9. Results from an Oracle Text CONTEXT index search

CONTEXT text indexes are not automatically synchronized (unlike B-tree, bitmap, and other Oracle Text index types) when rows are changed, deleted, or added. You must use the procedure CTX_DDL.SYNC_INDEX to refresh the index at the desired interval. The reason for using this index type on static tables is because the refresh operation on a CONTEXT index can be significantly higher than creating a new B-tree or bitmap index, and of course becomes inconsistent the moment you perform any DML statements on the table. In this example, you manually refresh the index TICKET_DESC_IX using the following anonymous PL/SQL block:

BEGIN CTX_DDL.SYNC_INDEX('ticket_desc_ix');
END;

You can accomplish many of the previous tasks using the Oracle Database XE GUI. First, drop the CONTEXT index with this SQL statement, and we will recreate it later:

DROP INDEX ticket_desc_ix;

Next, start from the Object Browser page shown earlier in Figure 38-2, click the Create button, and select Index. Enter TICKET for the Table Name and select the Text radio button, as shown in Figure 38-10.

Figure 38-10. Specifying the table name for a Text index in the Object Browser

After you click the Next button, you are on the page shown in Figure 38-11 and you specify the single text column you wish to index as well as the option to change the default index name, TICKET_CTX1.
After you click the Next button, you see a confirmation page. Click Finish to create the index.
The new index appears in the list on the Object Browser page shown in Figure 38-12.

■Tip On every confirmation page, you can click the SQL button to see the SQL statements that the GUI application
will use to create the object.

Figure 38-11. Specifying the index name and indexed column for a CONTEXT index

Figure 38-12. Using the Object Browser to view the definition of the CONTEXT index

Summary

This chapter continued the discussion of indexes from Chapter 30, explaining how to optimize the use of indexes by creating the right index for the job, as well as how to decide whether you need to create a specific index at all. We also showed you how to most effectively use the Oracle Database XE GUI to view and maintain indexes in your database. Finally, we showed you how to use Oracle Text to easily and efficiently find a string in almost any type of unstructured data in your database.
In the next chapter we’ll break free of the confines of your database and show you how to get data both in and out of your database: importing from spreadsheets, exporting to a text file, or exchanging data with other instances of Oracle Database XE.

Beginning PHP and Oracle From Novice to Professional by W. Jason Gilmore and Bob Bryla Chapter 37

Atrigger is a block of Oracle PL/SQL code that executes in response to some predetermined event. Specifically, this event involves inserting, modifying, or deleting table data, and the task can occur
either prior to or immediately following any such event. This chapter introduces triggers, one of Oracle’s key features that supplement what you cannot easily accomplish with Oracle’s built-in referential integrity features.
This chapter first introduces you to triggers, offering general examples that illustrate how you
can use them to carry out tasks such as enforcing business rules and preventing invalid transactions. This chapter then discusses Oracle’s trigger implementation, showing you how to create, execute, and manage triggers. Finally, you’ll learn how to incorporate trigger features into your PHP-driven Web applications.

Introducing Triggers

As developers, we have to remember to implement an extraordinary number of details in order for an application to operate properly. Of course, much of the challenge has to do with managing data, which includes tasks such as the following:

• Preventing corruption due to malformed data

• Enforcing business rules by ensuring that an insert of an item from an e-commerce store into the ORDER_ITEM table automatically calculates an estimated delivery date and shipping cost and inserts those values into other columns of the ORDER and ORDER_ITEM rows
• Automatically retrieving a unique number from an Oracle sequence and using it as the primary key of an inserted row
• Capturing usage information not available from Oracle’s built-in auditing

• Modifying rows in one or more base tables when a user performs DML operations against a view

If you’ve built even a simple application, you’ve likely spent some time writing code to carry out at least some of these tasks. Given the choice, you’d probably rather have some of these tasks carried out automatically on the server side, regardless of which application is interacting with the database. Database triggers give you that choice, which is why they are considered indispensable by many developers.
The utility of triggers stretches far beyond the aforementioned purposes. Suppose you want to update the corporate Web site when the $1 million monthly revenue target is met. Or suppose you want to e-mail any employee who misses more than two days of work in a week; or perhaps you want to notify a manufacturer if inventory runs low on a particular product. All of these tasks can be facil- itated by triggers.

649

Many developers would argue that business logic is best suited for middleware applications. However, enforcing business logic at the database level using triggers makes more sense when the business rule must be enforced regardless of the application used to access the database. Using trig- gers may prevent ad hoc SQL statements from creating logical inconsistencies in the data when a developer or DBA bypasses the application that normally updates the database.
To provide you with a better idea of the utility of triggers, let’s consider two scenarios, the first involving a before trigger, or a trigger that occurs prior to an event, and the second involving an after trigger, or a trigger that occurs after an event. These two types of triggers conveniently correspond to Oracle Database XE’s BEFORE and AFTER triggers.

Taking Action Before an Event

Suppose that a gourmet-food distributor gives automatic 20 percent discounts for an order line item if the customer orders premium coffee and it’s Monday. The pseudocode for this discounting process looks like this:

Shopping cart insertion request submitted: Set item_discount_amount = 0;
If product_id = "coffee" and day = "Monday":
Set item_discount_amount = item_amount * 0.20; End If
Process insertion request

Taking Action After an Event

Most help desk support software is based upon the paradigm of ticket assignment and resolution. Tickets are both assigned to and resolved by help desk technicians, who are responsible for logging ticket information. However, occasionally even the technicians are allowed out of their cubicles, sometimes even for a brief vacation or because they are ill. Clients can’t be expected to wait for a technician to return, so the technician’s tickets should be placed back in the pool for reassignment by the operations manager. This process should be automatic so that outstanding tickets aren’t potentially ignored. Therefore, it makes sense to use a trigger to ensure that the matter is never over- looked.
For the purposes of this example, assume that the TECHNICIAN table looks like the table in
Figure 37-1, viewed from the Object Browser in the Oracle Database XE Web interface.

Figure 37-1. The TECHNICIAN table

The TICKET table looks like the table in Figure 37-2.

Figure 37-2. The TICKET table

Therefore, to designate a technician as out-of-office, the AVAILABLE flag needs to be set accord- ingly (0 for out-of-office, 1 for in-office) in the TECHNICIAN table. If a query is executed setting that column to 0 for a given technician, his or her tickets should all be placed back in the general pool for eventual reassignment. The AFTER trigger pseudocode looks like this:

Technician table update request submitted: If available column set to 0:
Update helpdesk ticket table, setting any flag assigned to the technician back to the general pool.
End If

Later in this chapter in the section “Leveraging Triggers in PHP Applications,” you’ll learn how to implement this trigger and incorporate it into a Web application.

Before Triggers vs. After Triggers

You may be wondering how one arrives at the conclusion to use a BEFORE trigger instead of an AFTER trigger. For example, in the AFTER trigger scenario in the previous section, why couldn’t the ticket reassignment take place prior to the change to the technician’s availability status? Standard practice dictates that you should use a BEFORE trigger when validating or modifying data that you intend to insert or update. A BEFORE trigger shouldn’t be used to enforce propagation or referential integrity because it’s possible that other BEFORE triggers could execute after it, meaning the executing trigger may be working with soon-to-be-invalid data. It’s also possible that another BEFORE trigger will enforce another business rule that renders the transaction invalid.
On the other hand, an AFTER trigger should be used when data is to be propagated or verified against other tables and for carrying out calculations because you can be sure the trigger is working with the final version of the data.

Oracle’s Trigger Support

Because of Oracle Database XE’s rich support for built-in declarative integrity constraints, you may never need to create a trigger. In this section, we make sure you understand when triggers are not the best solution, saving you the time you would otherwise spend to write a trigger. In Chapter 30, we introduced foreign keys and how they can enforce referential integrity in your database. There is really no good reason to use a trigger if you can use a foreign key. A foreign key constraint check is more efficient than running a trigger because it’s built-in to the Oracle database engine. In addition, you don’t have to write even one line of PL/SQL code. Therefore, you should not use a trigger for integrity enforcement if you can use these built-in integrity constraints instead:

• NOT NULL

• UNIQUE

• PRIMARY KEY

• FOREIGN KEY

• CHECK

In the following sections, we tell you more about how Oracle implements triggers and some of the caveats when using triggers. Next, you’ll learn how to create, manage, and execute Oracle triggers using the TECHNICIAN and TICKET tables presented earlier in the chapter.

Understanding Trigger Events

Several types of events fire a trigger:

• DML statements: INSERT, UPDATE, or DELETE on a table or view

• DDL statements: CREATE or ALTER statements issued by a specific user or by any user in the database
• System events: Database startup, shutdown, and errors

• User events: User logon or logoff

In the next section, we give you an example of creating a DML statement trigger. This trigger fires when the availability of a technician changes by changing the AVAILABLE flag to 0. DDL triggers, system event triggers, and user event triggers are beyond the scope of this book.

Creating a Trigger

Oracle triggers are created using a rather straightforward SQL syntax, similar to that used to create PL/SQL procedures. This is not surprising, since the triggered actions have a syntax virtually iden- tical to that of a stored procedure. The command-line syntax prototype follows:

CREATE TRIGGER <trigger name>
{ BEFORE | AFTER }
{ INSERT | UPDATE | DELETE } ON <table name>
FOR EACH ROW
[WHEN (<restriction clause>)]
<triggered PL/SQL block>

TRIGGER NAMING CONVENTIONS

Although not a requirement, it’s a good idea to devise some sort of naming convention for your triggers so that you can more quickly determine the purpose of each. For example, you might consider prefixing each trigger title with one of the following strings, as shown in the example in Figure 37-4:

• AD: Execute trigger after a DELETE statement has been executed.

• AI: Execute trigger after an INSERT statement has been executed.

• AU: Execute trigger after an UPDATE statement has been executed.

• BD: Execute trigger before a DELETE statement has been executed.

• BI: Execute trigger before an INSERT statement has been executed.

• BU: Execute trigger before an UPDATE statement has been executed.

As you can see from the prototype, it’s possible to specify whether a trigger should execute before or after the query, whether it should take place on row insertion, modification, or deletion, and to what table the trigger applies. In addition, you can restrict the trigger to run on rows that fulfill the condition in the WHEN clause; you will specify the contents of the WHEN clause using the GUI in the example that follows.
The GUI-based Oracle Database XE interface makes it easy to create and view triggers. Even
though you can use a command-line interface using the previous prototype, we use the GUI to step through the trigger creation process. From the GUI home page, click the Object Browser icon. By default, you will see a list of tables owned by the user logged into the database. Click the Create button, and click the Trigger link. You will see the Trigger dialog shown in Figure 37-3.
Enter the name of the target table, that is, the table whose rows will fire the trigger when rows
are deleted, updated, or inserted. In this example, you enter TECHNICIAN or select TECHNICIAN using the drop-down if the table is owned by the current schema user. Click the Next button to continue.

Figure 37-3. Specifying the target table name in the trigger creation dialog

Next you fill in the following details for the trigger, as shown in Figure 37-4:

• Name of the trigger

• When the trigger fires

• What kind of DML causes the trigger to fire

• Whether the trigger fires for each affected row or only once

• An optional WHERE clause

• The trigger body (PL/SQL block to implement the trigger logic)

In this example, you modify the default trigger name by adding the prefix AU per the naming convention. The trigger will fire after updates to the TECHNICIAN table, and only when the technician table is updated. Select For Each Row since you want to update tickets for each technician who is not available. More than one technician can be updated in an UPDATE statement on the TECHNICIAN table.
The string in the When text box, shown in Figure 37-4, inserted into a WHEN clause in the trigger by the Create Trigger GUI application, is as follows:

NEW.AVAILABLE = 0

Figure 37-4. Specifying trigger options and logic

The NEW qualifier indicates that you’re checking the new (updated) value of the AVAILABLE flag. If the new value is 0, the technician is temporarily not available and you need to execute the body of this trigger to release his or her tickets to other technicians. The trigger body is very simple. Change all tickets for the unavailable technician to 0 so that another technician can be assigned to this ticket:
UPDATE TICKET
SET TECHNICIAN_ID = 0
WHERE TECHNICIAN_ID = :NEW.TECHNICIAN_ID;

The :NEW qualifier in the WHERE clause is similar to the NEW qualifier in the WHEN clause; it specifies that you want to use the new value of the technician ID number when performing the update. In some cases you could use :OLD.TECHNICIAN_ID with the same results, except in the situation where an update to a technician changes both the technician ID and the availability code in the same trig- gering UPDATE. Therefore, you use :NEW.
Click Next and you see the confirmation screen in Figure 37-5. If you click the SQL link, you can see the CREATE TRIGGER command that will be executed. Click Finish to create the trigger and return to the page with the new trigger’s details.

Figure 37-5. Confirm trigger creation request

For each row affected by an update to the TECHNICIAN table, the trigger will update the TICKET table, setting TICKET.TECHNICIAN_ID to 0 wherever the TECHNICIAN_ID value specified in the UPDATE query exists. You know the query value is being used because the alias :NEW prefixes the column name. It’s also possible to use a column’s original value by prefixing it with the :OLD alias.
Once the trigger has been created, go ahead and test it by inserting a few rows into the TICKET
table and executing an UPDATE query that sets a technician’s AVAILABILITY column to 0:

update technician set available=0 where technician_id=4;

Now check the TICKET table, and you’ll see that the ticket assigned to Kelly (in Figures 37-1 and
37-2) is no longer assigned to her.

Viewing Existing Triggers

Using the Oracle Database XE GUI, it’s easy to view existing triggers. From the Object Browser page, select Triggers. In the scroll box on the left, select the trigger to view. In Figure 37-6, the trigger AU_TECHNICIAN_T1 is selected and you can see the details of the trigger itself.

Figure 37-6. Trigger details

Clicking the SQL tab above the trigger details shows you the SQL used to create the trigger:

CREATE OR REPLACE TRIGGER "AU_TECHNICIAN_T1" AFTER
update on "TECHNICIAN"
for each row
WHEN (NEW.AVAILABLE = 0) begin
UPDATE TICKET
SET TECHNICIAN_ID = 0
WHERE TECHNICIAN_ID = :NEW.TECHNICIAN_ID;
end;
/
ALTER TRIGGER "AU_TECHNICIAN_T1" ENABLE
/

Modifying or Deleting a Trigger

There is no functionality for modifying an existing trigger using the Oracle Database XE GUI. There- fore, you must copy the attributes of the existing trigger, drop it using the Drop button shown in Figure 37-6, and recreate it using the steps outlined previously.

If you do not have the Oracle Database XE GUI available, you can drop the trigger using the SQL Commands interface or another SQL command-line interface using the DROP TRIGGER command
as follows:

DROP TRIGGER AU_TECHNICIAN_T1;

■Caution When you drop a table, all triggers defined against the table are also deleted.

Leveraging Triggers in PHP Applications

Because triggers occur transparently, you really don’t need to do anything special to integrate their operation into your Web applications. Nonetheless, it is worth offering an example demonstrating just how useful this feature can be in terms of both decreasing the amount of PHP code and further simplifying the application logic. Therefore, in this section you’ll learn how to implement the help desk application described earlier in this chapter.
To begin, create the two tables TECHNICIAN and TICKET with a few rows in each, as shown earlier in Figures 37-1 and 37-2. Next, create the trigger AU_TECHNICIAN_T1, shown earlier in Figure 37-4.
Recapping the scenario, submitted help desk tickets are resolved by assigning each to a techni- cian. If a technician is out of the office for an extended period of time, say due to a vacation or an illness, they are expected to update their profile by changing their availability status. The profile manager interface looks similar to that shown in Figure 37-7, using the PHP code later in this section.

Figure 37-7. The profile manager interface

When the technician makes any changes to this interface and submits the form, the code presented in Listing 37-1 is activated.

Listing 37-1. Updating the Technician Profile (upd_tech_prof.php)

<form action="<?php echo $_SERVER['PHP_SELF'];?>" method="post">
<p><b>Update your profile.</b></p>
<p>
Technician ID:<br />
<input type="text" name="technician_id" size="6" maxlength="6" value="" />
</p>
<p>
Name:<br />
<input type="text" name="name" size="25" maxlength="25" value="" />
</p>
<p>
EMail Address:<br />
<input type="text" name="email" size="40" maxlength="40" value="" />
</p>
<p>
Availability (0=unavailable, 1=available):<br />
<input type="text" name="available" size="1" maxlength="1" value="1" />
</p>
<p>
<input type="submit" name="submit" value="Update!" />
</p>
</form>

<?php
if (isset($_POST['submit']))
{
// Connect to Oracle Database XE
$c = oci_connect('hr', 'hr', '//localhost/xe');

// Assign the POSTed values for convenience

$technician_id = $_POST['technician_id'];
$name = $_POST['name'];
$email = $_POST['email'];
$available = $_POST['available'];

// Create and run the UPDATE statement
$result = oci_parse($c,
"UPDATE technician SET name='$name', email='$email', available='$available' WHERE technician_id='$technician_id'");
oci_execute($result);

echo "<p>Thank you for updating your profile.</p>";

if ($available == 0) {
echo "<p>Because you'll be out of the office, your tickets will be reassigned to another technician.</p>";
}
oci_close($c);
}
?>

Once you execute this code via the included form and set the status to unavailable for a given technician, query the TICKET table and you will see that the relevant tickets have been unassigned. Note that there are no references to the trigger within the PHP code itself; it happens transparently behind the scenes in Oracle Database XE.

Summary

This chapter introduced triggers, a feature that can help you automate database integrity and the enforcement of complex business rules that otherwise would have to be enforced in the application (although many flame wars have erupted over the best place to implement business logic). Triggers can greatly reduce the amount of code you need to write solely for ensuring the referential integrity and business rules of your database. You learned about the different trigger types and the conditions under which they will execute. We offered an introduction to Oracle Database XE’s trigger imple- mentation, followed by coverage of how to integrate these triggers into your PHP applications.
In the next chapter we’ll shift gears a bit and focus on database performance, using several different types of Oracle indexes, and when to use them to optimally retrieve table rows.

Beginning PHP and Oracle From Novice to Professional by W. Jason Gilmore and Bob Bryla Chapter 36

Throughout this book you’ve seen quite a few examples where the Oracle queries are embedded directly into the PHP script. Indeed, for smaller applications this is fine. However, as application
complexity and size increase, continuing this practice could be the source of some grief.
One of the most commonplace solutions to these challenges comes in the form of an Oracle database feature known as a PL/SQL subprogram. PL/SQL subprograms are also called PL/SQL procedures or stored routines; these terms can be used interchangably. A PL/SQL subprogram is a set of PL/SQL and SQL statements stored in the database server and executed by calling an assigned name within a query, much like a function encapsulates a set of commands that is executed when the function name is invoked. The PL/SQL subprogram can then be maintained from the secure confines of the data- base server, without ever having to touch the application code. In addition, separating the PL/SQL code from the PHP code makes both sets of code much easier to read and maintain.

Should You Use PL/SQL Subprograms?

What if you have to deploy two similar applications—one desktop-based and the other Web-based— that use Oracle Database XE and perform many of the same tasks? On the occasion a query changes, you’d need to make modifications wherever that query appears, not in one application but in two. Another challenge that arises when working with complex applications, particularly in a team envi- ronment, involves affording each member the opportunity to contribute his or her expertise without necessarily stepping on the toes of others. Typically, the individual responsible for database devel- opment and maintenance (known as the database architect) is particularly knowledgeable in writing efficient and secure queries. But how can the database architect write and maintain these queries without interfering with the application developer if the queries are embedded in the code? Further- more, how can the database architect be confident that the developer isn’t “improving” upon the queries, potentially opening up the application to penetration through a SQL injection attack (which involves modifying the data sent to the database in an effort to run malicious SQL code)? You can use a PL/SQL subprogram.

■Note PL/SQL stands for Procedural Language/Structured Query Language and is syntactically similar to the Ada
programming language. PL/SQL is Oracle’s proprietary server-based procedural extension to SQL. However, most other database vendors support similar functionality.

PL/SQL subprograms are categorized into three types: procedures, functions, and anonymous PL/SQL blocks. Anonymous PL/SQL blocks are syntactically identical to PL/SQL procedures and functions except that they don’t have a name or any parameters, are not directly stored in an Oracle

633

database, and are typically run as ad hoc blocks of PL/SQL code. You often see anonymous PL/SQL blocks within procedures or functions in addition to their use on an ad hoc basis. We detail these variations on PL/SQL subprograms and where to use them throughout this chapter.
Rather than blindly jumping onto the PL/SQL bandwagon, it’s worth taking a moment to consider the advantages and disadvantages of using PL/SQL subprograms, particularly because their utility is an often debated topic in the database community. The following sections summarize the pros and cons of incorporating PL/SQL into your PHP development strategy.

Subprogram Advantages

Subprograms have a number of advantages, the most prominent of which are highlighted here:

• Consistency: When multiple applications written in different languages are performing the same database tasks, consolidating these like functions within subprograms decreases other- wise redundant development processes.
• Performance: A competent database administrator often is the most knowledgeable member of the team regarding how to write optimized queries. Therefore, it may make sense to leave the creation of particularly complex database-related operations to this individual by main- taining them as subprograms.
• Security: When working in particularly sensitive environments such as finance, health care, and defense, it’s sometimes mandated that access to data is severely restricted. Using subpro- grams is a great way to ensure that developers have access only to the information necessary to carry out their tasks.
• Architecture: Although it’s out of the scope of this book to discuss the advantages of multitier architectures, using subprograms in conjunction with a data layer can further facilitate manageability of large applications. Search the Web for n-tier architecture for more informa- tion about this topic.

Subprogram Disadvantages

Although the preceding advantages may have you convinced that subprograms are the way to go, take a moment to ponder the following drawbacks:

• Performance: Many would argue that the sole purpose of a database is to store data and maintain data relationships, not to execute code that could otherwise be executed by the application. In addition to detracting from what many consider the database’s sole role, executing such logic within the database will consume additional processor and memory resources.
• Maintainability: Although you can use GUI-based utilities such as SQL Developer (see Chapter 29) to manage subprograms, coding and debugging them is considerably more difficult than writing PHP-based functions using a capable IDE.
• Portability: Because subprograms often use database-specific syntax (e.g., PL/SQL code is not easily ported to DB2 or SQL Server), portability issues will surely arise should you need to use the application in conjunction with another database product.

Even after reviewing the advantages and disadvantages, you may still be wondering whether subprograms are for you. Perhaps the best advice is to read on and experiment with the numerous examples provided throughout this chapter and see where you can leverage PL/SQL in your applications.

How Oracle Implements Subprograms

Although the term stored procedures is commonly bandied about, Oracle actually implements three procedural variants, which are collectively referred to as subprograms:

• Stored procedures: Stored procedures support execution of SQL statements such as SELECT, INSERT, UPDATE, and DELETE. They also can set parameters that can be referenced later from outside of the procedure.
• Stored functions: Stored functions support execution only of the SELECT statement, accept only input parameters, and must return one and only one value. Furthermore, you can invoke a stored function directly into a SQL command just like you might do with standard Oracle functions such as COUNT() and TO_DATE().
• Anonymous blocks: Anonymous blocks are much like stored procedures and functions except that they cannot be stored in the database and referenced directly because they are, as the name implies, anonymous. They do not have a name or parameters; you either run them in the SQL Commands or SQL Developer GUI application, or you can embed them within a stored procedure or function to isolate functionality.

Generally speaking, you use subprograms when you need to work with data found in the data- base, perhaps to retrieve rows or insert, update, and delete values; whereas you use stored functions to manipulate that data or perform special calculations. In fact, the syntax presented throughout this chapter is practically identical for both variations, except that the term procedure is swapped out for function. For example, the command DROP PROCEDURE procedure_name is used to delete an existing stored procedure, while DROP FUNCTION function_name is used to delete an existing stored function.

Creating a Stored Procedure

The following abbreviated syntax is available for creating a stored procedure; see the Oracle Data- base XE documentation for a complete definition:

CREATE [OR REPLACE] PROCEDURE procedure_name ([parameter[, ...]]) [characteristics, ...] [IS | AS] plsql_subprogram_body
The following is used to create a stored function:

CREATE [OR REPLACE] FUNCTION function_name ([parameter[, ...]]) RETURNS type
[characteristics, ...] [IS | AS] plsql_subprogram_body

Finally, you create and use anonymous PL/SQL blocks as follows:

DECLARE
declarations;
BEGIN statement1; statement2;
...

END;

The DECLARE section is optional regardless of whether you are writing a procedure, a function, or

an anonymous block. As you can infer from the syntax, you cannot pass variables, return variables, or reference the block from any other procedure or function; you can, however, save the block in a text file and retrieve it from the SQL Commands interface or embed the block within another stored function or procedure.

In this example, you use the SQL Commands interface to calculate an employee’s salary after two consecutive 10 percent raises. Figure 36-1 shows the anonymous block itself and the results after you click the Run button.

Figure 36-1.Running an anonymous PL/SQL block in SQL Commands

Although you could obtain the results in Figure 36-1 by using one or more SQL statements, the advantages of using PL/SQL are evident. The list of steps you use to obtain your results is easy to understand, and the output from the block would be difficult to obtain using just SQL commands. Note the embedded procedure call to DBMS_OUTPUT.PUT_LINE. This predefined stored procedure is included with your installation of Oracle Database XE that produces text output from your proce- dures. We show you more examples of calling procedures from within a procedure later in this chapter in the section “Creating and Using a Stored Function.”
The other advantage of using an anonymous block is clear only if you look at the output line Statement Processed. When you click the Run button, the entire block is sent to Oracle for processing as a unit; you see the results after Oracle executes the block. This minimizes the network traffic to and from the Oracle server in contrast to sending SQL commands one at a time.
For our second introductory example, let’s create a simple stored procedure that returns the static string Hello, World:

create or replace procedure say_hello as begin
dbms_output.put_line('Hello, World');
end;

You don’t need to pass any parameters; the procedure already has the text to print. Note the OR REPLACE clause; if the procedure already exists, it will be replaced. If you do not specify OR REPLACE and the procedure already exists, you will get an error message and the procedure is not replaced.
Now execute the procedure using the following command:

begin say_hello();
end;
Note that from the SQL Commands interface, you must use an anonymous block to call a stored procedure. Executing this procedure within the anonymous block returns the following output:

Hello, World

Statement processed.

0.00 seconds

In contrast to the previous example, once you create the procedure, you can call it repeatedly from different sessions without sending the procedure definition each time.

Parameters
Stored procedures can both accept input parameters and return parameters back to the caller. However, for each parameter, you need to declare the name and the datatype and whether it will be used to pass information into the procedure, pass information back out of the procedure, or perform both duties.

■Note Although stored functions can accept both input and output parameters in the parameter list, they only
support input parameters and must return one and only one value if referenced from a SELECT statement. There- fore, when declaring input parameters for stored functions, be sure to include just the name and type if you are only going to reference the stored functions from SELECT statements. Oracle best practices discourages the use of function parameters returning values to the calling program; if you must return more than one value from a subprogram, a stored procedure is more suitable.

Perhaps not surprisingly, the datatypes supported as parameters or return values for stored procedures correspond to those supported by Oracle, plus a few specific to PL/SQL. Therefore, you’re free to declare a parameter to be of any datatype you might use when creating a table.
To declare a parameter’s purpose, use one of the following three keywords:

• IN: These parameters are intended solely to pass information into a procedure. You cannot modify these values within the procedure.
• OUT: These parameters are intended solely to pass information back out of a procedure. You cannot pass a constant for a parameter defined as OUT.
• IN OUT: These parameters can pass information into a procedure, have its value changed, and then be referenced again from outside of the procedure.

Consider the following example to demonstrate the use of IN and OUT. First, create a stored proce- dure called RAISE_SALARY that accepts an employee ID and a salary increase amount and returns the employee name to confirm the salary increase:

create PROCEDURE raise_salary
(emp_id IN NUMBER, amount IN NUMBER, emp_name OUT VARCHAR2) AS BEGIN
UPDATE employees SET salary = salary + amount WHERE employee_id = emp_id;
SELECT last_name INTO emp_name FROM employees WHERE employee_id = emp_id; END raise_salary;
Next, use an anonymous PL/SQL block to increase the salary of employee number 105 by $200 per month:
DECLARE
emp_num NUMBER(6) := 105; sal_inc NUMBER(6) := 200; emp_last VARCHAR2(25);
BEGIN
raise_salary(emp_num, sal_inc, emp_last); DBMS_OUTPUT.PUT_LINE('Salary has been updated for: ' || emp_last);
END;

The results are as follows:

Salary has been updated for: Austin

Statement processed.

Declaring and Setting Variables

Local variables are often required to serve as temporary placeholders when carrying out tasks within a subprogram. This section shows you how to both declare variables and assign values to variables.

Declaring Variables

Unlike PHP, you must declare local variables within a subprogram before using them, specifying their type by using one of Oracle’s supported datatypes. Variable declaration is achieved with the DECLARE section of the PL/SQL subprogram or anonymous block, and its syntax looks like this:

DECLARE variable_name1 type [:= value];
variable_name2 type [:= value];
. . .

Here is a declaration section for a procedure that initializes some values for the area of a circle:

DECLARE
pi REAL := 3.141592654;
radius REAL := 2.5;
area REAL := pi * radius**2; BEGIN
. . .

There are a few things to note about this example. Variable declarations can refer to other variables already defined. In the previous example, the variable area is initialized to the area of a circle with a radius of 2.5. Note also the datatype REAL; it is one of PL/SQL’s internal datatypes not available for Oracle table columns but is provided as a floating-point datatype within PL/SQL to improve the performance of PL/SQL subprograms that require many high-precision floating point calculations.
Also note that by default any declared variable can be changed within the procedure. If you don’t want the application to change the value of pi, you can add the CONSTANT keyword as follows:

pi CONSTANT REAL := 3.141592654;

Setting Variables

You use the := operator to set the value of a declared subprogram variable. Its syntax looks like this:

variable_name := value;

Here are a couple of examples of assigning values in the body of the subprogram:

BEGIN
radius := 7.7;
area := pi * radius**2;
dbms_output.put_line
('Area of circle with radius: ' || radius || ' is: ' || area);

It’s also possible to set variables from table columns using a SELECT INTO statement. The syntax is identical to a SELECT statement you might run in SQL Commands or SQL*Plus but with the addition of the INTO variable_name clause to specify which PL/SQL variable will contain the table column’s value. We use this construct to retrieve the employee’s last name in the raise_salary procedure created earlier in the chapter:

SELECT last_name INTO emp_name FROM employees WHERE employee_id = emp_id;

PL/SQL Constructs

Single-statement subprograms are quite useful, but the real power lies in a subprogram’s ability to encapsulate and execute several statements, including conditional logic and iteration. In the following sections, we touch on the most important constructs.

Conditionals

Basing task execution on run-time information (e.g., from user input) is key for wielding tight control over the results of the task execution. Subprogram syntax offers two well-known constructs for performing conditional evaluation: the IF-THEN-[ELSIF][-ELSE]-END IF statement and the CASE statement. Both are introduced in this section.

IF-THEN-[ELSIF][-ELSE]-END IF

The IF-THEN-[ELSIF][-ELSE]-END IF statement is one of the most common means for evaluating conditional statements. In fact, even if you’re a novice programmer, you’ve likely already used it on numerous occasions. Therefore, this introduction should be quite familiar. The prototype looks like this:
IF condition THEN statement_list
[ELSIF condition THEN statement_list] . . . [ELSE statement_list]
END IF

■Caution The keyword for specifying alternate condition testing in an IF . . . END IF statement is ELSIF.
In many other programming languages it might be ELSE IF or ELSEIF, but in PL/SQL it’s one word: ELSIF.

For example, let’s say you want to adjust employee’s bonuses in proportion to their sales. Your conditional logic would look somewhat like the following:

IF sales > 50000 THEN
bonus := 1500;
ELSIF sales > 35000 THEN
bonus := 500; ELSE
bonus := 100;
END IF;
UPDATE employees SET salary = salary + bonus WHERE employee_id = emp_id;

For employees who are not in the sales department (sales = 0) or whose sales are $35,000 or less, the conditional logic assigns a bonus of $100.

CASE

The CASE statement is useful when you need to compare a value against an array of possibilities. While doing so is certainly possible using an IF statement, the code readability improves consider- ably by using the CASE statement. The CASE statement has two different forms, CASE-WHEN and the searched CASE statement. The CASE-WHEN statement identifies the variable to be compared in the first line of the CASE statement and performs the comparisons to the variable in subsequent lines. Here is the CASE-WHEN syntax:

CASE expression
WHEN expression THEN statement_list
[WHEN expression THEN statement_list] . . . [ELSE statement_list]
END CASE;

The ELSE condition executes if none of the other WHEN conditions evaluate to TRUE. Consider the following example, which sets a variable containing the appropriate sales tax rate by comparing a customer’s state to a list of values:

CASE state
WHEN 'AL' THEN tax_rate := .04; WHEN 'AK' THEN tax_rate := .00;
...
WHEN 'WY' THEN tax_rate := .06; END CASE;
Alternatively, the searched CASE statement gives you a bit more flexibility (at the expense of more typing). Here is the searched CASE syntax:

CASE
WHEN condition THEN statement_list
[WHEN condition THEN statement_list] . . . [ELSE statement_list]
END CASE;

Consider the following revised example, which sets a variable containing the appropriate sales tax rate by comparing a customer’s state to a list of values:

CASE
WHEN state='AL' THEN tax_rate := .04; WHEN state='AK' THEN tax_rate := .00;
...
WHEN state='WY' THEN tax_rate := .06; END CASE;
The form of the CASE statement is sometimes driven by your programming style. However, when you have complex conditions that cannot be represented in the CASE-WHEN syntax, you have no choice but to use the searched CASE. In either case (no pun intended), the readability of your code is dramatically improved in contrast to representing the same logic using IF-THEN-[ELSIF][-ELSE]-END IF.

Iteration

Some tasks, such as inserting a number of new rows into a table, require the ability to repeatedly execute over a set of statements. This section introduces the various methods available for iterating and exiting loops.

LOOP

The most basic way to loop through a series of statements is with the LOOP-END LOOP construct using this syntax:

LOOP statement1; statement2;
. . .
END LOOP;

The first question that may come to mind is, how useful is this plain LOOP construct if you can’t exit the loop? The DBA will not be too happy if your subprogram runs indefinitely. The EXIT constructs will address this issue in the next section.

EXIT and EXIT-WHEN

The EXIT statement forces a loop to complete unconditionally. As you might expect, you execute the EXIT statement based on a condition in an IF statement. Consider the following example where you display the square roots of the numbers one through ten:

DECLARE
countr NUMBER := 1; BEGIN
LOOP
dbms_output.put_line('Square root of ' || countr || ' is ' || SQRT(countr));
countr := countr + 1; IF countr > 10 THEN
EXIT; END IF;
END LOOP;
dbms_output.put_line('End of Calculations.'); END;

The counter is initialized in the DECLARE section; within the loop, the counter is incremented. Once the counter reaches the threshold value in the IF statement, the loop terminates and continues execution after the END LOOP statement. The output looks like this:

Square root of 1 is 1
Square root of 2 is 1.41421356237309504880168872420969807857
Square root of 3 is 1.73205080756887729352744634150587236694
Square root of 4 is 2
Square root of 5 is 2.23606797749978969640917366873127623544
Square root of 6 is 2.44948974278317809819728407470589139197
Square root of 7 is 2.64575131106459059050161575363926042571
Square root of 8 is 2.82842712474619009760337744841939615714
Square root of 9 is 3
Square root of 10 is 3.16227766016837933199889354443271853372
End of Calculations.

Statement processed.

You can alternatively use the EXIT-WHEN construct to improve the readability of your code if you only use the IF statement to check for a termination condition. You can rewrite the previous code example as follows:

DECLARE
countr NUMBER := 1; BEGIN
LOOP
dbms_output.put_line('Square root of ' || countr || ' is ' || SQRT(countr));
countr := countr + 1; EXIT WHEN countr > 10;
END LOOP;
dbms_output.put_line('End of Calculations.'); END;

WHILE-LOOP

As yet another alternative to EXIT, you can place your loop termination condition at the beginning of the loop using this syntax:

WHILE condition LOOP statement1; statement2;
. . .
END LOOP;

While this syntax may be more readable, it also has one major distinction compared to the previously discussed loop constructs: if the condition in the WHILE clause is not true the first time through the loop, the statements within the loop are not executed at all. In contrast, all previous versions of the LOOP construct execute the code within the loop at least once. Here is the previous example rewritten to use WHILE:

DECLARE
countr NUMBER := 1; BEGIN
WHILE countr < 11 LOOP
dbms_output.put_line('Square root of ' || countr || ' is ' || SQRT(countr));

countr := countr + 1; END LOOP;
dbms_output.put_line('End of Calculations.'); END;

FOR-LOOP

If your application needs to iterate over a range of integers, you can use the FOR-LOOP construct and simplify your code even more. Here is the syntax:

FOR variable IN startvalue..endvalue LOOP
statement1;
statement2;
. . . END LOOP;
Within the loop, the variable variable starts with a value of startvalue and terminates the loop when the value of variable exceeds endvalue. Rewriting our well-worn example from earlier in the chapter (and while we’re at it, dropping the unnecessary variable declaration) looks like this:

BEGIN
FOR i IN 1..10 LOOP
dbms_output.put_line('Square root of ' || i || ' is ' || SQRT(i)); END LOOP;
dbms_output.put_line('End of Calculations.'); END;

Note that you do not need to include the loop variable in the declaration section. You can, however, explicitly declare your loop variables depending on your programming standards.
In our final loop example, you want to iterate your loop in reverse order and produce the square roots starting with ten and ending at one. As you might expect, all you need to add is the REVERSE keyword to your LOOP clause as follows:

BEGIN
FOR i IN REVERSE 1..10 LOOP
dbms_output.put_line('Square root of ' || i || ' is ' || SQRT(i)); END LOOP;
dbms_output.put_line('End of Calculations.');
END;

This produces the following output, as expected:

Square root of 10 is 3.16227766016837933199889354443271853372
Square root of 9 is 3
Square root of 8 is 2.82842712474619009760337744841939615714
Square root of 7 is 2.64575131106459059050161575363926042571
Square root of 6 is 2.44948974278317809819728407470589139197
Square root of 5 is 2.23606797749978969640917366873127623544
Square root of 4 is 2
Square root of 3 is 1.73205080756887729352744634150587236694
Square root of 2 is 1.41421356237309504880168872420969807857
Square root of 1 is 1
End of Calculations.

Statement processed.

Creating and Using a Stored Function

As we mentioned earlier in this chapter, a stored function is similar to a stored procedure with one key difference: a stored function returns a single value. This makes a stored function available in your SQL SELECT statements, unlike stored procedures that you must call within an anonymous PL/SQL
block or another stored procedure.

■Note Although you can specify OUT parameters in a stored function, this is generally considered a bad program-
ming practice, and they are not allowed within SELECT statements. If you truly need multiple values returned from a subprogram, use a stored procedure.

In the example in Listing 36-1, you create a new stored function to format the employee data from the EMPLOYEES table (or any other source containing the same datatypes) to be more readable for Web applications or other reporting purposes.

Listing 36-1. Stored Function to Format Employee Data

CREATE OR REPLACE FUNCTION
format_emp (deptnum IN NUMBER, empname IN VARCHAR2, title IN VARCHAR2) RETURN VARCHAR2
IS
concat_rslt VARCHAR2(100); BEGIN
concat_rslt :=
'Department: ' || to_char(deptnum) ||
' Employee: ' || initcap(empname) ||
' Title: ' || initcap(title); RETURN (concat_rslt);
END;

To test this out using a SELECT statement, use an example similar to the following:

select
format_emp(183, 'CHRYSANTHEMUM', 'WIKIPEDIA MAINT') "Employee Info" from DUAL;
The output looks like that shown in Figure 36-2 when you run it using the SQL Commands inter- face. We show you how to use this function within a PHP application in the section “Integrating Subprograms into PHP Applications.”

Figure 36-2. Running a SELECT statement containing a user-defined function

Modifying, Replacing, or Deleting Subprograms Unless you are using a more advanced GUI or IDE (integrated development environment), you only have one option to update or replace a stored function or procedure: you redefine the function or procedure by including the OR REPLACE clause, as you saw in Listing 36-1. If the stored function or

procedure does not already exist, it is created; if it exists, it is replaced. This prevents error messages when you don’t care if the subprogram already exists.
To delete a subprogram, execute the DROP statement. Its syntax is as follows:

DROP (PROCEDURE | FUNCTION) proc_name;

For example, to drop the y2k_update stored procedure, execute the following command:

DROP PROCEDURE y2k_update;

Integrating Subprograms into PHP Applications Thus far, all the examples have been demonstrated by way of the Oracle Database XE SQL Commands or SQL Developer client. While this is certainly an efficient means for testing examples, the utility of subprograms is drastically increased by the ability to incorporate them into your application. This
section demonstrates just how easy it is to integrate subprograms into your PHP-driven Web application.
In the first example, you use the function created in Listing 36-1 to format a Web report. See
Listing 36-2 for the PHP application that references the FORMAT_EMP function.

Listing 36-2. Stored Function to Format Employee Data (use_stored_func.php)

<?php
// Connect to Oracle Database XE
$c = oci_connect('hr', 'hr', '//localhost/xe');

// Create and execute the query
$result = oci_parse($c,
'select employee_id "Employee Number", ' .
'format_emp(department_id, last_name, job_id) ' .
'"Employee Info"' .
' from employees where rownum < 11');
oci_execute($result);

// Format the table
echo "<table border='1'>";
echo "<tr>";

// Output the column headers
for ($i = 1; $i <= oci_num_fields($result); $i++) {
echo "<th>".oci_field_name($result, $i)."</th>";
}

echo "</tr>";

// output the results
while ($employee = oci_fetch_row($result)) {
$emp_id = $employee[0];
$emp_info = $employee[1];
echo "<tr>";
echo "<td>$emp_id</td><td>$emp_info</td>";
echo "</tr>";
}

echo "</table>";
oci_close($c);
?>

You can see the results for the first ten rows of the EMPLOYEES table in Figure 36-3.

Figure 36-3. Results from a PHP script using an embedded user-defined function

Invoking a stored procedure in PHP is almost as easy. The key difference is that since you are returning results from a procedure within the PHP script, you must bind the IN and OUT variables in the stored procedure to PHP variables. In this example, you first create a procedure called say_hello_ to_someone, based on the procedure say_hello you created earlier in this chapter, to address a specific person provided as input to the procedure:
create or replace procedure say_hello_to_someone
(who IN VARCHAR2, message OUT VARCHAR2)
as begin
message := 'Hello there, ' || who;
end;

To test this procedure using the SQL Commands interface, try this:

DECLARE
back_at_ya VARCHAR2(100); BEGIN
say_hello_to_someone('JenniferG',back_at_ya);
dbms_output.put_line('Message is: ' || back_at_ya); END;

The results are as follows:

Message is: Hello there, JenniferG

Statement processed.

Listing 36-3 contains the PHP script to call the new procedure say_hello_to_someone and display it on a very simple Web page. Note that you execute the procedure the same way you do from the SQL Commands interface: within an anonymous PL/SQL block.

Listing 36-3. Calling a Stored Procedure from PHP (use_stored_func.php)

<?php
// Connect to Oracle Database XE
$c = oci_connect('hr', 'hr', '//localhost/xe');

// Create and parse the query
$result = oci_parse($c,
'BEGIN ' .
' say_hello_to_someone(:who, :message); ' .
'END;');

oci_bind_by_name($result,':who',$who,32); // IN parameter oci_bind_by_name($result,':message',$message,64); // OUT parameter

$who = 'Dr. Who';

// Execute the query oci_execute($result);

echo "$message\n";

oci_close($c);
?>

After you create the connection to Oracle Database XE and parse the anonymous block, you
bind the PHP variables to the PL/SQL variables (for both input and output), execute the statement, and display the results on the Web page:

Hello there, Dr. Who

Summary

This chapter introduced Oracle PL/SQL, Oracle Database XE’s server-side programming language. You learned about the advantages and disadvantages to consider when determining whether this feature should be incorporated into your development strategy and all about Oracle’s specific implementation and syntax. In addition, you learned how easy it is to incorporate PL/SQL anony- mous blocks, stored functions, and stored procedures into your PHP applications.
The next chapter introduces another server-side feature of Oracle Database XE: triggers.