Oracle DBMS_ALERT
Version 21c

General Information
Library Note Morgan's Library Page Header
ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.
Purpose Interprocess Signaling
AUTHID DEFINER
Constants
Name Data Type Value
maxwait INTEGER 86400000 (equal to 1000 days)
Dependencies
DBMS_ALERT_INFO DBMS_PIPE DBMS_UTILITY
DBMS_GSM_UTILITY DBMS_SESSION X$KGLOB
DBMS_LOCK DBMS_STANDARD Demo
Documented Yes: Packages and Types Reference
Exceptions
Error Code Reason
ORU-10001 Lock request error, status: N
ORU-10015 Error: N waiting for pipe status
ORU-10016 Error: N sending on pipe 'X'
ORU-10017 Error: N receiving on pipe 'X'
ORU-10019 Error: N on lock request
ORU-10020 Error: N on lock request
ORU-10021 Lock request error; status: N
ORU-10022 Lock request error; status: N
ORU-10023 Lock request error; status: N
ORU-10024 There are no alerts registered
ORU-10025 Lock request error; status N
ORU-10037 Attempting to wait on uncommitted session signal
First Available version 7
Security Model Owned by SYS with EXECUTE granted to SYSTEM and the EXECUTE_CATALOG_ROLE role
Source {ORACLE_HOME}/rdbms/admin/dbmsalrt.sql
Subprograms
 
REGISTER
Lets a session register interest in an alert dbms_alert.register(
name    IN VARCHAR2,
cleanup IN BOOLEAN DEFAULT TRUE);
See DBMS_ALERT Demo at page bottom
 
REMOVE
Enables a session that is no longer interested in an alert to unregistration the alert dbms_alert.remove(name IN VARCHAR2);
exec dbms_alert.remove('emptab_alert');
 
REMOVEALL
Removes all alerts for this session from the registration list dbms_alert.removeall;
exec dbms_alert.removeall;
 
SET_DEFAULTS
Set the polling interval dbms_alert.set_defaults(sensitivity IN NUMBER);
exec dbms_alert.set_defaults(3);
 
SIGNAL
Signals an Alert (up to 1800 bytes) dbms_alert.signal(
name    IN VARCHAR2,
message IN VARCHAR2);
See DBMS_ALERT Demo at page bottom
 
WAITANY
Wait for an alert to occur for any of the alerts for which the current session is registered dbms_alert.waitany(
name    OUT VARCHAR2,
message OUT VARCHAR2,
status  OUT INTEGER,  -- 0=alert occurred, 1=timeout occurred
timeout IN  NUMBER DEFAULT MAXWAIT); -- in seconds
set serveroutput on

exec dbms_alert.register('Demo');

DECLARE
 name_out VARCHAR2(30);
 mesg_out VARCHAR2(30);
 status   PLS_INTEGER;
BEGIN
  dbms_alert.waitany(name_out, mesg_out, status, 3);
  dbms_output.put_line(status);
END;
/
 
WAITONE
Waits for a specific alert to occur dbms_alert.waitone(
name    IN  VARCHAR2,
message OUT VARCHAR2,
status  OUT INTEGER,
timeout IN  NUMBER DEFAULT MAXWAIT); -- in seconds


Status Value Description
0 Alert Occurred
1 Timeout Occurred 
See DBMS_ALERT Demo below
 
DBMS_ALERT Demo
Session 1 conn sys@pdbdev as sysdba

GRANT execute ON dbms_alert to uwclass;

conn uwclass/uwclass@pdbdev

CREATE TABLE emp (
empno    NUMBER(3),
ename    VARCHAR2(20),
hiredate DATE);

CREATE OR REPLACE TRIGGER t_empchg
AFTER INSERT OR UPDATE
ON emp
FOR EACH ROW
DECLARE
 msg VARCHAR2(1800);
BEGIN
  IF INSERTING THEN
    msg := 'New Employee Is: ' || :NEW.empno;
  ELSE
    msg := 'Updated Employee: ' || :OLD.empno;
  END IF;
  dbms_alert.signal('emptab_alert', msg);
END t_empchg;
/

CREATE OR REPLACE PROCEDURE waiting IS
 msg  VARCHAR2(1800);
 stat PLS_INTEGER;
BEGIN
  dbms_alert.register('emptab_alert');
  dbms_alert.waitone('emptab_alert', msg, stat);
  dbms_output.put_line('Msg: ' || msg || ' Stat: ' || TO_CHAR(stat));
END waiting;
/

set serveroutput on

exec waiting;
Session 2 conn uwclass/uwclass@pdbdev

INSERT INTO emp
(empno, ename, hiredate)
VALUES
(1, 'Morgan', SYSDATE);

COMMIT;

Related Topics
Built-in Functions
Built-in Packages
Database Security
DBMS_APPLICATION_INFO
DBMS_AQ
DBMS_AQADM
DBMS_PIPE
What's New In 21c
What's New In 23c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2023 Daniel A. Morgan All Rights Reserved
  DBSecWorx