Oracle PL/SQL/System Packages/DBMS ALERT

Материал из SQL эксперт
Перейти к: навигация, поиск

dbms_alert.signal

   <source lang="sql">
 

SQL> SQL> begin

 2      dbms_alert.signal( "WAITING", "" );
 3      commit;
 4  end;
 5  /

PL/SQL procedure successfully completed. SQL> SQL>


 </source>
   
  


dbms_alert.waitone

   <source lang="sql">
 

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.


 </source>
   
  


Receiving an alert using DBMS_ALERT.

   <source lang="sql">
  

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

--


 </source>
   
  


Register interest in an alert.

   <source lang="sql">
   

SQL> BEGIN

 2
 3    
 4    DBMS_ALERT.REGISTER("EVENT_MESSAGE_QUEUE");
 5
 6  END;
 7  /

PL/SQL procedure successfully completed. SQL> SQL>



 </source>
   
  


Remove your registered interest in a DBMS_ALERT.

   <source lang="sql">
 

SQL> BEGIN

 2
 3    
 4    DBMS_ALERT.REMOVE("EVENT_MESSAGE_QUEUE");
 5
 6  END;
 7  /

PL/SQL procedure successfully completed.


 </source>
   
  


Sending an alert through DBMS_ALERT.

   <source lang="sql">
  

set serveroutput on DECLARE

 v_AlertName VARCHAR2(30) := "MyAlert";

BEGIN

 DBMS_ALERT.SIGNAL(v_AlertName, "Alert!  Alert!  Alert!");
 
 COMMIT;

END; /

--


 </source>