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.
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;
/
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);