273-278

Table of Contents

Chapter 9
Special Packages

Oracle provides several packages that allow you to accomplish a wide range of tasks , from interprocess communication to file I/O to dynamically creating and executing SQL statements inside a PL/SQL block. All of these packages are owned by the SYS user ”one of the two users that exist when Oracle is first installed. The most important of these packages include:

DBMS_Alert

DBMS_DDL

DBMS_Describe

DBMS_Job

DBMS_Output

DBMS_Pipe

DBMS_SQL

DBMS_Utility

UTL_File

This chapter discusses these packages in detail and describes some common uses for each package.

DBMS_ALERT

The DBMS_Alert package is used to implement synchronous, event-driven interprocess communication via signals . This package is often used in conjunction with database triggers in systems that process data based on individual transactions.

The basic processing of an implementation using signals is fairly simple, as you can see in the following steps:

1. An event occurs, typically a row being written to a table.

2. A database trigger calls the DBMS_Alert.Signal() procedure.

3. The process that inserted the row into the table issues a COMMIT .

4. The signal is sent.

5. All processes that have registered for the signal are notified that the event indicated by the signal has occurred.

The importance of the COMMIT in this scheme can t be overlooked. A COMMIT must be issued for the signal to be sent.

The processes that receive the signal must first register for the signal by calling the DBMS_Alert.Register() procedure.

The DBMS_Alert package contains the following procedures:

The Register() procedure

The Remove() procedure

The RemoveAll() procedure

The SetDefaults() procedure

The Signal() procedure

The WaitAny() procedure

The WaitOne() procedure

These procedures and the parameters needed to call each procedure are explained in the following sections.

The Register() Procedure

The Register() procedure is used by a PL/SQL block to indicate that it would like to receive a particular signal. This procedure accepts a single parameter, as follows :

 PROCEDURE Register (name IN     varchar2)

The name parameter is the name of the signal for which the PL/SQL block is registering. This parameter must have a length of 30 characters or less.

The Remove() Procedure

The Remove() procedure is used by a PL/SQL block when receiving a registered signal is no longer appropriate. This procedure accepts a single parameter, as follows:

 PROCEDURE Remove (name IN     varchar2)

The name parameter is the name of the signal for which the PL/SQL block has no further need.

The RemoveAll() Procedure

The RemoveAll() procedure is used by a PL/SQL block when no further signals should be received. This procedure does not accept any parameters and appears as follows:

 PROCEDURE RemoveAll

The Set_Defaults() Procedure

The Set_Defaults() procedure is used by a PL/SQL block to determine the time that will pass between checks to see if a signal has occurred. This procedure accepts a single parameter, as follows:

 PROCEDURE Set_Defaults (sensitivity IN    number)

The sensitivity parameter indicates the number of seconds that should pass between checks for the signal. This value can be specified to a precision of hundredths of a second (two decimal positions ).

The Signal() Procedure

The Signal() procedure is used by a PL/SQL block to send a signal. The procedure accepts two parameters:

 PROCEDURE Signal (name    IN     varchar2,                   message IN     varchar2)

The name parameter is the name of a specific signal. The message parameter is a string of text that is received by all objects that receive the specific signal.

It s worth noting that consecutive signals will overwrite the message from a previous signal. Thus, unless you can guarantee that a signal will be processed immediately, it is unwise to pass data to a routine via the message parameter because the data could be overwritten by a later alert. Figure 9.1 illustrates how this could happen.

Figure 9.1 Conflicting alerts.

If you need to pass data when an event occurs, you should consider the use of the DBMS_Pipe package instead of the DBMS_Alert package (more about DBMS_Pipe later in this chapter). Alternately, you can mark data that has been affected by an event so that the code on the receiving end of the signal can identify rows that need to be processed.

The WaitAny() Procedure

The WaitAny() procedure is used by a PL/SQL block to wait for any of its registered signals to occur. The procedure has four parameters and appears as follows:

 PROCEDURE WaitAny (name       OUT varchar2,                    message    OUT varchar2,                    status     OUT integer,                    timeout IN     number DEFAULT MAXWAIT)

The name parameter for this procedure is the name of the signal that has occurred while waiting for an event. It s possible for a signal to have already occurred at the time of the call to the WaitAny() procedure. If this is the case, the procedure will return the name of the first signal that is found to have occurred.