Using the System Package dbms_alert monitoring table

Source: http://blog.csdn.net/edcvf3/article/details/8285328

Dbms_alert supports asynchronous capture of database events.

Now test its capture table for various changes, the test table is EMP.

Because it is asynchronous, we need to open 2 Sqlplus windows (two sessions)

The first session writes the following stored procedures:

(Note that to give Scott user permission first,

Grant execute on Dbms_alert to Scott)

[SQL]View PlainCopyprint?

2. Declare
4. V_n VARCHAR2 (30);
6. V_m varchar2 (100);
8. V_i Integer;
10. Begin
12. Dbms_alert.removeall;
14. Dbms_alert.register (' Emp_table_alert ');
16. Dbms_alert.waitany (v_n,v_m,v_i);
18. If l_i=0 Then
20. Dbms_output.put_line (' [name:] ' | |  L_n);
22. Dbms_output.put_line (' [msg:] ' | |  L_M);
24. End If;
26. Dbms_alert.removeall;
28. End
30. /

After executing this stored procedure, this session will be in the waiting state ...

We re-open a sqlplus and set up the trigger as follows:

[SQL]View PlainCopyprint?

1. Create or replace trigger emtring
2. After insert or update or delete on emp
3. Begin
4. Dbms_alert.signal (' Emp_table_alert ', To_char (sysdate,' YYYYMMDD ') | |  ': Table emp is changed ');
5. End

Where dbms_alert.signal is used to send alerts, and under this session, make some changes, such as insert

[SQL]View PlainCopyprint?

1. INSERT INTO emp (empno,ename) values (1234,' w001 ');
2. Commit

Note that you must commit, because Dbms_alert is transaction-based.

Once commit, the waiting state of the session in the first sqlplus is ended, and the output is as follows:

[SQL]View PlainCopyprint?

1. 10:18:18 [Email protected]>/
2. [Name:]emp_table_alert
3. [msg:]20121212:table emp is changed

This package is typically used in development, capturing various alerts in the DB that occur in application for processing.

Using the System Package dbms_alert monitoring table