Oracle PL/SQL Tutorial/System Packages/DBMS ALERT

Call dbms_alert.signal in a loop

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>

Call DBMS_ALERT.SIGNAL in a trigger

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>

DBMS_ALERT.REGISTER

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>

dbms_alert.waitone

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  /

Registers the alert for the session

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

Signal along with the entries in a table

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>

Singal with dbms_alert.signal

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