Oracle PL/SQL Tutorial/System Packages/DBMS ALERT

Call dbms_alert.signal in a loop

   <source lang="sql">

SQL> SQL> begin

 2    for i in 1 .. 10 loop
 3       dbms_alert.signal( "MyAlert", "Message " || i );
 4    end loop;
 5  end;
 6  /

PL/SQL procedure successfully completed. SQL></source>

Call DBMS_ALERT.SIGNAL in a trigger

   <source lang="sql">

SQL> SQL> CREATE TABLE s_inventory

 2  (product_id               NUMBER(7),
 3   warehouse_id             NUMBER(7),
 4   amount_in_stock          NUMBER(9),
 5   reorder_point            NUMBER(9),
 6   max_in_stock             NUMBER(9),
 7   out_of_stock_explanation VARCHAR2(255),
 8   restock_date             DATE);

Table created. SQL> SQL> INSERT INTO s_inventory VALUES (10011, 101, 650, 625, 1100, NULL, NULL); 1 row created. SQL> INSERT INTO s_inventory VALUES (10012, 101, 600, 560, 1000, NULL, NULL); 1 row created. SQL> INSERT INTO s_inventory VALUES (10013, 101, 400, 400, 700, NULL, NULL); 1 row created. SQL> INSERT INTO s_inventory VALUES (10021, 101, 500, 425, 740, NULL, NULL); 1 row created. SQL> INSERT INTO s_inventory VALUES (10022, 101, 300, 200, 350, NULL, NULL); 1 row created. SQL> INSERT INTO s_inventory VALUES (10023, 101, 400, 300, 525, NULL, NULL); 1 row created. SQL> INSERT INTO s_inventory VALUES (20106, 101, 993, 625, 1000, NULL, NULL); 1 row created. SQL> INSERT INTO s_inventory VALUES (20108, 101, 700, 700, 1225, NULL, NULL); 1 row created. SQL> INSERT INTO s_inventory VALUES (20201, 101, 802, 800, 1400, NULL, NULL); 1 row created. SQL> INSERT INTO s_inventory VALUES (20510, 101, 1389, 850, 1400, NULL, NULL); 1 row created. SQL> INSERT INTO s_inventory VALUES (20512, 101, 850, 850, 1450, NULL, NULL); 1 row created. SQL> INSERT INTO s_inventory VALUES (30321, 101, 2000, 1500, 2500, NULL, NULL); 1 row created. SQL> INSERT INTO s_inventory VALUES (30326, 101, 2100, 2000, 3500, NULL, NULL); 1 row created. SQL> INSERT INTO s_inventory VALUES (30421, 101, 1822, 1800, 3150, NULL, NULL); 1 row created. SQL> SQL> CREATE OR REPLACE TRIGGER au_inventory

 2     AFTER UPDATE of amount_in_stock
 3     ON s_inventory
 4     FOR EACH ROW
 5     WHEN (new.amount_in_stock < new.reorder_point)
 6  BEGIN
 7     DBMS_ALERT.SIGNAL("REORDER_THRESHOLD_ALERT", "Warehouse: " ||
 8        :new.warehouse_id || "  Product: " || :new.product_id ||
 9        "  Current Stock: " || :new.amount_in_stock ||
10        "  Reorder Point: " || :new.reorder_point);
11  END au_inventory;
12  /

SQL> SQL> show error SQL> SQL> drop table s_inventory; Table dropped. SQL> SQL></source>

DBMS_ALERT.REGISTER

   <source lang="sql">

SQL> SQL> CREATE OR REPLACE PROCEDURE p_register

 2  IS
 3  BEGIN
 4      DBMS_ALERT.REGISTER("ALERTTEST");
 5  END;
 6  /

SQL> SQL> drop procedure p_register ; Procedure dropped. SQL> SQL> SQL></source>

dbms_alert.waitone

   <source lang="sql">

SQL> SQL> set serveroutput on SQL> declare

 2      l_status    number;
 3      l_msg       varchar2(1800);
 4  begin
 5      dbms_alert.waitone( name    => "MyAlert",
 6                          message => l_msg,
 7                          status  => l_status,
 8                          timeout => dbms_alert.maxwait );
 9
10      if ( l_status = 0 )
11      then
12          dbms_output.put_line( "Msg from event is " || l_msg );
13      end if;
14  end;
15  /</source>
   
  

Registers the alert for the session

   <source lang="sql">

SET SERVEROUTPUT ON SIZE 1000000 DECLARE

  lv_message_txt VARCHAR2(1800);
  lv_status_num  PLS_INTEGER;

BEGIN

  DBMS_ALERT.REGISTER("REORDER_THRESHOLD_ALERT");
  FOR lv_count_num IN 1..10 LOOP
     DBMS_ALERT.WAITONE("REORDER_THRESHOLD_ALERT",lv_message_txt, lv_status_num);
     IF lv_status_num = 0 THEN
        DBMS_OUTPUT.PUT_LINE(lv_message_txt);
     ELSE
        DBMS_OUTPUT.PUT_LINE("Alert Failed.");
     END IF;
  END LOOP;

END; /</source>

Signal along with the entries in a table

   <source lang="sql">

SQL> SQL> SQL> create table job_table

 2  ( job_id     int primary key,
 3    alert_name varchar2(30),
 4    message    varchar2(2000)
 5  )
 6  /

Table created. SQL> SQL> create or replace procedure background_alert( p_job in int )

 2  as
 3      l_rec job_table%rowtype;
 4  begin
 5      select * into l_rec from job_table where job_id = p_job;
 6      dbms_alert.signal( l_rec.alert_name, l_rec.message );
 7      delete from job_table where job_id = p_job;
 8      commit;
 9  end;
10  /

SQL> SQL> SQL> drop table job_table

 2  /

Table dropped. SQL></source>

Singal with dbms_alert.signal

   <source lang="sql">

SQL> SQL> set echo on SQL> SQL> exec dbms_alert.signal( "MyAlert", "Hello World" ); PL/SQL procedure successfully completed. SQL></source>