Page 485
by Timothy Atwood
As the name suggests, alerts can be used to warn you or alert you for informational purposes. The DBMS_ALERT package is typically a one-way asynchronous communication, which is triggered when a transaction commits. Unless a transaction commits, no information will be sent to the alert. Because alerts are used for one-way communication, they have limited usage. Some examples of how you could use alerts are as follows: If you work for an insurance company, you can be alerted to natural disasters so that your team of experts can immediately be dispatched to aid the survivors. You can be alerted if a trigger fails, which could cause database corruption. You can implement a system to trap errors using alerts; however, this case would be better handled with exception handlers in the PL/SQL code.
Page 486
Today's lesson discusses the following:
| NOTE |
Because the DBMS_ALERT package uses COMMIT, this package cannot be used in Oracle Forms. |
Because the DBMS_ALERT package is transaction-based, any ROLLBACK will remove any waiting alerts affected. The order for setting up an alert is
| NOTE |
In order to work with the DBMS_ALERT package, you must have the package installed, and you must have EXECUTE permission to the package. |
When you want to send an alert, you need to use the SIGNAL procedure.
Page 487
The Syntax for the SIGNAL Procedure
PROCEDURE SIGNAL(alert_name IN VARCHAR2,
message_sent IN VARCHAR2);
alert_name can be a maximum of 30 characters, and it is not case-sensitive. In addition, the name must not start with ORA$, because this is reserved for use with Oracle. message_sent can be up to 1800 characters, which allows for a generous concatenation of text, variable names, and so on.
After the alert is sent, Oracle changes the state of the alert from not signaled to a state of signaled. This information is recorded in the DBMS_ALERT_INFO data dictionary. Because there is only one record for each alert, any other sessions attempting to send an alert will be blocked until the alert has been received.
If no sessions have registered the alert, the alert will remain signaled until the session has registered the alert. If multiple sessions have registered for the alert, after the alert has been signaled, all sessions will receive the alert, and then the alert will return to the nonsignaled state.
Before you can even search for an alert, you must register the alert or alerts you want to monitor, which adds you to the master registration list. This is done through the use of the REGISTER procedure.
The Syntax for the REGISTER Procedure
PROCEDURE REGISTER(alert_name IN VARCHAR2);
In this syntax, alert_name is the name of the alert to monitor. You can monitor as many alerts as you are registered for. You can remove yourself from the master registration list through the use of the REMOVE or REMOVEALL procedures.
| NOTE |
Simply registering an alert does not block the session from executing; rather, it simply records an interest in the alert. Only the WAITONE and WAITANY commands can block the session from executing. Although you can benefit from registering for all possible alerts and then checking for the alert later in a procedure at any time, you are still using valuable resources to monitor the registration. Use REGISTER only when necessary! |
Page 488
If you want to monitor one alert, you can accomplish this through the WAITONE procedure.
The Syntax for the WAITONE Procedure
PROCEDURE WAITONE(alert_name IN VARCHAR2,
alert_message OUT VARCHAR2,
alert_status OUT INTEGER,
timeout IN NUMBER DEFAULT maxwait);
Again, alert_name is the name of the alert you are monitoring. alert_message is the message that you will receive when the alert has been signaled. The alert_status parameter has two possible values: 0 if the alert was signaled before the timeout or 1 if the timeout has occurred before any alert has been received. timeout is how long you will wait (in seconds) for the alert before the procedure continues executing if no alert was received. As you might recall, the default for maxwait is 1000 days.
| NOTE |
When testing a DBMS_ALERT procedure, it is a good idea to make maxwait no longer than five minutes—otherwise, you could be at the keyboard for 1000 days! |
If the alert_name specified has not been registered, you will receive an error message:
ORA-20000, ORU-10024: there are no alerts registered.
The WAITANY procedure allows you to constantly monitor for any alert for which you have registered.
The Syntax for the WAITANY Procedure
PROCEDURE WAITANY(alert_name OUT VARCHAR2,
alert_message OUT VARCHAR2,
alert_status OUT INTEGER,
timeout IN NUMBER DEFAULT maxwait);
alert_name is an OUT parameter of type VARCHAR2, instead of type IN VARCHAR2. Instead of specifying alert_name as an input, you now receive the alert_name of the first registered alert that was sent. alert_message is the message that you will receive when the specific alert is signaled. The alert_status parameter has two possible values: 0 if any alert was signaled before the timeout or 1 if the timeout has occurred before any alert has been received. timeout is how long you will wait (in seconds) for the alert before the procedure continues executing. Again, the default for maxwait is 1000 days. You will also receive the same error message as WAITONE if you do not register the alert before trying to wait for an alert.