Oracle Advanced Queueing (AQ), AQ provides a message queuing infrastructure as integral part of the Oracle server engine. It provides an API for enqueing messages to database queues. These messages can later be dequeued for asynchronous processing. Oracle AQ also provides functionality to preserve, track, document, correlate, and query messages in queues.
How to Implement
AQ implementation of workflows: A->B->C->D
AQ implementation of Publish/Subscribe:
publish/subscribe with rule:
From a PL/SQL standpoint, Oracle AQ is made available through two packages: DBMS_AQADM and DBMS_AQ. The DBMS_AQADM package is the interface to the administrative tasks of Oracle AQ.
DBMS_AQADM tasks include:
Creating or dropping queue tables, which contain one or more queues
Creating, dropping, and altering queues, which are stored in a queue table
Starting and stopping queues in accepting message creation or consumption Most users of the Oracle AQ facility will not work with DBMS_AQADM. The DBA will most likely initialize all needed queue tables and queues. PL/SQL developers will instead work with the DBMS_AQ.
DBMS_AQ tasks include:
Creating a message to the specified queue
Consuming a message from the specified queue
Prepare database user
1 2 3 4 5
CONN / AS SYSDBA CREATEUSER testq IDENTIFIEDBY x; GRANTconnect, resource, dba TO testq; GRANT aq_administrator_role, aq_user_role TO testq; GRANTcreatetypeTO testq;
Starting with Oracle Database 10g, the Oracle scheduler was greatly improved with the dbms_scheduler package. Replacing the dbms_job with dbms_scheduler offers additional features by adding the ability to tie jobs with specific user-type privileges and roles: