Set up PL/SQL Procedure for Propagation Functionality to enqueue Message

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.

• Why propagate?
  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');

This simple call triggers a single propagation to the other_queue message queue. If there were no value for the destination parameter, the message queue would propagate to all other queues on the local machine.
The following series of images illustrates the process of propagation:

Oracle Propagation Functionality

Oracle has many replication mechanisms that require data propagation:

1. Snapshot and Multimaster replication: Uses snapshots for data propagation.
2. Streams Replication: Uses Oracle queues (Asynchronous change data capture) for data propagation. You define the propagation rules using the dbms_repcat package.
3. Oracle Data Guard replication - Uses redo logs for data propagation.

• Staging and Propagation:
  The captured LCRs must be made available to the subscribers and consumers. Oracle Streams achieves this by staging the LCRs for propagation.

1. Staging: All captured messages are stored in a staging area. The staging area is an in-memory buffer and is part of the system global area (SGA) of the database instance. Messages created by user applications are also stored in this staging area. The LCRs created by the synchronous capture process are not stored in the memory queue, but they are stored in a disk queue table. The messages remain in the staging area until consumed by the subscribers. The subscriber can read the contents of the staging area to select messages of their interest. The subscriber can be an application, a staging area, or the apply process of a different database. The applications can explicitly dequeue or read the message from the staging area to consume it. If the subscriber to this staging area is an apply process, then the messages will be dequeued and applied by the apply process.
2. Propagation: Messages in one staging area can be propagated to another staging area in another database using database links over Oracle Net. Streams offers a great deal of flexibility in choosing how messages are routed. Rules can be applied during propagation to select which messages will be propagated to another staging area. You can modify existing propagation rules or define your own rules. In some cases, the propagation may not be required. The messages can be dequeued by the consumer from the same staging area where the capture process created the message. In this case, the publisher and consumer processes run in the same database.
   In the next lesson, you will run the application you have created.