original link: http://www.oracle-developer.net/display.php?id=411
Oracle Advanced Queuing Introduction
Advanced Queuing Advance Queuing (AQ) is available in multiple versions of Oracle. He is Oracle native messaging software and is being strengthened in every version.
This article provides a high-level overview of AQ. In particular, we will see how to start a queue and perform into row-dequeue operations, as well as creating asynchronous dequeue by notification.
Note AQ supports database unexpected message snooping (such as JMS Message Queuing). This article covers only database internal message traffic.
Requirements:
This example requires the specified roles and permissions (in addition to the standard create session/table/procedure/type and tablespace quotas)
1, Aq_administrator_role: Used to create queue tables and queues;
2. EXECUTE on Dbms_aq: To enable Plsql stored procedure compilation in a notification case
In addition, standard application users requiring into row/dequeue messages require AQ permissions through dbms_aqadm[grant| The Revoke]_queue_priviliege API provides.
The following example can be run under any user who has the above permissions.
1. Create and start a queue
The message that AQ processes is called "payload" (payloads). The message format can be either a user-defined object or a xmltype or anydata. When we create a queue, we need to tell Oracle
Payload structure, so we first create a simple object type.
CREATE TYPE Demo_queue_payload_type as OBJECT
(Message VARCHAR2 (4000));
/
Our payload type contains an attribute, which can be more complex in reality. The following Create queue table is used to store queue messages until they are permanently queued.
BEGIN
Dbms_aqadm. Create_queue_table (
queue_table = ' demo_queue_table ',
Queue_payload_type = ' Demo_queue_payload_type '
);
END;
/
Then create the queue and start:
BEGIN
Dbms_aqadm. Create_queue (
queue_name = ' Demo_queue ',
queue_table = ' demo_queue_table '
);
Dbms_aqadm. Start_queue (
queue_name = ' Demo_queue '
);
END;
/
At this point, we have created queue payloads, queue tables, and queues. See what the relevant objects are:
SELECT object_name, Object_type
From User_objects
WHERE object_name! = ' Demo_queue_payload_type ';
object_name object_type
------------------------------ ---------------
Demo_queue_table TABLE
sys_c009392 INDEX
sys_lob0000060502c00030$$ LOB
aq$_demo_queue_table_t INDEX
Aq$_demo_queue_table_i INDEX
Aq$_demo_queue_table_e QUEUE
Aq$demo_queue_table VIEW
Demo_queue QUEUE
We see a queue that brings out a series of auto-generated objects, some of which are used directly from behind. But interestingly, the second queue was created. This is known as the exception queue (exception
Queue). If AQ cannot receive messages from our queue, it is logged in the exception queue.
2. into row message (enqueuing messages)
We are ready to use DBMS_AQ. ENQUEUE API to into row a message. For the next example, we use the enqueue process to into row a single message.
Dbms_aq has a wide range of records and array types to support its interface and allows us to modify its behavior (we will see 2 such references in the following example).
DECLARE
R_enqueue_options Dbms_aq. enqueue_options_t;
R_message_properties Dbms_aq. message_properties_t;
V_message_handle RAW (16);
O_payload Demo_queue_payload_type;
BEGIN
O_payload: = Demo_queue_payload_type (' Here is a message ');
Dbms_aq. ENQUEUE (
queue_name = ' Demo_queue ',
Enqueue_options = R_enqueue_options,
Message_properties = R_message_properties,
Payload = O_payload,
MsgId = V_message_handle
);
COMMIT;
END;
/
It's easy to see the into row message. The into row operation is a basic transaction (like an INSERT into a queue table), so we need to commit.
3. Browsing messages (browsing messages)
Before we dequeue the message, we will "browse" the contents of the queue. First we can query the aq$demo_queue_table view to see how many messages have been into row. As we have seen earlier,
The view is in front of the dbms_aqadm. Create_queue creates a queue that is automatically generated.
SELECT COUNT (*)
From Aq$demo_queue_table;
COUNT (*)
----------
1
As we expect, there is only one message in the queue. We have 2 ways to browse the message content:
1) Direct Query view:
SELECT User_data
From Aq$demo_queue_table;
User_data (MESSAGE)
------------------------------------------------------------
Demo_queue_payload_type (' Here is a message ')
2) We can use Dbms_aq. DEQUEUE API browsing. As you can see from the name, this procedure is used to dequeue messages. In order to achieve the purpose of only browsing and not deleting, we can use
Dbms_aq. Browse modifies the Dequeue property (default is Dbms_aq. REMOVE).
DECLARE
R_dequeue_options Dbms_aq. dequeue_options_t;
R_message_properties Dbms_aq. message_properties_t;
V_message_handle RAW (16);
O_payload Demo_queue_payload_type;
BEGIN
R_dequeue_options.dequeue_mode: = Dbms_aq. BROWSE;
Dbms_aq. DEQUEUE (
queue_name = ' Demo_queue ',
Dequeue_options = R_dequeue_options,
Message_properties = R_message_properties,
Payload = O_payload,
MsgId = V_message_handle
);
Dbms_output. Put_Line (
' * * * * browsed message is [' | | o_payload.message | | ‘] ***‘
);
END;
/
Browsed message is [here is a message] * * *
Query the view again to determine that the message was not actually removed:
SELECT User_data
From Aq$demo_queue_table;
User_data (MESSAGE)
------------------------------------------------------------
Demo_queue_payload_type (' Here is a message ')
4. Dequeue message (dequeuing messages)
Now we will actually dequeue the message. This action does not require the same session to be performed (remember that into row is the AQ-based commit transaction for the table). Like into row, the dequeue is also a transaction (from the queue table
Remove the message).
DECLARE
R_dequeue_options Dbms_aq. dequeue_options_t;
R_message_properties Dbms_aq. message_properties_t;
V_message_handle RAW (16);
O_payload Demo_queue_payload_type;
BEGIN
Dbms_aq. DEQUEUE (
queue_name = ' Demo_queue ',
Dequeue_options = R_dequeue_options,
Message_properties = R_message_properties,
Payload = O_payload,
MsgId = V_message_handle
);
Dbms_output. Put_Line (
' * * * * dequeued message is [' | | o_payload.message | | ‘] ***‘
);
COMMIT;
END;
/
Dequeued message is [here is a message] * * *
PL/SQL procedure successfully completed.
Querying the view again reveals that the message has been checked out:
SELECT COUNT (*)
From Aq$demo_queue_table;
COUNT (*)
----------
0
5. Notice (notification)
For the remainder of the article, we'll look at automatic dequeue by notification. In this way, no matter when the message is into row, Oracle notifies an agent to perform a registered Plsql
The callback (callback) procedure (optionally, the agent can also notify an email address or http://Address).
To illustrate, we will create and register a plsql process to manage our dequeue through notification. This callback process will dequeue the message and write to a database table to simulate
Standard database operations.
As a start, we clean up the objects we created earlier.
BEGIN
Dbms_aqadm. Stop_queue (
queue_name = ' Demo_queue '
);
Dbms_aqadm. Drop_queue (
queue_name = ' Demo_queue '
);
Dbms_aqadm. Drop_queue_table (
queue_table = ' demo_queue_table '
);
END;
/
Now we re-create the queue table to allow multiple consumers (consumers). A consumer is a dequeue message agent (agent) enabling multiple consumers to be automatically notified of implementation
Prerequisites.
BEGIN
Dbms_aqadm. Create_queue_table (
queue_table = ' demo_queue_table ',
Queue_payload_type = ' Demo_queue_payload_type ',
Multiple_consumers = TRUE
);
END;
/
Then re-create and start our queue.
BEGIN
Dbms_aqadm. Create_queue (
queue_name = ' Demo_queue ',
queue_table = ' demo_queue_table '
);
Dbms_aqadm. Start_queue (
queue_name = ' Demo_queue '
);
END;
/
To prove the asynchronous nature of the notification, we will present a dequeue message in an application table.
CREATE TABLE demo_queue_message_table
(Message VARCHAR2 (4000));
Now that we have an application table, we can create callback PL/SQL. This process will dequeue the into row message that triggered the notification. Program parameters must be named and typed. The into row message will
Contains the into row timestamp so that it is inserted into the application table we will see the asynchronous delay of message into row and notification dequeue.
CREATE PROCEDURE Demo_queue_callback_procedure (
Context RAW,
Reginfo SYS. Aq$_reg_info,
Descr SYS. Aq$_descriptor,
Payload RAW,
PAYLOADL number
) as
R_dequeue_options Dbms_aq. dequeue_options_t;
R_message_properties Dbms_aq. message_properties_t;
V_message_handle RAW (16);
O_payload Demo_queue_payload_type;
BEGIN
R_dequeue_options.msgid: = descr.msg_id;
R_dequeue_options.consumer_name: = Descr.consumer_name;
Dbms_aq. DEQUEUE (
Queue_name = Descr.queue_name,
Dequeue_options = R_dequeue_options,
Message_properties = R_message_properties,
Payload = O_payload,
MsgId = V_message_handle
);
INSERT into demo_queue_message_table (message)
VALUES (' Message [' | | | o_payload.message | | '] ' | |
' dequeued at [' | | To_char (Systimestamp,
' Dd-mon-yyyy HH24:MI:SS. FF3 ') | | ‘]‘ );
COMMIT;
END;
/
We have not completed the notification step yet. We need to add a Subscriber (Subsriber) to the queue and register the subscriber's action when notified (for example, our callback process will be executed).
BEGIN
Dbms_aqadm. Add_subscriber (
queue_name = ' Demo_queue ',
Subscriber = SYS. Aq$_agent (
' Demo_queue_subscriber ',
Null
NULL)
);
Dbms_aq. REGISTER (
SYS. Aq$_reg_info_list (
SYS. Aq$_reg_info (
' Demo_queue:demo_queue_subscriber ',
Dbms_aq. Namespace_aq,
' Plsql://demo_queue_callback_procedure ',
Hextoraw (' FF ')
)
),
1
);
END;
/
Now we can test it with the into row message. This message will contain only one timestamp so that we can compare the time difference between into row and auto-dequeue.
DECLARE
R_enqueue_options Dbms_aq. enqueue_options_t;
R_message_properties Dbms_aq. message_properties_t;
V_message_handle RAW (16);
O_payload Demo_queue_payload_type;
BEGIN
O_payload: = Demo_queue_payload_type (
To_char (Systimestamp, ' dd-mon-yyyy HH24:MI:SS. FF3 ')
);
Dbms_aq. ENQUEUE (
queue_name = ' Demo_queue ',
Enqueue_options = R_enqueue_options,
Message_properties = R_message_properties,
Payload = O_payload,
MsgId = V_message_handle
);
COMMIT;
END;
/
In order to see if our hours are automatically dequeue, we will check the application table (demo_queue_message_table).
SELECT message
From Demo_queue_message_table;
MESSAGE
---------------------------------------------------------------------------
Message [21-jul-2005 21:54:51.156] dequeued at [21-jul-2005 21:54:56.015]
We can see that the asynchronous dequeue occurs by notifying about 5 seconds after the into row operation.
6. Further Reading
We have been exposed to the AQ capability in this article. AQ is a huge application that is beyond the scope of this article. For more information, please refer to:
http://docs.oracle.com/cd/B10501_01/appdev.920/a96587/toc.htm
Asktom:
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P 11_question_id:8760267539329
All source code download: Source Download
-------------------------
Dylan presents.
Oracle Advanced Queuing Introduction
