 |
|
Oracle Special Packages
Oracle Tips by
Burleson
|
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:
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.
The WaitOne() Procedure
The WaitOne() procedure is used by a PL/SQL block to wait
for a particular signal to occur. The procedure has four parameters:
name, message, status, and timeout.
PROCEDURE WaitOne (name IN varchar2,
message OUT varchar2,
status OUT integer,
timeout IN number DEFAULT MAXWAIT)
The name parameter is the name of the signal for which the
PL/SQL block is waiting. The message parameter returns any text
that is passed with the signal when it occurs. The status
parameter returns 0 if the signal was received or 1 if the procedure
timed out while waiting for the signal. The timeout parameter
indicates the interval (in seconds) that the WaitOne()
procedure will wait for the specified signal. If no value for this
parameter is specified, it defaults to the value of the
DBMS_Alert.MAXWAIT constant. The value of this constant is 1,000
days (86,400,000 seconds).
This is an excerpt from the book "High Performance Oracle
Database Automation" by Jonathan Ingram and Donald K.
Burleson, Series Editor. |
