Using a Queue with PL/SQL in a Point-to-Point Model

Introducing the Java Samples

The Publish-Subscribe Model

The Java Message Service JMS

Other Words for Same Things

Queue Creation


Oracle introduced powerful queuing mechanisms where messages can be exchanged between different programs. They called it Advanced Queuing AQ. Exchanging messages and communicating between different application modules is a key functionally becoming important as soon as we leave the database servers SQL and PL/SQL programming domain.

If we have to do different jobs simultaneously, for instance to communicate with external systems and evaluate complex queries at the same time, it might be a design decision to uncouple "request for service" and "supply for service". In one case an application module deals with all external systems and requests a certain query by posting a message on a queue. In the other case an application gets the message, performs the query and supplies the result back on the queue in between.

While using Oracle Advanced Queuing we do not have to install additional middle-ware dealing with inter-process communication and transaction monitoring. We can directly use an existing and well-known database and can benefit from given functionalities like online backup or transaction processing. Alternatively other simple and non queue based messaging techniques can be used like the Java RMI, which is limited to Java. Or more complex approaches like CORBA, where the complexity lies more in design and conceptual decisions.

Part I: PL/SQL and Oracle's Native AQ for Java

In a simple system we could think about two applications that like to use one or more queues together. This approach is called the Point-to-Point Model:

The process to put messages on a queue is called enqueue or send whereas the opposite is called dequeue. There may be more than two consumer applications but a single message can only be dequeued once. Consumers may browse the queue without dequeuing messages.

In a more advanced system we may like to have different applications that publish messages and others that subscribe to certain queues from where they like to consume messages. There is no more strict connection between applications. This is called the Publish-Subscribe Model and is covered by Part II of these articles.

For database queue creation we should have an AQ administrator user with the required privileges. It can be used as object owner too. All created queues and message object types will belong to this administrator. Afterwards we can create as many queue users as we like or grant the required privileges to existing users who want to access the queues. To avoid maintaining privileges for every single user, we will create two roles in this sample. One for the AQ administrator and another for all AQ users.

In these samples the administrator role is called "my_aq_adm_role" and the corresponding user "aqadm". We grant Oracle's AQ role "aq_administrator_role" to our administrator role.

CREATE ROLE my_aq_adm_role;

GRANT CONNECT, RESOURCE, aq_administrator_role

TO my_aq_adm_role;

The user role is called "my_aq_user_role" and the corresponding sample user "aquser". Here we grant Oracle's AQ role "aq_user_role" and additional system privileges required for basic operations.

CREATE ROLE my_aq_user_role;

GRANT CREATE SESSION, aq_user_role TO my_aq_user_role;


privilege => 'ENQUEUE_ANY',

grantee => 'my_aq_user_role',

admin_option => FALSE);


privilege => 'DEQUEUE_ANY',

grantee => 'my_aq_user_role',




GRANT my_aq_adm_role TO aqadm;

And the queue user for our samples:




GRANT my_aq_user_role TO aquser;

For our first queue we will use an object type instead of a base data type like NUMBER or VARCHAR2 as payload. The payload is the data type and structure used for every message. To use an object type is more realistic than sending single numbers or strings around but a bit more complicated. In a message we might have an identification number, a title and a message text or content.

It's time now to change to the AQ administration user where the previous operations could be performed by any DBA.

CONNECT aqadm/aqadm;

CREATE TYPE queue_message_type AS OBJECT(


title VARCHAR2(30),

text VARCHAR2(2000) );

GRANT EXECUTE ON queue_message_type TO my_aq_user_role;

Let's create a queue called "message_queue" with a corresponding queue table "queue_message_table". We start the queue so that it can be used from now on.


queue_table => 'queue_message_table',

queue_payload_type => aqadm.queue_message_type');


queue_name => 'message_queue',

queue_table => 'queue_message_table');


queue_name => 'message_queue');

Now we have a complete queue that is ready to use. All the administrative PL/SQL operations shown are available in Java too. However it's a handy idea to do these steps in a SQL shell.

