Oracle handbook Series 12: Advanced Queuing)

Oracle Streams Advanced Queuing (AQ) is a robust and feature-rich message queuing system integrated with Oracle Database. when an organization has different systems that must communicate with each other, a messaging environment can provide a standard, reliable way to transport critical information between these systems. AQ is implemented in database tables.

Oracle AQ is a message queue mechanism integrated in Oracle databases. It can be used for message interaction between different applications, for example, PL/SQL can access the queue through the corresponding Package, and C # Should program can access the queue through ODP. NET Access queue, Java applications can access the queue through OJMS. AQ is implemented through database tables (that is, messages are actually stored in database tables ).

This document describes the basic usage of AQ in PL/SQL. The example assumes that A is A frequently called stored procedure. Before each call to A, the calling process B and B consume A lot of time, assuming that the execution of A does not depend on the execution result of B, we can save the context of calling B to AQ and then process it asynchronously, this reduces the impact of B on application performance.

1.CreateAQRequired Permissions

GRANT EXECUTE ON DBMS_AQ TO user1;
GRANT EXECUTE ON DBMS_AQADM TO user1;
BEGIN
  DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE('ENQUEUE_ANY', 'user1', FALSE);
  DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE('DEQUEUE_ANY', 'user1', FALSE);
END;

You need to use sys or system users to initiate these authorization statements. To create and manage AQ, You need to obtain the execution permissions of the two crucial packages dbms_aq and dbms_aqadm. The last two authorizations are optional through grant_system_privilege, which indicate:

ENQUEUE_ANY means users granted this privilege are allowed to enqueue messages to any queues in the database. DEQUEUE_ANY means users granted this privilege are allowed to dequeue messages from any queues in the database.

2.CreatePayloadType

CREATE OR REPLACE TYPE t_spl_queue_payload AS OBJECT
(
  ID             CHAR(36),
  EXEC_DATE     TIMESTAMP(6),
  PARAMETER1       NUMBER,
  PARAMETER2       VARCHAR2(500),
  FLAG           CHAR(1)
);

We usually define an object to store information that will be placed in the AQ queue in the future.

3.CreateAQRelated table

BEGIN
  DBMS_AQADM.CREATE_QUEUE_TABLE(queue_table        => 'user1. Spl_queue_table',
                                multiple_consumers => TRUE,
                                queue_payload_type => 'user1.t_spl_queue_payload');
END;

The execution result is that the Spl_queue_table table and several aq $ _ spl_queue_table _ tables are generated. In the Spl_queue_table table, in addition to some fields required by the AQ queue itself, there is a USER_DATA field of the type t_spl_queue_payload, which is used to store queue messages. This also confirms the above: AQ is implemented through database tables.

4.Create and startAQ

BEGIN
  DBMS_AQADM.CREATE_QUEUE(queue_name  => 'user1.spl_aq',
                          queue_table => 'user1.spl_queue_table');
END;
--
BEGIN
  DBMS_AQADM.START_QUEUE(queue_name => 'user1.spl_aq');
END;

How to stop and delete AQ:

BEGIN
  DBMS_AQADM.STOP_QUEUE (queue_name => 'user1.spl_aq'); 
  DBMS_AQADM.DROP_QUEUE (queue_name => 'user1.spl_aq'); 
  DBMS_AQADM.DROP_QUEUE_TABLE (queue_table => 'user1.spl_queue_table');
END;

5.Message Queue

PROCEDURE enqueue(p_payload IN t_spl_queue_payload) IS
--PRAGMA AUTONOMOUS_TRANSACTION;
  enqueue_options    dbms_aq.enqueue_options_t;
  message_properties dbms_aq.message_properties_t;
  message_handle     RAW(16);
  recipients         DBMS_AQ.aq$_recipient_list_t;
BEGIN
  recipients(1) := sys.aq$_agent('someguy', 'user1.SPL_AQ', NULL);
  message_properties.recipient_list := recipients;
  message_properties.priority := -5;
  message_properties.delay := dbms_aq.no_delay;
  message_properties.expiration := dbms_aq.never;
--enqueue_options.visibility := dbms_aq.on_commit;
  enqueue_options.visibility := dbms_aq.immediate;
  enqueue_options.sequence_deviation := null;

  dbms_aq.enqueue(queue_name         => 'user1.SPL_AQ',
                  enqueue_options    => enqueue_options,
                  message_properties => message_properties,
                  payload            => p_payload,
                  msgid              => message_handle);

--COMMIT;
END enqueue;

(1) recipient, where "someguy" specifies the message receiver. You need to specify the same name to receive the message when leaving the queue. (2) visibility, which can be on_commit or immediate. If on_commit is used, the message enters the queue after the commit statement is manually called (in this case, it is best to use autonomous transactions). If immediate is used, when dbms_aq.enqueue is complete, the message enters the queue without commit. By default, the autonomous transaction is used.

6.Message Queue

PROCEDURE dequeue IS
  l_payload      t_spl_queue_payload;
  l_queue_record NUMBER;
  dequeue_options    dbms_aq.dequeue_options_t;
  message_properties dbms_aq.message_properties_t;
  message_handle     RAW(16);
BEGIN
  dequeue_options.consumer_name := 'someguy';
  dequeue_options.dequeue_mode  := dbms_aq.remove;
  dequeue_options.navigation    := dbms_aq.next_message;
  dequeue_options.visibility    := dbms_aq.immediate;
--dequeue_options.wait          := dbms_aq.forever;
  dequeue_options.wait          := dbms_aq.no_wait;
  dequeue_options.msgid         := null;
--
  SELECT COUNT(*)
INTO l_queue_record
FROM AQ$SPL_QUEUE_TABLE
WHERE msg_state = 'READY';
--
  FOR i IN 1 .. l_queue_record LOOP
    dbms_aq.dequeue(queue_name         => 'user1.SPL_AQ',
                    dequeue_options    => dequeue_options,
                    message_properties => message_properties,
                    payload            => l_payload,
                    msgid              => message_handle);
--
  /*……………………………………….
  some time consuming calculation
  ………………………………………….*/
END LOOP;
END;

(1) consumer_name must be consistent with the previously specified recipient when you enter the queue. (2) The two wait values forever and no_wait indicate whether to wait if there is no message in the current queue. (3) The two values of navigation: first_message and next_message. Generally, we use the latter for performance reasons, or use the former in the first release, and then use the latter in the subsequent release:

The FIRST_MESSAGE navigation option performs a SELECT on the queue. the NEXT _ MESSAGE navigation option fetches from the results of the SELECT run in the FIRST _ MESSAGE navigation. thus performance is optimized because subsequent dequeues need not run the entire SELECT again.