Table of Contents
This chapter briefly introduces the DBMS_ALERT package, and describes how to use the procedures of the package.
DBMS_ALERT provides procedures related to Tibero ALERT package for notification and waiting. The package is integrated with a Tibero database. Notification and waiting information is removed when the server restarts.
ALERT is usually used as follows.
Register an alert to wait for. (REGISTER procedure)
Wait for the alert. (WAITANY and WAITONE procedures)
Wake up when the alert is caused by another session because a relevant event occurs. (SIGNAL procedure)
The alert registered in 1. and the alert signal caused in 3. are removed when the server restarts.
The DBMS_ALERT package defines the following constant.
MAXWAIT
MAXWAIT CONSTANT INTEGER :=
86400000
This section describes the procedures provided by the DBMS_ALERT package, in alphabetical order.
Registers an alert to wait for. A registered alert is valid only in the session that executes this procedure. An alert is registered only once in a session. To use a registered alert in another session, the alert must be registered in the session.
Details about the REGISTER procedure are as follows.
Prototype
DBMS_ALERT.REGISTER
( name IN VARCHAR2 );
Parameter
| Parameter | Description |
|---|---|
| name | Alert name to register or wait for. |
Example
BEGIN
DBMS_ALERT.REGISTER('ABC'); END; /
Removes a registered alert. An alert is removed only in the session that executes this procedure. The removal does not have an effect on an alert with the same name in another session. If trying to remove an unregistered alert, an error occurs.
Details about the REMOVE procedure are as follows.
Prototype
DBMS_ALERT.REMOVE
( name IN VARCHAR2 );
Parameter
| Parameter | Description |
|---|---|
| name | Alert name to remove. |
Example
BEGIN
DBMS_ALERT.REMOVE('ABC'); END; /
Removes all registered alerts. Alerts are removed only in the session that executes this procedure. The removal does not have an effect on alerts with the same names in another session.
Details about the REMOVEALL procedure are as follows.
Prototype
DBMS_ALERT.REMOVEALL
( );
Example
BEGIN
DBMS_ALERT.REMOVEALL(); END; /
Causes an alert to wake up sessions that wait for the alert.
Details about the SIGNAL procedure are as follows.
Prototype
DBMS_ALERT.SIGNAL
( name IN VARCHAR2, message IN VARCHAR2 );
Parameter
| Parameter | Description |
|---|---|
| name | Alert name. |
| message | Up to 128-byte message that will be sent along with the alert. |
Example
BEGIN
DBMS_ALERT.SIGNAL('ABC', 'DEF'); END; /
Waits for any registered alert. The session that registered the alert wakes up when the alert occurs or timeout expires. The alert is not removed automatically.
Details about the WAITANY procedure are as follows.
Prototype
DBMS_ALERT.WAITANY
( name OUT VARCHAR2, message OUT VARCHAR2, status OUT VARCHAR2,
timeout IN VARCHAR2 );
Parameter
| Parameter | Description |
|---|---|
| name | Alert name that wakes the session up. No value is specified if the session wakes up because of timeout. |
| message | Message that will be sent along with the alert. No value is specified if the session wakes up because of timeout. |
| status |
|
| tiemout | Maximum time to wait for the alert. (Unit: seconds) |
Example
set
serveroutput on declare name varchar(1000); msg varchar(1000);
status integer; begin dbms_alert.register('ABC');
dbms_alert.waitany(name, msg, status, 100000);
dbms_output.put_line(name); dbms_output.put_line(msg);
dbms_output.put_line(status); END; /
Waits for a specific registered alert. If an unregistered alert is specified, an error occurs. The session that registered the alert wakes up when the alert occurs or timeout expires. The alert is not removed automatically.
Details about the WAITONE procedure are as follows.
Prototype
DBMS_ALERT.WAITONE
( name IN VARCHAR2, message OUT VARCHAR2, status OUT VARCHAR2,
timeout IN VARCHAR2 );
Parameter
| Parameter | Description |
|---|---|
| name | Alert name to wait for. |
| message | Message that will be sent along with the alert. No value is specified if the session wakes up because of timeout. |
| status |
|
| tiemout | Maximum time to wait for the alert. (Unit: seconds) |
Example
set
serveroutput on declare name varchar(1000); msg varchar(1000);
status integer; begin dbms_alert.register('ABC');
dbms_alert.waitone('ABC', msg, status, 100000);
dbms_output.put_line(msg); dbms_output.put_line(status); END;
/