Oracle DBMS_ALERT
Version 12.2.0.1

General Information
Library Note Morgan's Library Page Header
"I spent my entire adult life looking out for the well-being, the training, the equipping of the troops for whom I was responsible. I will not be lectured about what our military needs by a five-deferment draft dodger,"
~ Sen. Tammy Duckworth
Purpose Interprocess Signaling
AUTHID DEFINER
Constants
Name Data Type Value
maxwait INTEGER 86400000 (equal to 1000 days)
Dependencies
DBMS_ALERT_INFO DBMS_SESSION X$KGLOB
DBMS_LOCK DBMS_STANDARD Demo
DBMS_PIPE DBMS_UTILITY  
Documented Yes
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
 
WAIT_ANY
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
DBMS_APPLICATION_INFO
DBMS_AQ
DBMS_AQADM
DBMS_PIPE
Packages
What's New In 12cR1
What's New In 12cR2

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-2017 Daniel A. Morgan All Rights Reserved