Oracle Built−in Packages- P66: 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 WHEN THEN No more students no more message groups. NULL END Working with Multiple Consumers In the simpler schemes of queueing one producer puts a message on a queue and another agent a consumer retrieves that single message from a queue. A common variant of this process follows a broadcasting model where a producer enqueues a message with the intention of distributing that message to many consumers. Oracle AQ allows you to perform this kind of broadcast in two different ways 1 1 Prior to Oracle AQ the DBMS_ALERT package already supported this broadcast mechanism I would not be surprised to find that DBMS_ALERT is redesigned to use AQ in a future release. Define a default subscriber list for a queue. Then any message that is placed on that queue is available for dequeuing by any of the agents in that subscriber list. Specify an override recipient list when you enqueue a specific message to the queue by assigning a list index-by table of recipients to the recipient_list field of the message properties record. In both of these cases you must have defined the queue table in which your queue is defined to support multiple consumers. Here is an example of the creation of a queue table that supports multiple consumers BEGIN queue_table msg queue_payload_type message_type multiple_consumers TRUE END Let s take a look at the different steps involved in using both the default subscriber list and the override recipient list. Suppose that in my student registration and management system I want to define a default set of subscribers who are to receive notification of a student s change in major_pkg. When the student changes his or her major from mathematics or philosophy to business however notification is sent to the school psychologist and the professor of ethics. I will demonstrate these techniques by constructing incrementally a package that supports the change-major operation.
