PL/SQL Propagation Functionality

The actions you have implemented so far illustrate queuing in its most basic form. You created a queue, a procedure to enqueue a message to the queue, and a message to dequeue a message from the queue. You can also propagate messages from one queue to another to implement more complex queuing applications. Propagating messages is the process of moving a set of messages from one queue to one or more other queues.

There are many reasons why you might want to propagate messages from one queue to another.
The primary reason is that the logic of your application requires it. Your application might have a single "post office" message queue for receiving requests, and an agent propagates the messages in the queue to a number of other queues for action. You may also wish to publish a single message to many other agents for informational or implementation purposes. Advanced Queuing is also widely used to implement workflow applications, where a message is propagated from one staging queue to another as the process step represented by that queue is completed. In addition to the design flexibility, Advanced Queuing can also be used to increase the efficiency of communications between databases. By propagating at specific times, you can essentially "batch" the communication of messages between queues in different databases in a way to reduce the impact of propagation on the overall system.

There are two basic steps for implementing propagation:

1. Define a queue or queues to receive the propagation: You can propagate to a queue or to a list of queues. The queue you use as the source for the propagation must be defined with the MULTIPLE_RECIPIENTS parameter set to TRUE.
2. Schedule a propagation: When you schedule a propagation, you can also specify the propagation to recur at specified intervals.

An optional intermediate step would be to define a subscriber list for the source queue, which would specify the queues to which the propagation would occur. You can also disable and enable existing propagation schedules. The DBMS_AQADM package contains all the PL/SQL procedure calls to implement and administer propagation.

Assuming that the appropriate destination queue is set up–in this case, a queue with the same message payload as the msg_queue previously defined called other_queue– you can enable propagation with a single call:

EXECUTE DBMS_AQADM.SCHEDULE_PROPAGATION(
queue_name   => 'queue1', 
destination  => 'queue2');

Oracle Propagation Functionality
In the next lesson, you will run the application you have created.