Oracle MaMe

DBMS_Alert signals when inserting in a table

Sometimes you want to be informed, when data are inserted in a oracle table.

One possibility is to use the sys.dbms_alert package to send signals to other
client progams. Here first a simple example about the usage of the package methods:

DECLARE

 NAME VARCHAR2(200);

 MESSAGE VARCHAR2(200);

 STATUS NUMBER;

 TIMEOUT NUMBER;

BEGIN

 NAME := 'HURZ';

 MESSAGE := 'This is a test';

 SYS.DBMS_ALERT.REGISTER ( NAME );

 SYS.DBMS_ALERT.SIGNAL ( NAME, MESSAGE );

 commit;

 STATUS := NULL;

 TIMEOUT := 20;

 SYS.DBMS_ALERT.WAITONE ( NAME, MESSAGE, STATUS, TIMEOUT );

 DBMS_OUTPUT.Put_Line('MESSAGE = ' || MESSAGE);

 DBMS_OUTPUT.Put_Line('STATUS = ' || TO_CHAR(STATUS));

END;

Now after some first tests, how can this be used in a real world example?

1. Let's assume we have created a test-user with the name ALERT_TESTER with the commands [as user "SYS AS SYSDBA"]:
   

   
   

   create user alert_tester identified by YOUR_ALERT_TESTER_PASSWORD;
   
   GRANT CONNECT, RESOURCE to alert_tester;

   
   

   
   







2. The user which wants to use the SYS.DBMS_ALERT package has to have the necessary rights i.e. the EXECUTE right for package SYS.DBMS_ALERT. This can be granted by the command:
   
   

   
   

   
   
   GRANT EXECUTE ON SYS.DBMS_ALERT to alert_tester;

   
   

   
   


3. Login now as user ALERT_TESTER and create a test table with:
   

   
   
   CREATE TABLE ALERT_TESTER.Alert_table
   
   (
   
   	id	    NUMBER,
   
   	name	VARCHAR2(200)
   
   )
   
   



4. A trigger which fires a alert when some data are inserted can be created by:
   

   
   
   CREATE OR REPLACE TRIGGER ALERT_TRIGGER
   
   AFTER INSERT ON Alert_table
   
   FOR EACH ROW
   
   BEGIN
   
    DBMS_ALERT.SIGNAL('Alert_table#ALERT', 'Data inserted into table Alert_table');
   
   END ALERT_TRIGGER;
   
   /
   
   



5. The trigger can now be tested by executing the following command from a first sql-client, which will block until some alert arrives or a timeout is reached:
   

   
   
   DECLARE
   
   NAME VARCHAR2(200);
   
   MESSAGE VARCHAR2(200);
   
   STATUS NUMBER;
   
   TIMEOUT NUMBER;
   
   BEGIN
   
   NAME := 'Alert_table#ALERT';
   
   MESSAGE := 'DUMMY';
   
   SYS.DBMS_ALERT.REGISTER ( NAME );
   
   STATUS := NULL;
   
   TIMEOUT := 60;
   
   SYS.DBMS_ALERT.WAITONE ( NAME, MESSAGE, STATUS, TIMEOUT );
   
   DBMS_OUTPUT.Put_Line('MESSAGE = ' || MESSAGE);
   
   DBMS_OUTPUT.Put_Line('STATUS = ' || TO_CHAR(STATUS));
   
   END;
   
   



6. During the last statement blocks you should insert some data to the test table in a second sql-client and commit the transaction. After the commit
   you should see a message from the trigger in the first sql-client:
   

   
   
   INSERT INTO ALERT_TESTER.Alert_table (ID,NAME) Values (0,'Test');
   
   
   
   commit
   
   

Further information can be found for example here

posted by Dr. Martin Menzel @ 10:19 AM