Oracle Built−in Packages- P41: Ah, for the good old days of Version of PL /SQL! Life was so simple then. No stored procedures or functions and certainly no packages. You had your set of built−in functions, like SUBSTR and TO_DATE. You had the IF statement and various kinds of loops. With these tools at hand, you built your batch−processing scripts for execution in SQL*Plus, and you coded your triggers in SQL*Forms , and you went home at night content with a good day's work done. | Appendix A What s on the Companion Disk Exceptions This program does not raise any package exceptions. The program will raise an ORA-20000 exception for specific error conditions with message text indicating the error as follows ORU-10019 Error n on lock request ORU-10023 Lock request error status n ORU-10037 Attempting to wait on uncommitted signal from same session Restrictions Note the following restrictions on WAITONE The message parameter is limited to 1800 bytes in length. The WAITONE procedure cannot be called in SQL. Example This example waits specifically on the EMP_INSERT alert and updates the status when it is signaled DECLARE alert_msg VARCHAR2 1800 alert_status INTEGER BEGIN EMP_INSERT alert_msg alert_status 300 IF alert_status 1 THEN timed out ELSE UPDATE emp SET status REGISTERED WHERE empid alert_msg employee registered END IF END DBMS_ALERT Examples The DBMS_ALERT package is a good example of how you can build higher-level functionality out of lower-level built-ins. Both the DBMS_LOCK and DBMS_PIPE packages are used extensively in the implementation of DBMS_ALERT. NOTE If you have an old Version installation of Oracle you can check out to see exactly how this is done since the code is not wrapped. The DBMS_ALERT Interface 191 Appendix A What s on the Companion Disk An important feature of the alerting mechanism in DBMS_ALERT is that it is transaction-based. This means that alerts will be sent to registered sessions only if and when the signaling session issues a COMMIT. If the signaler issues a ROLLBACK instead the alerts will not be sent. Applications that are interested only in real changes to data in the database will benefit from using transaction-based alerts. Applications that need to signal other sessions regardless of transaction boundaries or data modifications like debuggers or auditing monitors will probably need to .
