Suppose a graphics tool is displaying a graph of some data from a database table. The graphics tool can, after reading and graphing the data, wait on a database alert (WAITONE) covering the data just read. The tool automatically wakes up when the data is changed by any other user. All that is required is that a trigger be placed on the database table, which performs a signal (SIGNAL) whenever the trigger is fired.

Table 17-2 shows the messages and the procedures that can raise them.

• Alerts are transaction-based. This means that the waiting session is not alerted until the transaction signalling the alert commits. There can be any number of concurrent signalers of a given alert, and there can be any number of concurrent waiters on a given alert.

• A waiting application is blocked in the database and cannot do any other work.

• An application can register for multiple events and can then wait for any of them to occur using the WAITANY procedure.

• An application can also supply an optional timeout parameter to the WAITONE or WAITANY procedures. A timeout of 0 returns immediately if there is no pending alert.

• The signalling session can optionally pass a message that is received by the waiting session.

• Alerts can be signalled more often than the corresponding application wait calls. In such cases, the older alerts are discarded. The application always gets the latest alert (based on transaction commit times).

• If the application does not require transaction-based alerts, the DBMS_PIPE package may provide a useful alternative.

  See Also:

  DBMS_PIPE

• If the transaction is rolled back after the call to SIGNAL, no alert occurs.

• It is possible to receive an alert, read the data, and find that no data has changed. This is because the data changed after the prior alert, but before the data was read for that prior alert.

• Usually, Oracle is event-driven; this means that there are no polling loops. There are two cases where polling loops can occur:

  • Shared mode. If your database is running in shared mode, a polling loop is required to check for alerts from another instance. The polling loop defaults to one second and can be set by the SET_DEFAULTS procedure.

  • WAITANY procedure. If you use the WAITANY procedure, and if a signalling session does a signal but does not commit within one second of the signal, a polling loop is required so that this uncommitted alert does not camouflage other alerts. The polling loop begins at a one second interval and exponentially backs off to 30-second intervals.

Your application would look similar to this code:

DBMS_ALERT.REGISTER('emp_table_alert');
    <<readagain>>: 
   /* ... read the emp table and graph it */ 
      DBMS_ALERT.WAITONE('emp_table_alert', :message, :status); 
      if status = 0 then goto <<readagain>>; else 
      /* ... error condition */ 

The EMP table would have a trigger similar to this:

CREATE TRIGGER emptrig AFTER INSERT OR UPDATE OR DELETE ON emp
    BEGIN 
      DBMS_ALERT.SIGNAL('emp_table_alert', 'message_text'); 
   END;

When the application is no longer interested in the alert, it makes this request:

DBMS_ALERT.REMOVE('emp_table_alert');

This reduces the amount of work required by the alert signaller. If a session exits (or dies) while registered alerts exist, the alerts are eventually cleaned up by future users of this package.

The example guarantees that the application always sees the latest data, although it may not see every intermediate value.