Removing One Alert

To remove only one specific alert from the registration list, use the REMOVE procedure.

The Syntax for the REMOVE Procedure

PROCEDURE REMOVE(alert_name IN VARCHAR2);

alert_name is the alert you want to remove from the registration list. After you no longer need to wait for an alert, you should use REMOVE to remove the registration instead of using up valuable resources. Whether you wait for an alert or not, once registered, the alert will try to signal all procedures that are registered. Not only does the system waste resources attempting to send an alert to what it believes will be a waiting process, but it also takes longer for the system to process through the registration list.

Removing All Alerts

You can remove all registered alerts by placing a call to the procedure REMOVEALL. The format for the REMOVEALL procedure is as follows:

PROCEDURE REMOVEALL;

After the procedure is executed, all registered alerts are deleted.

Polling Versus Events with the SET_DEFAULTS Procedure

As an Oracle event occurs, it is picked up in the system and processed. You will see this in the walkthrough example of the DBMS_ALERT package using the WAITONE procedure. The WAITONE procedure waits for the specific event to occur, and either alerts you when the alert occurs or eventually times out. On the other hand, there are situations that will require polling, or specifically searching for an alert:

• When shared instances of a database could issue an alert, you need to poll for an alert for any of the shared instances.
• When using the WAITANY procedure. The WAITANY procedure goes into a looping poll mode to search for any alerts signaled for registered alerts. When WAITANY goes into a sleep mode after polling for alerts, if three alerts are signaled during this sleep period, WAITANY will pick up only the most recent signaled alert. The default poll starts at one second and increases exponentially to 30 seconds.

Because two possibilities—using shared instances or using WAITANY for any alert to be signaled—could result in missed alerts, you can change the polling time in seconds using SET_DEFAULTS.

The Syntax for the SET_DEFAULTS Procedure

PROCEDURE SET_DEFAULTS(polling_interval IN NUMBER);

You simply specify the interval between polling expressed in seconds. The default interval for this procedure is five seconds.

Alerts Versus Pipes

By now, you should see many similarities and differences between pipes and alerts. The similarities can be summarized as follows:

• Both use asynchronous communication.
• Both send messages between sessions of the same instance.
• Both can signal the execution of a C program.
• Both are PL/SQL packages.

The differences between alerts and pipes are

• The DBMS_ALERT package uses COMMIT, whereas DBMS_PIPE does not. Because alerts are transaction-based and use commits, a ROLLBACK can remove any waiting alert that has not been received. With pipes, after the message has been sent out, there is no recalling of the message.
• Alerts are generally used for one-way communication, whereas pipes are usually used for two-way communications.
• The DBMS_ALERT package not only allows multiple sessions to wait for an alert, but also all sessions will receive the alert after it has been signaled. This is similar to broadcasting a message in networking. If more than one session is waiting for a message from the pipe, only one of the sessions will receive the message and then clear the pipe when using the DBMS_PIPE package.
• Alerts can only send a string of characters, whereas pipes can send STRING, DATE, and NUMBER datatypes.

A Demonstration of the DBMS_ALERT Package

The best way to understand alerts is to try using the DBMS_ALERT package. Your goal is to solve a security problem. It has been noted that some employees have gained access to the

payroll database and have had some fun changing around pay rates. The IS director empowered you to devise an alert, which will be constantly monitored by security, to detect whether anything in the payroll database is changed.

In order for you to meet this goal, you have decided to create a copy of the payroll database, along with who changed what data at what time. In addition, because human resources can legitimately change data in the database, you need to add a Verified field, which security will change to Y for Yes after the change has been approved. You will have to create the following:

• A backup database called security, which will hold the old and new values, the user who changed the information, the date the user changed the information, and whether the information has been verified.
• A trigger based upon the insert, update, or delete performed on a row, which will then issue an alert.
• A program to monitor for the security alert.

Creating the Backup Database

As with any type of an audit trail, you will create a database that will hold a copy of the old and new information, the date, time, and user who changed the data, and whether the data was verified by security. To create the database, enter and execute the code in Listing 20.1.

INPUTListing 20.1. Creating the backup security database.

CREATE TABLE security(
/*This database holds the original and new data archived from
  the payroll database to look for any violations of pay rate,
  name changes, and so on by internal employees or external hackers */

/*  Store the original values */
     OLD_Emp_Id INTEGER,
     OLD_Emp_Name VARCHAR2(32),
     OLD_Supervised_By INTEGER,
     OLD_Pay_Rate NUMBER(9,2),
     OLD_Pay_Type CHAR,
     OLD_Emp_Dept_Id INTEGER,
/* Store the changed values */
     NEW_Emp_Id INTEGER,
     NEW_Emp_Name VARCHAR2(32),
     NEW_Supervised_By INTEGER,
     NEW_Pay_Rate NUMBER(9,2),
     NEW_Pay_Type CHAR,
     NEW_Emp_Dept_Id INTEGER,
/* Flag to retain status if security has verified the change (Y/N)*/
     Verified CHAR(1),
/* Store Date and who made the changes */
     Changed_By VARCHAR2(8),
     Time_Changed DATE)
/