| 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; |
