CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle DBMS_ALERT

Version 11.1
 
General Information
Source {ORACLE_HOME}/rdbms/admin/dbmsalrt.sql
First Available 7.3.4
Constants
Name Data Type Value
maxwait INTEGER 86400000
 := 1000 days
Dependencies
DBMS_ALERT_INFO DBMS_SESSION
DBMS_LOCK DBMS_UTILITY
DBMS_PIPE REPCATLOGTRIG
DBMS_REPCAT_ADD_MASTER X$KGLOB
DBMS_REPCAT_MAS  

Error Messages Raised by ORA-2000 Exception
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
Security Model Execute is granted to the execute_catalog_role role
 
REGISTER
Lets a session register interest in an alert dbms_alert.register(name IN VARCHAR2);
See demo below
 
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 demo below
 
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,
timeout IN   NUMBER DEFAULT MAXWAIT); -- in seconds
set serveroutput on

DECLARE
 alert_out VARCHAR2(50);
BEGIN
  dbms_alert.waitany;
  dbms_output.put_line(alert_out);
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 demo below
 
DBMS_ALERT Demo

Session 1
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 INSERT INTO emp
(empno, ename, hiredate)
VALUES
(1, 'Morgan', SYSDATE);

COMMIT;
 
Related Topics
DBMS_AQ
DBMS_AQADM
DBMS_PIPE
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [86 users online]    © 2010 psoug.org