Oracle PL/SQL/System Packages/DBMS ALERT

dbms_alert.signal

  
SQL>
SQL> begin
  2      dbms_alert.signal( "WAITING", "" );
  3      commit;
  4  end;
  5  /
PL/SQL procedure successfully completed.
SQL>
SQL>

dbms_alert.waitone

  
SQL>
SQL> create table myTable as select rownum id, a.* from all_objects a where rownum < 20;
Table created.
SQL>
SQL>
SQL> declare
  2      myLong   long;
  3      myNumber number;
  4  begin
  5      dbms_alert.register( "WAITING" );
  6      for i in 1 .. 999 loop
  7          dbms_application_info.set_client_info( i );
  8          dbms_alert.waitone( "WAITING", myLong, myNumber, 0 );
  9          exit when myNumber = 0;
 10          for x in ( select * from myTable order by 1, 2, 3, 4 )
 11          loop
 12              null;
 13          end loop;
 14      end loop;
 15  end;
 16  /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> drop table myTable;
Table dropped.

Receiving an alert using DBMS_ALERT.

   
set serveroutput on
DECLARE
  v_AlertName VARCHAR2(30) := "MyAlert";
  v_Message VARCHAR2(100);
  v_Status INTEGER;
BEGIN
  DBMS_ALERT.REGISTER(v_AlertName);
  
  DBMS_ALERT.WAITONE(v_AlertName, v_Message, v_Status);
  
  IF v_Status = 0 THEN
    DBMS_OUTPUT.PUT_LINE("Received: " || v_Message);
  ELSE
    DBMS_OUTPUT.PUT_LINE("WAITONE timed out");
  END IF;
END;
/

--

Register interest in an alert.

    
SQL> BEGIN
  2
  3    
  4    DBMS_ALERT.REGISTER("EVENT_MESSAGE_QUEUE");
  5
  6  END;
  7  /
PL/SQL procedure successfully completed.
SQL>
SQL>

Remove your registered interest in a DBMS_ALERT.

  
SQL> BEGIN
  2
  3    
  4    DBMS_ALERT.REMOVE("EVENT_MESSAGE_QUEUE");
  5
  6  END;
  7  /
PL/SQL procedure successfully completed.

Sending an alert through DBMS_ALERT.

   
set serveroutput on
DECLARE
  v_AlertName VARCHAR2(30) := "MyAlert";
BEGIN
  DBMS_ALERT.SIGNAL(v_AlertName, "Alert!  Alert!  Alert!");
  
  COMMIT;
END;
/

--