Programming Oracle Triggers and Stored Procedures (3rd Edition) (Prentice Hall PTR Oracle Series)

The Oracle DBMS_ALERT package allows a process to post a message to another process and have that message delivered only after a commit. This is different from pipes where a process posts a message and that message will always be sent to the pipe, regardless of commit or rollback.

Pipe messages are sent immediately. Alerts are delivered only after the commit. A process can post many alerts. None can be received until the sending process has done a commit.

Alerts are asynchronous. The sender can send an alert signal whether or not a receiver has a posted read. This is also true with pipes. Receivers can post a read-wait with a timer or they issue a read and return. Senders and receivers communicate with the alert buffer independent of each other. Senders never wait on receivers, or vice versa.

Alerts are for processes connected to the same instance. The following is one scenario.

An application program, APP_A, written in Java and running on server host01.domain.com, is connected to a database on hostdb.domain.com through JDBC. A second application, APP_B, written in C, is running on host02.domain.com ”this is connected to the same database on hostdb.domain.com with ODBC. Application APP_A can post alerts using the DBMS_ALERT package. Application APP_B, running on a separate server, can receive those alerts ”the intermediary is the database. This scenario is essentially application-to-application communication, even though these applications run on separate machines.

Alerts can also be fired from triggers. That is a practical form of asynchronous communication because you can post numerous alerts, but should the transaction fail, the alerts are never delivered. This is not so with pipes. Messages posted to a pipe from within a database trigger will always be delivered. This behavior helps when deciding whether to use a pipe or an alert. If you are sending text as a general means of communication and that communication is not tied to a transaction, then use a pipe. If it's transaction based, use an alert.

Pipes are also more suited for larger text messages. Alerts are modeled after the UNIX SIGNAL paradigm, which is intended to just signal an event. One process sends a signal to another process, and the mere name of that signal, in this case alert name , indicates the type of event that took place.

The following summarizes the differences between alerts and pipes.

• Use pipes for exchanging textual information because you can pass VARCHAR2, NUMBER, and DATE types, and the buffer for transmission is 4K.

• Use alerts to signal an event. The alert name is the indicator of the particular event that took place.

• Alerts are limited in textual transmission. You can only deliver 1,800 characters of text. The alert name is what identifies the event.

• A message posted to a pipe is always delivered. When you post an alert, you are saying, "allow the receiver to get this only if and when I commit."

Alerts and pipes can work together. Two processes can post messages to pipes but use alerts as an indicator that work needs to be done. For example, a worker-process may spend most of its processing time idle, waiting to receive any one of several alert signals. When that process receives an alert, that particular alert indicates a specific task. Based on that task, the worker-process will read messages from one of several pipes.

Messages sitting in pipes and alerts, waiting to be delivered, are temporary. If the database goes down, the alerts and pipe messages will not be present when the database comes up. The Oracle Advanced Queuing option provides a robust form of queuing messages that are integrated into the database. Queues are implemented with tables, so queues are restored during crash recovery. If you put a message in a queue and the database goes down before the queue message is delivered, the queue message will be present when the database comes back up. The API to queues is extensive , compared to pipes and alerts. The pipes and alerts are straightforward forms of communication, but a message or alert can potentially be lost if the database crashes before a message can be received.

Pipe communication requires no synchronization between a sender and receiver. A PL/SQL procedure can send a message to a pipe at any time. Whether a receiver procedure reads from that pipe has no effect on the sender. There is a mild synchronization required with alerts. A receiver must first register for an alert. A receiver can register for many alerts, but someone must register first. When the alert is sent the process that registered will be able to get the alert. Once an alert is sent, it is too late to register and receive. The register must occur first. If you send an alert and no process has registered for that alert, no process will ever be able to receive it.

"Event" and "Signal" are excellent synonyms for an alert. If we want to know that an event took place, we only need to be told once. "The light is on" ”that is an event, or an alert. If 10 people say the light is on, the message is the same as if said once. A process can send the same alert twice, or even three times. When a receiving process issues a read, Oracle will deliver a single alert because the receiver only needs to be told once that that event has occurred. Messages, up to 1,800 characters, can accompany an alert. When multiple alerts with the same name are issued and there is one delivery, the message with the last sent alert is the message delivered.