To work with queues we connect with our AQ sample user:

CONNECT aquser/aquser;

Now we like to enqueue a message. We have to name the queue, give some default options and pass our message "my_message" as payload, which is made by our own defined message. Remember, we live in a transactional environment. We must issue a final COMMIT.

CONNECT aquser/aquser;



queue_name => 'aqadm.message_queue',

enqueue_options => queue_options,

message_properties => message_properties,

payload => my_message,

msgid => message_id);



We can dequeue the recently enqueued message. The DBMS_AQ.DEQUEUE statement waits until there is a message to dequeue. The shown code looks very similar to the one above.

queue_name => 'aqadm.message_queue',

dequeue_options => queue_options,

message_properties => message_properties,



'Dequeued no: ' ||;

'Dequeued title: ' || my_message.title);


'Dequeued text: ' || my_message.text);


The PL/SQL samples were easy and straightforward. Not a lot to do. Every kind of application and programming environment could use it like this, assuming they are able to connect to the database and execute PL/SQL stored procedures. However, it is more convenient and is better practise to use the programming languages' own way to deal with messages. That's the point where we should have a look what Java offers.

While using Java, it's not only the different programming syntax we use but also the way we design programs. We leave the procedural area and enter into the object oriented world. In these samples we use an abstract base class AQApplication to hide all the steps we must perform before we are able to start working with queues.

This UML diagram shows our sample class AQDequeue derived from AQApplication. We will focus down to those statements we must know about, in order to work with queues. It's not necessarily required to understand the whole object oriented concept.

When we created our queue we created also an Oracle object type to be used for messages. Because we cannot use Oracle data types in Java we must have a Java class to fill the dequeued message in. JPublisher can do this job for us. It connects to the database and creates a Java class matching the specified Oracle object type.





or more object types and packages that you want JPublisher to translate. Use commas for separation.


The oracle mapping maps Oracle datatypes to their corresponding Java classes.


If true, JPublisher generates SQLJ classes for PL/SQL packages and wrapper methods for methods in packages and object types. SQLJ wraps static SQL operations in Java code. We do not use SQLJ here, thus we pass false.

JPublisher connects to the database and creates a Java class QUEUE_MESSAGE_TYPE for us. We can use this class now as a Java data type to receive messages posted by another Java or PL/SQL client.

Dequeue a Point-to-Point Message with Oracle's

Native AQ Interface for Java

We use the previously shown PL/SQL sample and replace the dequeue functionality by Java to show the similarities. Additionally it can be used to show message exchanging between PL/SQL and Java. Enqueuing in Java is very similar again to dequeuing and is therefore not shown here.

Before we can start using Oracle's Native AQ Interface for Java we must connect to the database via JDBC. As a connection string we use a host name for HOST and an Oracle database SID for SID. Between these two values the listener port address must be specified, e.g. 1521.



aq.connection = DriverManager.getConnection(


"aquser", "aquser");


Afterwards we create a so-called AQ session passing the AQ connection:


aq.session = AQDriverManager.createAQSession(aq.connection);

Now we're ready to get a reference to the queue we like to use. To do so, we pass the queue owner and the queue name:

AQQueue queue = aq.session.getQueue(

"aqadm", "MESSAGE_QUEUE");

For dequeuing we create default options and pass them along with an instance of JPublisher's created message data type QUEUE_MESSAGE_TYPE.

AQDequeueOption dequeueOption = new AQDequeueOption();

System.out.println("Waiting for message to dequeue. ");

