Oracle DBMS

Oracle DBMS_ALERT Version 21c

General Information

Library Note

Morgan's Library Page Header

Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.

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

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;