Advanced Queuing   «Prev  Next»

Lesson 9Running the Application
Objective Run the application you have created with Advanced Queuing

Run Advanced Queuing in Oracle

You have now walked through all the code you need for a simple application that uses Advanced Queuing. There are four parts to this application.

Initialization

Before you can use the features of Advanced Queuing, you have to set up a queue to use, involving four steps:
  1. Create an object type that will act as the message payload of the message.
  2. Create a queue table that will hold the queue.
  3. Create the queue itself and assign it to the message table.
  4. Start the queue.
The first three actions need to be executed only once the object type, queue table, and queue are persistent objects in the Oracle8i database. You have restart the queue every time you restart your Oracle8i database.
For the purposes of this application, you also created a second queue to demonstrate propagation.

Enqueuing

Once the queue is operational, a user can send a message to the queue. Enqueuing a message essentially consists of calling a single PL/SQL procedure, DBMS_AQ.ENQUEUE(), with the appropriate parameters and payload. You use the ENQUEUE() procedure, which is a simple wrapper[1] for the DBMS_AQ.ENQUEUE() procedure with default values for the parameters.

Dequeuing

You also use a single PL/SQL procedure to dequeue a message from the queue. You have a variety of options you can set on the dequeuing operation, such as which message to get and how to treat the message once it is retrieved. You use the DEQUEUE() procedure, which is a simple wrapper for the DBMS_AQ.DEQUEUE() procedure with default values for the parameters.

Propagation

Although you might want eventually to propagate your messages to other queues, for the purposes of this application, you will not use the propagation feature of Advanced Queuing.

Shutdown

The final step in using your application is to run a script file to close the queue, drop the queue, and drop the queue table. Normally, you would not use this script, because you want your queues to remain active, but the script is provided so you can return your environment to its original state.

Checking your results

To see the results of your queuing actions, you will have to use some SQL statements to check the contents of a queue.
There are two SQL statements you will use to check the status of the queues you create.
  1. The first SQL statement is used to ensure that the queues exist, that the startup script you used created the queue table and
  2. the queues and started the queues:

SELECT * FROM USER_QUEUES;

You will also use this same SQL statement to check to make sure that the cleanup script, which stops and drops the queues and then drops the queue tables, works correctly. The second SQL statement is used to check a view to show the messages in the queue table named QUEUE1. 
SELECT QUEUE, MSG_ID, 
TO_CHAR(enq_time, 'MON DD HH:MI:SS') 
FROM AQ$QUEUE1

You must use the TO_CHAR function to show the hours, minutes, and seconds for the time a message was enqueued–the default display of the date shows only the month, date, and year. The next lesson is the module conclusion.

Queuing and Dequeuing Messages

The following sequence of steps describes queuing and dequeuing messages.

Running Oracle application with Advanced Queuing

  1. 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 check to see whether any queues currently exist by entering a SQL statement to query the USER_QUEUES view. Once you have entered the correct command, click the Execute button in the left toolbar, which is the second button from the top.
  2. The results from the SQL statement indicate that there are no queues currently in your schema. You begin by entering a command to run a script file called START.SQL. You can do this by preceding the path name for the file with the @ sign. You do not have to include the .SQL extension, because it is assumed by SQL*Plus. For the purpose of this exercise, you can leave off the path name. Once you have entered the correct command, click the Execute button in the left toolbar.
  3. The script file executed three PL/SQL commands to create a queue table, to create a queue, and to start a queue. To check your work, enter the SQL command to see whether any queues now exist. Once you have entered the correct command, click the Execute button in the left toolbar.
  4. You can see that your actions resulted in the creation of two queues: the queue you specified and the exception queue for the queue table. An exception queue is automatically created once the first queue is added to a queue table. Now that your queue is in place, you can execute the ENQUEUE() procedure. This procedure places a sample message into the QUEUE1 queue. Once you have entered the correct command, click the Execute button in the left toolbar.
  5. You can see that the ENQUEUE() procedure successfully executed. To check for the message in the message queue, you should use a SQL command to query the AQ$QUEUE1 view, which gives you access to the messages in that queue table. You should query for the QUEUE column and the MSG_ID column, and use the TO_CHAR(ENQ_TIME, 'MON DD HH:MI:SS') function to format the time value in the ENQ_TIME column properly. Once you have entered the correct command, click the Execute button in the left toolbar.
  6. You can see that you have a single message waiting in the queue. Use the ENQUEUE() procedure to add another message to the queue. Once you have entered the correct command, click the Execute button in the left toolbar.
  7. Add another message to the queue by executing the procedure again. Because the procedure is already in the command window, you can simply click the Execute button in the left toolbar again.
  8. Check for messages in the queue by using the same SQL command you used in the earlier screen. Once you have entered the correct command, click the Execute button in the left toolbar.
  9. You can now see that there are three messages, with slightly different MSG_ID values and different time stamps. Your next step is to execute the DEQUEUE() command to take a message from the queue. Once you have entered the correct command, click the Execute button in the left toolbar.
  10. The procedure executed successfully. Your next step is to check the messages in the queue with the appropriate SQL command once again. Once you have entered the correct command, click the Execute button in the left toolbar.
  11. You can see that the first message you placed in the queue has been consumed by the DEQUEUE() procedure because no selection conditions or priorities were specified in this generic procedure. Your last functional step is to stop the queue, drop the queue, and then drop the queue table by running the STOP.SQL script. In a real-life situation, you would not normally drop a queue, especially one that had messages in it. You can simply run the STOP.SQL script without preceding the @ with a path name. Once you have entered the correct command, click the Execute button in the left toolbar.
  12. To check the result of the cleanup script, query the USER_QUEUE table again to see whether any queues remain. Once you have entered the correct command, click the Execute button in the left toolbar.
  13. You have cleaned up the queues properly. This is the end of the simulation. Click Exit.

Using Advanced Queuing
[1]Wrapper: Code that wraps a type of logic with some other code to make a cleaner interface.