Lesson 5	Developing an Advanced Queuing Application Setup
Objective	Use PL/SQL to create an Advanced Queuing Application

Develop Oracle Queuing Application

The best way to understand how to use Advanced Queuing is to create a sample application that uses the feature. The next several lessons will walk you through the creation of a simple application that will be able to post a message to a queue, retrieve the message from the queue, and propagate a message to other queues.
The PL/SQL code shown in the lessons will focus on the procedural logic necessary to implement your queues and not display some of the surrounding syntax necessary to create PL/SQL packages and procedures. The complete code is available for download as part of the exercise following Lesson 9 in this module. This application will not use some of the more specific features of Advanced Queuing.

Additional Advanced Queuing Features in Oracle

This module is meant to give you an introduction to Advanced Queuing, but there are some more advanced aspects of this feature that will not be covered.
These include:

Navigating through messages in a queue
Prioritizing messages
Grouping messages together, so that a complex transaction can be broken up into a group of simpler messages that work together
Subscriber lists, which allow a single message to be queued to a group of other recipients or queues
Rule-based subscriber lists, which let you describe rules to determine the appropriate recipients on a subscriber list
Limiting the duration of a message: If a message is not dequeued in a particular window of time, it expires and disappears from the queue
Security on queues
Any of the capabilities of the queue monitor

Enhanced Messaging Gateway Agent in an Oracle Real Application Clusters (Oracle RAC) Environment

The Oracle Scheduler will be used to start Messaging Gateway agents. Messaging Gateway will leverage the Oracle Scheduler RAC service feature so that a Messaging Gateway agent is associated with a database service. If the instance on which a Messaging Gateway agent is running fails or is shutdown, the Oracle Scheduler will automatically restart the agent on another instance supporting that service.

Multiple Messaging Gateway Agents

Messaging Gateway is enhanced to enable multiple agents per instance and database. With this enhancement, you can now statically partition propagation jobs based on functionality, organizations, or workload and assign them to different MGW agents hosted by different database instances on different machines. This not only enables MGW to scale, but also enables propagation job grouping and isolation, which is important when MGW is used in a complicated application integration environment.

Simplified Messaging Gateway Propagation Job Configuration

An enhanced PL/SQL API consolidates the propagation subscriber and the propagation schedule into a new propagation job. It is now easier to create and schedule a propagation job for the messaging gateway.

Application Scenario

You will be building a mini-application to handle order processing for your pet store by using Advanced Queuing. When an order is placed, a message will be sent to a queue. That message will be read by the Billing Department and later propagated to an Oracle8i database at your supplier. With Advanced Queuing, you can effectively write an agent to read the messages and implement the actions necessary to process the order. The messages could come from your pet store, from another pet store, or from a remote sales rep.

Setting up the Queue

Before you can use a queue, you must set it up properly. You will initially need only a single queue for this sample application. To create the queue in the PETSTORE schema, use the four following PL/SQL calls:

Use this syntax:

 CREATE type PETSTORE.Message_typ as object (
 order_msg     VARCHAR2(100));

This PL/SQL call creates a message type that will be the "payload," or content, of the message. For the purposes of this application, the message will contain only brief text, which is shown below, but it could contain more structured and detailed information.

Use this syntax:

EXECUTE DBMS_AQADM.CREATE_QUEUE_TABLE (
queue_table       => 'PETSTORE.queue1',
queue_payload_type=> ''PETSTORE.Message_typ');

This PL/SQL call creates a queue table, which will store messages with a payload of the type created in the previous line of code. Although multiple queues can share a queue table, all the queues in a table must have the same message payload.

Once you have created the queue table, you can create a queue that uses the table. This procedure creates an actual queue, which is stored in the previously created queue table:

EXECUTE DBMS_AQADM.CREATE_QUEUE (
queue_name            => 'queue1',
queue_table           => ''PETSTORE.queue1');

The final step in initializing the queue is to start the queue with this PL/SQL command:

EXECUTE DBMS_AQADM.START_QUEUE (
queue_name => 'queue1');

Your queue is now ready to accept messages.
Setting Up Advanced Queue
In the next lesson, you will learn how to enqueue a message.

Setting up Using Advanced Queuing

You will be entering commands in the SQL*Plus Worksheet. You enter commands in the upper (white) window, and the results of the command are displayed in the lower (gray) window. Your first step is to create an object type that will act as the payload of your message. You use the
```
CREATE TYPE SQL
```
command, with a name for the type Message_typ, the keywords AS OBJECT, followed by an attribute of the type and a data type for the attribute (order_msg VARCHAR2(100)). Once you have entered the correct command, click the Execute button in the left toolbar, which is the second button from the top.
The results from the SQL statement indicate that you have successfully created the object type. Your next step is to create a queue table that uses the previously created message type. You will use the syntax of EXECUTE, which calls a PL/SQL procedure, the DBMS_AQADM.CREATE_QUEUE_TABLE procedure name, and two named parameters, the queue_table parameter, which should have the name 'queue1', and the queue_payload_type, which should use the name of the object type you created in the previous lesson. Once you have entered the correct command, click the Execute button in the left toolbar.
The results from the SQL statement indicate that you have successfully set up the queue table. Your next step is to create a queue with the DBMS_AQADM.CREATE_QUEUE() procedure call. You will have two named parameters: queue_name and queue_table, which you created in the previous step. Once you have entered the correct command, click the Execute button in the left toolbar.
The results from the SQL statement indicate that you have successfully set up the queue. Your final step is to start the queue. To accomplish this, you execute the START_QUEUE() procedure in the DBMS_AQADM package with one named parameter, the queue_name parameter, with the name of the previously created queue, 'queue1'. Once you have entered the correct command, click the Execute button in the left toolbar.
You have successfully set up a queue. This is the end of the simulation. Click Exit.