Advanced Queuing   «Prev  Next»

Lesson 4Introducing PL/SQL applications for Advanced Queuing
Objective Describe the prebuilt PL/SQL Packages used to create an Advanced Queuing Application

PL/SQL prebuilt Package Applications for Queuing

Oracle comes with a number of prebuilt PL/SQL packages[1] that you use to implement Advanced Queuing. The two basic packages are called DBMS_AQ and DBMS_AQADM. During the rest of this module, as you create your own sample advanced queuing application, you will be using many of the procedures[2] contained in these packages.

DBMS_AQ

The DBMS_AQ package contains the basic procedures necessary to use queues. These procedures are:
  1. ENQUEUE: To add a message to a queue
  2. DEQUEUE: To take a message from a queue
  3. LISTEN: To wait on one or more queues for the arrival of incoming messages on behalf of an agent

The DBMS_AQ package also includes type definitions, such as object names, agents, and recipient, agent, and subscriber lists, for most of the objects needed to uses queues. Type definition: Defines an Oracle8i object type.

DBMS_AQADM

The DBMS_AQADM package contains procedures to administer queues. These procedures include:
  1. CREATE_/ALTER_/DROP_QUEUEs: To manage queues
  2. CREATE_/ALTER_/DROP_QUEUE_TABLE: To manage queue tables
  3. START_/STOP_QUEUE: To control the operation of a queue
  4. GRANT_/REVOKE_QUEU_PRIVILEGE: To control security on a queue
  5. ADD_/ALTER_/REMOVE_SUBSCRIBER: To manage subscribers to a queue
  6. SCHEDULE_/UNSCHEDULE_PROPAGATION: To control the propagation of messages from a queue
  7. ENABLE_/DISABLE_ALTER_PROPAGATION_SCHEDULE: To control the schedule of the propagation of messages from a queue

SET_ENQUEUE_DESTINATION Procedure

This procedure sets the queue where the apply process automatically enqueues a message that satisfies the specified rule. This procedure modifies the specified rule's action context to specify the queue. A rule action context is optional information associated with a rule that is interpreted by the client of the rules engine after the rule evaluates to TRUE for a message. In this case, the client of the rules engine is an Oracle Streams apply process. The information in an action context is an object of type SYS.RE$NV_LIST, which consists of a list of name-value pairs. A queue destination specified by this procedure always consists of the following name-value pair in an action context:
  1. The name is APPLY$_ENQUEUE.
  2. The value is a ANYDATA instance containing the queue name specified as a VARCHAR2.
Syntax 
DBMS_APPLY_ADM.SET_ENQUEUE_DESTINATION(
rule_name IN VARCHAR2,
destination_queue_name IN VARCHAR2);

SET_ENQUEUE_DESTINATION Procedure Parameters
Table 6-4: SET_ENQUEUE_DESTINATION Procedure Parameters

Usage Notes

If an apply handler, such as a DML handler, DDL handler, or message handler, processes a message that also is enqueued into a destination queue, then the apply handler processes the message before it is enqueued. The following are considerations for using this procedure:
  1. This procedure does not verify that the specified queue exists. If the queue does not exist, then an error is raised when an apply process tries to enqueue a message into it.
  2. Oracle Streams capture processes, propagations, and messaging clients ignore the action context created by this procedure.
  3. The apply user of the apply processes using the specified rule must have the necessary privileges to enqueue messages into the specified queue. If the queue is a secure queue, then the apply user must be a secure queue user of the queue.
  4. The specified rule must be in the positive rule set for an apply process. If the rule is in the negative rule set for an apply process, then the apply process does not enqueue the message into the destination queue.
  5. If the commit SCN for a message is less than or equal to the relevant instantiation SCN for the message, then the message is not enqueued into the destination queue, even if the message satisfies the apply process rule sets.
In the next lesson, you will start to build a sample application that uses Advanced Queuing.
[1] Package: A group of PL/SQL procedures that are combined into a single logical grouping. The package header contains the interface description for the procedures, whereas the package body contains the actual code for the procedures in the package.
[2] Procedure: Uses PL/SQL code and syntax to implement a logical operation.