Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

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:

  • 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.

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.

††
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracleģ is the registered trademark of Oracle Corporation. 

Hit Counter