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.