INPUTListing 20.5. Updating a record to trigger an alert.

UPDATE employee
   SET pay_rate = 75
   WHERE emp_id = 9109;
COMMIT;

When you execute the code in Listing 20.5, on the screen where you perform the update, your output should be

1 row updated

After the COMMIT has been executed, you should see

Commit complete

On the other screen that is monitoring the alert, your output should look like

NOTICE:  OLD ID: 9109 NEW ID: 9109 OLD Pay Rate: 20.5 NEW Pay Rate:  75
PL/SQL procedure successfully completed.

If the procedure ends without output, make sure that you have entered SET SERVEROUTPUT ON. The other possibility is that the UPDATE command did not complete before the time to wait for the alert elapsed or you forgot to commit the transaction.

Look at this! You now have someone going from $20.50 per hour to $75.00 per hour. This is reminiscent of the movie Superman III, in which Richard Pryor gives himself a huge raise after breaking into the payroll computer. This definitely bears investigation!

Using DELETE to Signal the Alert
With your two sessions still open, execute in one of the SQL*Plus screens the code in Listing 20.3. On the other screen, execute the code in Listing 20.6 to practice deleting a record. Before you execute the code from Listing 20.3, make sure that you have changed the time to wait to 600 seconds if you need the time to enter the SQL code in Listing 20.6.

INPUTListing 20.6. Deleting a record to trigger an alert.

DELETE from employee
    WHERE emp_id = 9109;
COMMIT;

When you execute the code in Listing 20.6, on the screen where you perform the delete, your output should be

1 row deleted

After the COMMIT has been executed, you should see

Commit complete

On the other screen that is monitoring the alert, your output should look like

NOTICE: OLD ID: 9109 NEW ID: OLD Pay Rate: 75 NEW Pay Rate:
PL/SQL procedure successfully completed.

If the procedure ends without output, make sure that you have entered SET SERVEROUTPUT ON. The other possibility is that the UPDATE command did not complete before the time to wait for the alert elapsed or you forgot to commit the transaction.

Security finally escorted the person who manipulated the payroll database out the door to the police waiting outside. Human resources then deleted this person from their system. There are no NEW values since you deleted this record! You can now run a query against the security database to show that the trigger did indeed work and place the data into the table security.

Viewing the Results of the Trigger in the security Database
To see the three rows in the security database, execute the following code line:

SELECT * from security;

When you execute this code line, your output should look like

OLD_EMP_ID OLD_EMP_NAME       SUPERVISED_BY OLD_PAY_RATE O OLD_EMP_DEPT_ID
---------- ------------------ ------------- ------------ - ---------------
NEW_EMP_ID NEW_EMP_NAME       SUPERVISED_BY NEW_PAY_RATE N NEW_EMP_DEPT_ID V
---------- ------------------ ------------- ------------ - --------------- -
CHANGED_ TIME_CHANGED
-------- ---------
      9109 Benjamin Franklin  209                   20.5 H              10
      9109 Benjamin Franklin  209                     75 H              10 N

SCOTT    29-JUN-97

      9109 Benjamin Franklin  209                     75 H              10 N

SCOTT    29-JUN-97


      9109 Benjamin Franklin  209                   20.5 H              10 N
SCOTT    29-JUN-97

Summary

Today you discovered how the DBMS_ALERT package works in Oracle. In order to receive an alert, you must first register the alert. You can then wait for one specific alert with WAITONE or poll for any registered alert with WAITANY. The procedure will be blocked until the wait time has expired, which returns a status of 1, or an alert is signaled, which returns a status of 0. Alerts are asynchronous, and can generally only communicate one way. A message can be sent with the alert up to 1800 bytes as one huge VARCHAR2 string.

Q&A

1. What is required to check for an alert?
2. You must first register the alert and then use WAITONE or WAITANY to wait for the alert to occur.
3. What is the difference between WAITONE and WAITANY?
4. WAITONE waits for one specific alert to occur as an event, whereas WAITANY polls for all alerts that are registered to that session.
5. Do you need to remove the alert after the session ends?
6. This is highly recommended to clean up the registration table of alerts and also to recover resources used by the alert process.
7. Why use alerts instead of pipes?
8. Alerts offer you the ability to broadcast a message to all sessions monitoring the alert. The DBMS_PIPE package will only send the message to the first session that reads the pipe. In addition, pipes do not offer ROLLBACK to remove the message waiting for pickup.
9. Why should you change the polling interval when using WAITANY?
10. If you do not create a short polling interval, when WAITANY awakes and goes to poll, it will receive only the most recent alert, ignoring any that might have occurred while the process was in sleep mode.

Workshop

Use the following workshop to test your comprehension of this chapter and put what you've learned into practice. You'll find the answers to the quiz and exercises in Appendix A, "Answers."

Quiz

1. What is the maximum length of an alert name?
2. What is the maximum length of the message?
3. What datatype is the message sent as?
4. If 20 sessions are monitoring for an alert and the alert is sent, how many of those sessions receive the signaled alert?
5. Alerts require a(n) __________ because you are dealing more on a transactional level, whereas pipes do not.

Exercises

1. Change the code in Listing 20.3 to wait for any alert, and also register for two more alerts called `my_test' and `extra_alert'. Store the name of the alert that is signaled in a variable entitled alert_name of type VARCHAR2(30). After the alert has been handled, remove all registered alerts.
2. Write a loop that will continually execute until the value of FIRE equals 1, which will then trigger the alert called `a_fire', which will pass the message `A Fire has Broken Out'.