AQMessage message = ((AQOracleQueue)queue).dequeue(



To get the message content we convert the raw payload into our message type.


System.out.println("Dequeued title: " +


System.out.println("Dequeued text: " +


Like in PL/SQL, we need a final COMMIT.

Oracle Advanced Queuing is a powerful and rather simple way of working with queues. The libraries available for Java offer a smart way to enqueue and dequeue messages without too much programming overhead.

In more sophisticated projects Advanced Queuing's whole functionality can be taken into account. Oracle's Advanced Queuing developer's guide exceeds thousand pages. This might be seen as an indication that in advanced projects a reasonable amount of time is required to understand the different concepts and possibilities. This should not be seen as a disadvantage for Oracle's Advanced Queuing, the same is true for other similar communication or queuing technologies.

Only essential issues and aspects have been covered by this article. Part II of these articles will cover somewhat more sophisticated concepts such as publishing and subscribing a message with Oracle's Java Message Service JMS Interface to AQ.

Part II: Oracle's JMS Interface to AQ

Oracle Advanced Queuing AQ is a powerful queuing mechanism for message exchanging between different applications. Part I of these articles introduced AQ and explained how to create queues in the database, use PL/SQL in a Point-to-Point Model, JPublisher and Oracle's Native AQ Interface for Java.

In Part II complete samples are available, showing all the required statements, environment variables, imports, JAR files, error processing's along with Windows batch files to compile and run etc.

In a more sophisticated system we may like to have different applications that publish messages and others that subscribe to certain queues from where they like to consume messages. The Publish-Subscribe Model uses multi-consumer queues:

Publisher applications propagate messages to queues which are called topics here. These messages can be either addressed for specific applications or they will be received by all destinations. Applications receiving messages are also called agents. We talk about broadcast if the message can be consumed by all applications and about multicast if a subscription is required for consummation.

To explain broadcast and multicast more clearly the following parallel cases are often used: Broadcast is similar to radio and TV broadcasting received by everybody. Multicast can be seen like a newspaper where you need a subscription. Many people have a subscription but not everybody.

Different enterprise messaging vendors lead by Sun Microsystems, Inc. defined a common API for reliable and flexible message exchange in distributed systems throughout an enterprise. Oracle is one of the companies that implemented JMS and decided to do it by their own Advanced Queuing feature. Other companies implement JMS using another technology. The underlying technology remains exchangeable and developers do not need to learn always proprietary messaging API's.

We will use Oracle's JMS Interface to AQ that implements an interface for Advanced Queuing.

If we talk about queues while using Advanced Queuing, we call the same Topics if we are in the world of JMS. The same is true for enqueue and dequeue. They're called Publish and Receive in JMS. Additionally applications are often called Agents in JMS.

Required database users creation and granting of needed privileges has been described in Part I of these articles. We created an AQ administration user "aqadm" and a AQ application user called "aquser" for the samples.

A queue table "multi_message_table" with a special object type AQ$_JMS_OBJECT_MESSAGE will be created now. This object type is a reference only and does not yet define the message structure. It gives us the freedom to define later the payload of our messages we like to transfer. The payload is the data type and structure used for every message.

Creating and starting the queue "multi_queue" works in the same way as for the Point-to-Point connection, except that the parameter "multiple_consumers" is set to true.

We use the AQ administration user "aqadm":

CONNECT aqadm/aqadm;


queue_table => 'multi_message_table',

queue_payload_type => 'SYS.AQ$_JMS_OBJECT_MESSAGE',

multiple_consumers => TRUE);


queue_name => 'multi_queue',

queue_table => 'multi_message_table');


queue_name => 'multi_queue');

When using Java we leave the procedural area and step into the object oriented world. The abstract base class AQApplication offers all required steps we must perform before we are able to start working with queues.

The UML diagram shows our sample classes AQJmsPublisher and AQJmsSubscriber derived from AQApplication. They will be used to act as publishers and subscribers.

Both classes instantiate the payload class AQJmsMultiQueueItem as message data type.

We will focus down to those statements we must know about, in order to work with queues. It's not necessarily required to understand the whole object oriented concept.

In our sample application the following class is used as payload for message content. We are free in choosing the member variables and methods. However, the class must implement the Serializable interface.

The Serializable interface in the library is used for object serialisation. Serialisation means, exchanging objects between programs on the same machine and between remote computers. The objects are transferred via streams and networks conserving their current states and data. They are restored by receivers, become alive again and continue to work.

public class AQJmsMultiQueueItem implements Serializable <


Category: Forex

Similar articles: