3.2 DBMS_AQ

Table of Contents Previous Next


3 Built-In Packages : 3.2 DBMS_AQ

Advanced Server also provides extended (non-compatible) functionality for the DBMS_AQ package with SQL commands. Please see the Database Compatibility for Oracle Developers Reference Guide for detailed information about the following SQL commands:
ALTER QUEUE
ALTER QUEUE TABLE
CREATE QUEUE
CREATE QUEUE TABLE
DROP QUEUE
DROP QUEUE TABLE
The DBMS_AQ package provides procedures that allow you to enqueue a message, dequeue a message, and manage callback procedures. The supported procedures are:
Advanced Server's implementation of DBMS_AQ is a partial implementation when compared to Oracle's version. Only those procedures listed in the table above are supported.
3.2.1 ENQUEUE
The ENQUEUE procedure adds an entry to a queue. The signature is:
ENQUEUE(
queue_name IN VARCHAR2,
enqueue_options IN DBMS_AQ.ENQUEUE_OPTIONS_T,
message_properties IN DBMS_AQ.MESSAGE_PROPERTIES_T,
payload IN <type_name>,
msgid OUT RAW)
The name (optionally schema-qualified) of an existing queue. If you omit the schema name, the server will use the schema specified in the SEARCH_PATH. Please note that unlike Oracle, unquoted identifiers are converted to lower case before storing. To include special characters or use a case-sensitive name, enclose the name in double quotes.
enqueue_options
enqueue_options is a value of the type, enqueue_options_t:
message_properties is a value of the type, message_properties_t:
The supported values for message_properties_t are:
If the queue table definition includes a sort_list that references priority, this parameter affects the order that messages are dequeued. A lower value indicates a higher dequeue priority.
Use the exception_queue parameter to specify the name of an exception queue to which a message will be moved if it expires or is dequeued by a transaction that rolls back too many times.
enqueue_time is the time the record was added to the queue; this value is provided by the system.
This parameter is maintained by DBMS_AQ; state can be:
DBMS_AQ.WAITING – the delay has not been reached.
DBMS_AQ.READY – the queue entry is ready for processing.
DBMS_AQ.PROCESSED – the queue entry has been processed.
DBMS_AQ.EXPIRED – the queue entry has been moved to the exception queue.
Use the payload parameter to provide the data that will be associated with the queue entry. The payload type must match the type specified when creating the corresponding queue table (see DBMS_AQADM.CREATE_QUEUE_TABLE).
Use the msgid parameter to retrieve a unique (system-generated) message identifier.
The following anonymous block calls DBMS_AQ.ENQUEUE, adding a message to a queue named work_order:
3.2.2 DEQUEUE
The DEQUEUE procedure dequeues a message. The signature is:
DEQUEUE(
queue_name IN VARCHAR2,
dequeue_options IN DBMS_AQ.DEQUEUE_OPTIONS_T,
message_properties OUT DBMS_AQ.MESSAGE_PROPERTIES_T,
payload OUT type_name,
msgid OUT RAW)
The name (optionally schema-qualified) of an existing queue. If you omit the schema name, the server will use the schema specified in the SEARCH_PATH. Please note that unlike Oracle, unquoted identifiers are converted to lower case before storing. To include special characters or use a case-sensitive name, enclose the name in double quotes.
dequeue_options
dequeue _options is a value of the type, dequeue_options_t:
The locking behavior of the dequeue operation. Must be either:
DBMS_AQ.BROWSE – Read the message without obtaining a lock.
DBMS_AQ.LOCKED – Read the message after acquiring a lock.
DBMS_AQ.REMOVE – Read the message before deleting the message.
DBMS_AQ.REMOVE_NODATA – Read the message, but do not delete the message.
Identifies the message that will be retrieved. Must be either:
FIRST_MESSAGE – The first message within the queue that matches the search term.
NEXT_MESSAGE – The next message that is available that matches the first term.
Must be ON_COMMIT – if you roll back the current transaction the dequeued item will remain in the queue.
DBMS_AQ.FOREVER – Wait indefinitely.
DBMS_AQ.NO_WAIT – Do not wait.
A VARCHAR2 expression that evaluates to a BOOLEAN value, indicating if the message should be dequeued.
Must be PERSISTENT; buffered messages are not supported at this time.
message_properties is a value of the type, message_properties_t:
The supported values for message_properties_t are:
If the queue table definition includes a sort_list that references priority, this parameter affects the order that messages are dequeued. A lower value indicates a higher dequeue priority.
Use the exception_queue parameter to specify the name of an exception queue to which a message will be moved if it expires or is dequeued by a transaction that rolls back too many times.
enqueue_time is the time the record was added to the queue; this value is provided by the system.
This parameter is maintained by DBMS_AQ; state can be:
DBMS_AQ.WAITING – the delay has not been reached.
DBMS_AQ.READY – the queue entry is ready for processing.
DBMS_AQ.PROCESSED – the queue entry has been processed.
DBMS_AQ.EXPIRED – the queue entry has been moved to the exception queue.
Use the payload parameter to retrieve the payload of a message with a dequeue operation. The payload type must match the type specified when creating the queue table.
Use the msgid parameter to retrieve a unique message identifier.
The following anonymous block calls DBMS_AQ.DEQUEUE, retrieving a message from the queue and a payload:
The payload is displayed by DBMS_OUTPUT.PUT_LINE.
3.2.3 REGISTER
Use the REGISTER procedure to register an email address, procedure or URL that will be notified when an item is enqueued or dequeued. The signature is:
REGISTER(
reg_list IN SYS.AQ$_REG_INFO_LIST,
count IN NUMBER)
reg_list is a list of type AQ$_REG_INFO_LIST; that provides information about each subscription that you would like to register. Each entry within the list is of the type AQ$_REG_INFO, and may contain:
The only supported value is DBMS_AQ.NAMESPACE_AQ (0)
Describes the action that will be performed upon notification. Currently, only calls to PL/SQL procedures are supported. The call should take the form:
plsql://schema.procedure
Where:
schema specifies the schema in which the procedure resides.
procedure specifies the name of the procedure that will be notified.
count is the number of entries in reg_list.
The following anonymous block calls DBMS_AQ.REGISTER, registering procedures that will be notified when an item is added to or removed from a queue. A set of attributes (of sys.aq$_reg_info type) is provided for each subscription identified in the DECLARE section:
The subscriptionlist is of type sys.aq$_reg_info_list, and contains the previously described sys.aq$_reg_info objects. The list name and an object count are passed to dbms_aq.register.
Use the UNREGISTER procedure to turn off notifications related to enqueueing and dequeueing. The signature is:
UNREGISTER(
reg_list IN SYS.AQ$_REG_INFO_LIST,
count IN NUMBER)
reg_list is a list of type AQ$_REG_INFO_LIST; that provides information about each subscription that you would like to register. Each entry within the list is of the type AQ$_REG_INFO, and may contain:
The only supported value is DBMS_AQ.NAMESPACE_AQ (0)
Describes the action that will be performed upon notification. Currently, only calls to PL/SQL procedures are supported. The call should take the form:
plsql://schema.procedure
Where:
schema specifies the schema in which the procedure resides.
procedure specifies the name of the procedure that will be notified.
count is the number of entries in reg_list.
The following anonymous block calls DBMS_AQ.UNREGISTER, disabling the notifications specified in the example for DBMS_AQ.REGISTER:
The subscriptionlist is of type sys.aq$_reg_info_list, and contains the previously described sys.aq$_reg_info objects. The list name and an object count are passed to dbms_aq.unregister.

3 Built-In Packages : 3.2 DBMS_AQ

Table of Contents Previous Next