11.21.1 Interface Description

The following paragraphs describe the DBMS_ALERT package API.

SIGNAL PROCEDURE signal( name IN VARCHAR2, message IN VARCHAR2);

This procedure sends an alert. Unless some other process has previously registered for an alert, this call is meaningless. Some processes must first register for an alert ”then a sender can send that alert.

The alert name is the key component ”this indicates the event that needs to be transmitted. You may find that you rarely use the message parameter, or use it for supplemental information only. Suppose you have a manufacturing system. In this system, inventory is moved from a stock warehouse to an assembly floor ”this is a fully automated system. The movement of materials involves a DELETE trigger on a database inventory table. When items are deleted, the trigger evaluates how much inventory is left. If inven-tory is too low, resulting from the delete, the trigger sends a LOW_INVENTORY alert to another ORDER INVENTORY application. That alert name should be sufficient information as to what needs to be done. The additional 1,800 characters can be supplemental information that specifies the specifics of exactly what materials are short.

REGISTER PROCEDURE register(name in VARCHAR2);

The general behavior of a receiver is to register for one or more alerts and then wait on any of those alerts. Let's continue with the manufacturing example. There are a variety of critical events in a manufacturing environment. You can have an application that remains in an idle state, waiting for a problem to solve (e.g., problems such as low inventory and out-of-stock items). This application would begin by registering for both events and then posting a read-wait on either of these events. This PL/SQL code would begin with the following.

dbms_alert.register('LOW_INVENTORY'); dbms_alert.register('OUT_OF_STOCK_ITEM'); dbms_alert.waitany(name, message, status);

This application would "sit" on the WAITANY call until an alert was received.

WAITANY PROCEDURE waitany( name OUT VARCHAR2, message OUT VARCHAR2, status OUT INTEGER, timeout IN INTEGER DEFAULT maxwait); maxwait constant integer := 86400000; /* 1000 days */

This procedure is very useful. It means that a single process can register for multiple events and then wait, with a single call, on the occurrence of any event. When the event occurs (i.e., the alert is received), the process performs some work, then goes back and issues another wait.

A process cannot restrict a WAITANY to a subset of the alerts for which that process is registered. If a process registers for a LOW_INVENTORY, OUT_OF_STOCK_ITEM, and other alerts, then a WAITANY will always, and can only, wait on that full set of alerts.

Alerts that are sent within a transaction that is uncommitted do not block subsequent alerts that are sent in committed transactions. Assume we have a process that waits on LOW_INVENTORY and OUT_OF_STOCK_ITEM. Another process sends the alert, LOW_INVENTORY, but has not done a commit. Seconds later another process sends the alert OUT_OF_STOCK_ITEM with an immediate commit. The process with the WAITANY call will receive the OUT_OF_STOCK_ITEM immediately.

WAITONE PROCEDURE waitone( name IN VARCHAR2, message OUT VARCHAR2, status OUT INTEGER, timeout IN INTEGER DEFAULT maxwait);

If an application is registered for several alerts, it makes sense to post a WAITANY. The WAITONE procedure is for posting a read for a specific alert. If you register for just one alert, this procedure can be used to wait on that specific. The choice between WAITONE and WAITANY is a design issue and depends on how many alerts upon which you want to post concurrent read-waits.

REMOVE PROCEDURE remove(name IN VARCHAR2);

Oracle documentation recommends that a procedure remove an alert when it no longer needs that alert. An application program may be designed to always have a posted read on one or more alerts. In this case, that application is never "finished" with any of its registered alerts. This is quite acceptable. A procedure can remove all registered alerts with the REMOVEALL procedure.

REMOVEALL PROCEDURE removeall;

An application can remove all alerts for which it is registered with this one call.

SET_DEFAULTS PROCEDURE set_defaults(sensitivity IN NUMBER);

This procedure sets a polling "sleep time" value that is used internally with calls to WAITANY. It is not necessary to use this procedure unless you want to override the default sleep period, which is five sec.