Oracle PL/SQL/System Packages/DBMS ALERT
Содержание
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>