Re: HELP DB_ALERT

-- 
In article <2t2rom$6q3_at_mystech.mystech.com>, jcrupi_at_mystech writes:


> Does anyone know how to use the DB_ALERT package ?


> 

> Thanks,

> 

> jcrupi_at_mystech.com

> 

Here is a quick & simple/silly example - the idea is that
one process is updating drill temperatures and another
must be informed when a drill gets too hot (note there 
are some steps at startup which you must not forget):

DROP TABLE Drill
/
CREATE TABLE Drill
(
   id          INTEGER,
   temperature INTEGER,
   name        VARCHAR2(10)
);

INSERT INTO Drill VALUES (1, 100, '1st drill');
INSERT INTO Drill VALUES (2, 200, '2nd drill');
INSERT INTO Drill VALUES (3, 100, '3nd drill');
/

DROP TABLE drill_log
/
CREATE TABLE drill_log
(
   LogDate     DATE,
   Status      VARCHAR2(10),
   id          INTEGER,
   temperature INTEGER,
   name        VARCHAR2(10)
)
/

CREATE OR REPLACE TRIGGER drillhot
   AFTER UPDATE OF temperature ON drill
   FOR EACH ROW
   WHEN (new.temperature >= 500)
BEGIN
   INSERT INTO drill_log
          VALUES (SYSDATE, 'Offline', :new.id, :new.temperature, :new.name);
   dbms_alert.signal('drill_hot', 'Take drill '|| :new.id ||' down!');
END drillhot;
/

CREATE OR REPLACE PROCEDURE CheckDrillMsg AS
           message VARCHAR2(256);
           status  INTEGER;
        BEGIN
             dbms_alert.waitone('drill_hot', message, status, 5);
             IF status = 0 THEN
                dbms_output.put_line(message);
             ELSE
                dbms_output.put_line('no alters');
             END IF;
        END;
/
SHOW errors

SET SERVEROUTPUT ON
EXECUTE dbms_alert.register('drill_hot');

/*
Check that user has permission to execute dbms_alter procedures.
  grant EXECUTE on dbms_alert to user_name;

Using for example SQL*Plus (2 sessions if you can):
% sqlplus user/password

Allow output from the server and register the alerter:
 SET SERVEROUTPUT ON
 EXECUTE dbms_alert.register('drill_hot');

Do update on database and see that event is raised:
 UPDATE drill set temperature = 700 where id = 2;
 COMMIT;
 EXECUTE CheckDrillMsg;
 EXECUTE dbms_alert.signal('drill_hot', 'Drill Overheating: please take down.');
 COMMIT;
 EXECUTE CheckDrillMsg;

Admin statements:
 select * from drill_log;
 delete from drill_log;
 select * from sys.dbms_alert_info;
 EXECUTE dbms_alert.remove('drill_hot');
*/

See also dbmsalrt.sql and the manuals/books.

--
- Andrew