Oracle Advanced queue Introduction

Oracle Advanced queue Introduction
Oracle Advanced queue Introduction


Advanced Queue (AQ) is available in multiple oracle versions. He is an oracle native messaging software and is being enhanced in every version.
This article provides an advanced overview of AQ. In particular, we will see how to start a queue and perform columns-and columns-out operations, and create asynchronous columns through notifications.


Note that AQ supports unexpected message listening (for example, JMS Message Queue) in the database ). This article only involves message communication within the database.


Requirements:


In this example, the specified roles and permissions are required (except for the standard create session/TABLE/PROCEDURE/TYPE and tablespace quota)


1. AQ_ADMINISTRATOR_ROLE: used to create a queue list and queues;
2. execute on DBMS_AQ: used to notify the case to enable PLSQL Stored Procedure Compilation


In addition, the standard application users who need to enter or output messages require AQ permissions to be provided through DBMS_AQADM [GRANT | REVOKE] _ QUEUE_PRIVILIEGE API.


The following example can be run under any user with the above permissions.


1. Create and start a queue


The message processed by AQ is called payloads ). The message format can be a user-defined object, XMLType, or ANYDATA. When we create a queue, we need to tell oracle
Effective load 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 may be more complex in reality. The following creates a queue list to store queue messages until they are permanently listed.

BEGINDBMS_AQADM.CREATE_QUEUE_TABLE (queue_table => 'demo_queue_table',queue_payload_type => 'demo_queue_payload_type');END;/

Create a queue and start it:

BEGINDBMS_AQADM.CREATE_QUEUE (queue_name => 'demo_queue',queue_table => 'demo_queue_table');DBMS_AQADM.START_QUEUE (queue_name => 'demo_queue');END;/

So far, we have created a queue payload, a queue list, and a queue. Let's take a look at the related objects:

SELECT object_name, object_typeFROM user_objectsWHERE object_name != 'DEMO_QUEUE_PAYLOAD_TYPE';OBJECT_NAME OBJECT_TYPE------------------------------ ---------------DEMO_QUEUE_TABLE TABLESYS_C009392 INDEXSYS_LOB0000060502C00030$$ LOBAQ$_DEMO_QUEUE_TABLE_T INDEXAQ$_DEMO_QUEUE_TABLE_I INDEXAQ$_DEMO_QUEUE_TABLE_E QUEUEAQ$DEMO_QUEUE_TABLE VIEWDEMO_QUEUE QUEUE

We can see that a queue brings out a series of automatically generated objects, some of which are directly used later. However, it is interesting that the second queue is created. This is the so-called exception Queue (exception
Queue ). If AQ cannot receive messages from our queue, it will be recorded in this exception queue.


2. enqueuing messages)
We are ready to use DBMS_AQ.ENQUEUE API to input a message. In the following example, we use the ENQUEUE process to column a single message.
DBMS_AQ has a wide range of records and array types to support its interface and let us modify its behavior (we will see two such references in the following example ).

DECLAREr_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;BEGINo_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 is easy to see the column message. The column operation is a basic transaction (like Insert to the queue list), so we need to submit it.


3. browsed messages)
Before we publish a message, we will "Browse" the queue content. First, we can query the number of messages that have been listed in the AQ $ DEMO_QUEUE_TABLE view. As we saw earlier,
This view is automatically generated when a queue is created in DBMS_AQADM.CREATE_QUEUE.

SELECT COUNT(*)FROM aq$demo_queue_table;COUNT(*)----------1

As we expected, there is only one message in the queue. There are two ways to view the message content:


1) directly query the View:

SELECT user_dataFROM aq$demo_queue_table;USER_DATA(MESSAGE)------------------------------------------------------------DEMO_QUEUE_PAYLOAD_TYPE('Here is a message')

2) We can use DBMS_AQ.DEQUEUE API to browse. According to the name, we can see that this process is used to output messages. We can use
DBMS_AQ.BROWSE: Modify the column attributes (DBMS_AQ.REMOVE by default ).

DECLAREr_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;BEGINr_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 confirm that the message is not removed:

SELECT user_dataFROM aq$demo_queue_table;USER_DATA(MESSAGE)------------------------------------------------------------DEMO_QUEUE_PAYLOAD_TYPE('Here is a message')

4. dequeuing messages)


Now we will actually output the message. This operation is not required in the same session (remember to join the AQ table-based commit transaction ). Like columns, columns are also a transaction (from the queue list
Remove messages ).

DECLAREr_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;BEGINDBMS_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.

Query the view again to find that the message is indeed out of the column:

SELECT COUNT(*)FROM aq$demo_queue_table;COUNT(*)----------0

5. notification)


For the rest of the article, we will take a look at the automatic listing through notifications. In this way, Oracle notifies a proxy to execute a registered PLSQL Statement no matter when the message is entered.
"Callback" (optional, the proxy can also notify an email address or HTTP: // address ).


To illustrate, we will create and register a PLSQL process to manage our columns through notification. This callback process writes a column of messages to a database table to simulate
Standard Database Operations.


At the beginning, we clean up the previously created objects.

BEGINDBMS_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 recreate the team list to allow multiple consumers (consumers ). A consumer is an out-of-the-box message proxy (agent). automatic notification is enabled for multiple consumers.
Prerequisites.

BEGINDBMS_AQADM.CREATE_QUEUE_TABLE (queue_table => 'demo_queue_table',queue_payload_type => 'demo_queue_payload_type',multiple_consumers => TRUE);END;/

Then create and start our queue.

BEGINDBMS_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 feature of notifications, We will store the listed messages in an application table.
Create table demo_queue_message_table
(Message VARCHAR2 (4000 ));




Now we have an application table. We can create a callback PL/SQL. This process triggers the notification columns. Program parameters must be named and typed. The columns are
The timestamp is included in the column, so that we can see the asynchronous latency of the message column and the notification column in the application table.

CREATE PROCEDURE demo_queue_callback_procedure(context RAW,reginfo SYS.AQ$_REG_INFO,descr SYS.AQ$_DESCRIPTOR,payload RAW,payloadl NUMBER) ASr_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;BEGINr_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 process. We need to add a subscriber (subsriber) to the queue and register the actions when the subscriber receives the notification (for example, we will execute our callback process ).

BEGINDBMS_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 by adding a column message. The message contains only one timestamp so that we can compare the time difference between the input and automatic output columns.

DECLAREr_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;BEGINo_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;/

To check whether columns are automatically generated during the hour, we will check the application table (DEMO_QUEUE_MESSAGE_TABLE ).

SELECT messageFROM 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 out-of-column notification occurs about five seconds after the column operation.