Oracle Advanced queue introduction, oracle queue

Oracle Advanced queue introduction, oracle queue
Original link: http://www.oracle-developer.net/display.php? Id = 411


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.
BEGIN
DBMS_AQADM.CREATE_QUEUE_TABLE (
Queue_table => 'demo _ queue_table ',
Queue_payload_type => 'demo _ queue_payload_type'
);
END;
/


Create a queue and start it:


BEGIN


DBMS_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_type
FROM user_objects
WHERE object_name! = 'Demo _ QUEUE_PAYLOAD_TYPE ';
 
OBJECT_NAME OBJECT_TYPE
---------------------------------------------
DEMO_QUEUE_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 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 ).


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 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_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 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 ).


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


SELECT user_data
FROM 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 ).


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.


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.


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 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.


BEGIN
DBMS_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.
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 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
)


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 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 ).


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 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.


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


To check whether columns are automatically generated during the hour, 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 out-of-column notification occurs about five seconds after the column operation.


6. Read more

We have already touched on AQ capabilities in this article. AQ is an application with a huge coverage. It is beyond the scope of this article. For more information, see:

Http://docs.oracle.com/cd/B10501_01/appdev.920/a96587/toc.htm


ASKTOM: Https://asktom.oracle.com/pls/asktom/f? P = 100: 11: 0: P11_QUESTION_ID: 8760267539329

Download all source code: Download source code

-------------------------
Dylan Presents.