DBMS_ALERT lets one session tell others that something happened. Sessions register for an alert by name and wait; another session signals the alert with a message, and the alert is delivered when its transaction commits. Unlike pipes, alerts are transactional, and unlike queues, they keep only the latest message.
Code for This Guide
The main examples are in the examples/pkg-messaging folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
dbms_alert.register(name); dbms_alert.signal(name, message); -- delivered on commit dbms_alert.waitone(name, message_out, status_out, timeout); dbms_alert.waitany(name_out, message_out, status_out, timeout); dbms_alert.remove(name); dbms_alert.removeall;
Status 0 means an alert arrived, 1 a timeout.
Signal and Wait
For the example one session registers, signals, and waits; normally the signal comes from another session.
Example:
declare
v_message varchar2(1800);
v_status integer;
begin
dbms_alert.register('FLIGHT_CANCELLED'); -- this session wants to know
dbms_alert.signal('FLIGHT_CANCELLED', 'NM205 cancelled: crew shortage');
commit; -- alerts are sent on commit
dbms_alert.waitone('FLIGHT_CANCELLED', v_message, v_status, timeout => 5);
dbms_output.put_line('status ' || v_status || ': ' || v_message);
dbms_alert.waitone('FLIGHT_CANCELLED', v_message, v_status, timeout => 1);
dbms_output.put_line('status ' || v_status || ' (1 = timed out)');
dbms_alert.remove('FLIGHT_CANCELLED');
end;
/Output:
status 0: NM205 cancelled: crew shortage status 1 (1 = timed out) PL/SQL procedure successfully completed.
The signal is delivered by the COMMIT, and WAITONE returns status 0 with the message. A second wait times out with status 1, since the alert was already received.
Wait for Any of Several Alerts
Example:
declare
v_name varchar2(30);
v_message varchar2(1800);
v_status integer;
begin
dbms_alert.register('GATE_CHANGED');
dbms_alert.register('FLIGHT_DELAYED');
dbms_alert.signal('FLIGHT_DELAYED', 'NM101 delayed 30 minutes');
commit;
dbms_alert.waitany(v_name, v_message, v_status, timeout => 5); -- whichever comes
dbms_output.put_line(v_name || ': ' || v_message || ' (status ' || v_status || ')');
dbms_alert.removeall;
end;
/Output:
FLIGHT_DELAYED: NM101 delayed 30 minutes (status 0) PL/SQL procedure successfully completed.
The session registers two alerts and WAITANY returns whichever is signaled, here FLIGHT_DELAYED, with its name and message. REMOVEALL ends all registrations.
Things to Know
- If an alert is signaled several times before anyone waits, waiters get only the last message.
- A rolled-back signal is never delivered, which keeps alerts consistent with the data.
- Signaling serializes on the alert name until commit, so keep signaling transactions short.
Related Guides
Conclusion
DBMS_ALERT signals named events that waiting sessions receive after the signaling transaction commits. Use it to wake up sessions when something changes, with WAITONE for one alert and WAITANY for several.
