7.1 DBMS_ALERT

Table of Contents Previous Next


7 Built-In Packages : 7.1 DBMS_ALERT

The DBMS_ALERT package provides the capability to register for, send, and receive alerts.
Table 7-7‑1 DBMS_ALERT Functions/Procedures
REMOVE(name)
SIGNAL(name, message)
Signals the alert named, name, with message.
WAITANY(name OUT, message OUT, status OUT, timeout)
WAITONE(name, message OUT, status OUT, timeout)
Postgres Plus Advanced Server's implementation of DBMS_ALERT is a partial implementation when compared to Oracle's version. Only those functions and procedures listed in the table above are supported.
Advanced Server allows a maximum of 500 concurrent alerts. You can use the dbms_alert.max_alerts GUC variable (located in the postgresql.conf file) to specify the maximum number of concurrent alerts allowed on a system.
To set a value for the dbms_alert.max_alerts variable, open the postgresql.conf file (located by default in /opt/PostgresPlus/9.4AS/data) with your choice of editor, and edit the dbms_alert.max_alerts parameter as shown:
alert_count specifies the maximum number of concurrent alerts. By default, the value of dbms_alert.max_alerts is 100. To disable this feature, set dbms_alert.max_alerts to 0.
For the dbms_alert.max_alerts GUC to function correctly, the custom_variable_classes parameter must contain dbms_alerts:
After editing the postgresql.conf file parameters, you must restart the server for the changes to take effect.
7.1.1 REGISTER
The REGISTER procedure enables the current session to be notified of the specified alert.
REGISTER(name VARCHAR2)
7.1.2 REMOVE
The REMOVE procedure unregisters the session for the named alert.
REMOVE(name VARCHAR2)
7.1.3 REMOVEALL
The REMOVEALL procedure unregisters the session for all alerts.
7.1.4 SIGNAL
The SIGNAL procedure signals the occurrence of the named alert.
SIGNAL(name VARCHAR2, message VARCHAR2)
7.1.5 WAITANY
The WAITANY procedure waits for any of the registered alerts to occur.
WAITANY(name OUT VARCHAR2, message OUT VARCHAR2,
status OUT INTEGER, timeout NUMBER)
The following anonymous block uses the WAITANY procedure to receive an alert named, alert_test or any_alert:
7.1.6 WAITONE
The WAITONE procedure waits for the specified registered alert to occur.
WAITONE(name VARCHAR2, message OUT VARCHAR2,
status OUT INTEGER, timeout NUMBER)
The following anonymous block is similar to the one used in the WAITANY example except the WAITONE procedure is used to receive the alert named, alert_test.
Signal sent for alert_test sent by an anonymous block in a second session:
The following example uses two triggers to send alerts when the dept table or the emp table is changed. An anonymous block listens for these alerts and displays messages when an alert is received.

7 Built-In Packages : 7.1 DBMS_ALERT

Table of Contents Previous Next