Oracle | DBMS

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	SET_DEFAULTS	WAITANY
REMOVE	SIGNAL	WAITONE
REMOVEALL

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;