Oracle DBMS_ALERT
Version 12.1.0.2

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.1 to 12.1.0.2. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Interested in Oracle GoldenGate? Check out the IGGOUG,
Purpose Interprocess Signaling
AUTHID DEFINER
Constants
Name Data Type Value
maxwait INTEGER 86400000 (equal to 1000 days)
Dependencies
DBMS_ALERT_INFO DBMS_REPCAT_MAS REPCATLOGTRIG
DBMS_LOCK DBMS_SESSION X$KGLOB
DBMS_PIPE DBMS_STANDARD Demo
DBMS_REPCAT_ADD_MASTER 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 7.3.4
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

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