The DBMS_AQ
package provides an interface to Oracle Streams Advanced Queuing (AQ).
See Also:
Oracle Streams AQ TYPEs for information about TYPE
s to use with DBMS_AQ.
This chapter contains the following topics:
Constants
Data Structures
Operational Notes
When using enumerated constants such as BROWSE
, LOCKED
, or REMOVE
, the PL/SQL constants must be specified with the scope of the packages defining it. All types associated with the operational interfaces have to be prepended with DBMS_AQ
. For example: DBMS_AQ.BROWSE
. Table 16-1 lists the PL/SQL enumerated constants that require the prefix, DBMS_AQ.
Table 16-1 Enumerated Constants
Parameter | Options |
---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Table 16-2 DBMS_AQ Data Structures
Data Structures | Description |
---|---|
Names database objects |
|
Defines queue types |
|
Specifies the user-defined PL/SQL procedure, defined in the database to be invoked on message notification |
The object_name
data structure names database objects. It applies to queues, queue tables, agent names, and object types.
object_name := VARCHAR2; object_name := [schema_name.]name;
Names for objects are specified by an optional schema name and a name. If the schema name is not specified, the current schema is assumed. The name must follow object name guidelines in Oracle Database SQL Reference with regard to reserved characters. Schema names, agent names, and object type names can be up to 30 bytes long. Queue names and queue table names can be up to 24 bytes long.
The type_name
data structure defines queue types.
type_name := VARCHAR2;
type_name := object_type | "RAW";
Table 16-3 Type Name Attributes
Attribute | Description |
---|---|
|
Maximum number of attributes in the object type is limited to 900. |
|
To store payload of type Because |
The plsqlcallback
data structure specifies the user-defined PL/SQL procedure, defined in the database to be invoked on message notification.
If a notification message is expected for a RAW
payload enqueue, then the PL/SQL callback must have the following signature:
procedure plsqlcallback( context IN RAW, reginfo IN SYS.AQ$_REG_INFO, descr IN SYS.AQ$_DESCRIPTOR, payload IN RAW, payloadl IN NUMBER);
Table 16-4 Oracle Streams AQ PL/SQL Callback Attributes
Attribute | Description |
---|---|
|
Specifies the context for the callback function that was passed by |
|
See AQ$_REG_INFO Type. |
|
|
|
If a notification message is expected for a raw payload enqueue then this contains the raw payload that was enqueued into a non persistent queue. In case of a persistent queue with raw payload this parameter will be null. |
|
Specifies the length of |
If the notification message is expected for an ADT payload enqueue, the PL/SQL callback must have the following signature:
procedure plsqlcallback( context IN RAW, reginfo IN SYS.AQ$_REG_INFO, descr IN SYS.AQ$_DESCRIPTOR, payload IN VARCHAR2, payloadl IN NUMBER);
Java interfaces are available for DBMS_AQ
and DBMS_AQADM
. The Java interfaces are provided in the $ORACLE_HOME/rdbms/jlib/aqapi
.jar
. Users are required to have EXECUTE
privileges on the DBMS_AQIN
package to use these interfaces.
Table 16-5 DBMS_AQ Package Subprograms
Subprograms | Description |
---|---|
Creates an entry for an Oracle Streams AQ agent in the LDAP directory |
|
Dequeues a message from the specified queue |
|
Dequeues an array of messages from the specified queue |
|
Adds a message to the specified queue |
|
Adds an array of messages to the specified queue |
|
Listen to one or more queues on behalf of a list of agents |
|
Posts to a anonymous subscription which allows all clients who are registered for the subscription to get notifications |
|
Registers for message notifications |
|
Removes an entry for an Oracle Streams AQ agent from the LDAP directory |
|
Unregisters a subscription which turns off notification |
Note:
DBMS_AQ
does not have a purity level defined; therefore, you cannot call any procedure in this package from other procedures that have RNDS
, WNDS
, RNPS
or WNPS
constraints defined.This procedure creates an entry for an Oracle Streams AQ agent in the LDAP server.
DBMS_AQ.BIND_AGENT( agent IN SYS.AQ$_AGENT, certificate IN VARCHAR2 default NULL);
Table 16-6 BIND_AGENT Procedure Parameters
Parameter | Description |
---|---|
|
Agent that is to be registered in LDAP server. |
|
Location (LDAP distinguished name) of the "organizationalperson" entry in LDAP whose digital certificate (attribute |
In the LDAP server, digital certificates are stored as an attribute (usercertificate
) of the OrganizationalPerson
entity. The distinguished name for this OrganizationalPerson
must be specified when binding the agent.
This procedure dequeues a message from the specified queue.
DBMS_AQ.DEQUEUE ( queue_name IN VARCHAR2, dequeue_options IN dequeue_options_t, message_properties OUT message_properties_t, payload OUT "<ADT_1>" msgid OUT RAW);
Table 16-7 DEQUEUE Procedure Parameters
Parameter | Description |
---|---|
|
Specifies the name of the queue. |
|
|
|
|
|
Not interpreted by Oracle Streams AQ. The payload must be specified according to the specification in the associated queue table. For the definition of |
|
System generated identification of the message. |
The search criteria for messages to be dequeued is determined by the following parameters in dequeue_options
:
consumer_name
msgid
Msgid
uniquely identifies the message to be dequeued. Only messages in the READY
state are dequeued unless msgid
is specified.
correlation
Correlation identifiers are application-defined identifiers that are not interpreted by Oracle Streams AQ.
deq_condition
Dequeue condition is an expression based on the message properties, the message data properties and PL/SQL functions. A deq_condition
is specified as a Boolean expression using syntax similar to the WHERE
clause of a SQL query. This Boolean expression can include conditions on message properties, user data properties (object payloads only), and PL/SQL or SQL functions (as specified in the where clause of a SQL query). Message properties include priority,
corrid
and other columns in the queue table.
To specify dequeue conditions on a message payload (object payload), use attributes of the object type in clauses. You must prefix each attribute with tab.user_data
as a qualifier to indicate the specific column of the queue table that stores the payload.
Example: tab.user_data.orderstatus='EXPRESS'
The dequeue order is determined by the values specified at the time the queue table is created unless overridden by the msgid
and correlation ID in dequeue_options
.
The database-consistent read mechanism is applicable for queue operations. For example, a BROWSE
call may not see a message that is enqueued after the beginning of the browsing transaction.
The default NAVIGATION
parameter during dequeue is NEXT_MESSAGE
. This means that subsequent dequeues will retrieve the messages from the queue based on the snapshot obtained in the first dequeue. In particular, a message that is enqueued after the first dequeue command will be processed only after processing all the remaining messages in the queue. This is usually sufficient when all the messages have already been enqueued into the queue, or when the queue does not have a priority-based ordering. However, applications must use the FIRST_MESSAGE
n
avigation option when the first message in the queue needs to be processed by every dequeue command. This usually becomes necessary when a higher priority message arrives in the queue while messages already-enqueued are being processed.
Note:
It may be more efficient to use theFIRST_MESSAGE
navigation option when messages are concurrently enqueued. If the FIRST_MESSAGE
option is not specified, Oracle Streams AQ continually generates the snapshot as of the first dequeue command, leading to poor performance. If the FIRST_MESSAGE
option is specified, then Oracle Streams AQ uses a new snapshot for every dequeue command.Messages enqueued in the same transaction into a queue that has been enabled for message grouping will form a group. If only one message is enqueued in the transaction, then this will effectively form a group of one message. There is no upper limit to the number of messages that can be grouped in a single transaction.
In queues that have not been enabled for message grouping, a dequeue in LOCKED
or REMOVE
mode locks only a single message. By contrast, a dequeue operation that seeks to dequeue a message that is part of a group will lock the entire group. This is useful when all the messages in a group need to be processed as an atomic unit.
When all the messages in a group have been dequeued, the dequeue returns an error indicating that all messages in the group have been processed. The application can then use the NEXT_TRANSACTION
to start dequeuing messages from the next available group. In the event that no groups are available, the dequeue will time out after the specified WAIT
period.
For secure queues, you must specify consumer_name
in the dequeue_options
parameter. See DEQUEUE_OPTIONS_T Type for more information about consumer_name
.
When you use secure queues, the following are required:
You must have created a valid Oracle Streams AQ agent using DBMS_AQADM.CREATE_AQ_AGENT
. See CREATE_AQ_AGENT Procedure .
You must map the Oracle Streams AQ agent to a database user with dequeue privileges on the secure queue. Use DBMS_AQADM.ENABLE_DB_ACCESS
to do this. See ENABLE_DB_ACCESS Procedure.
See Also:
Oracle Streams Concepts and Administration for information about secure queuesThis function dequeues an array of messages and returns them in the form of an array of payloads, an array of message properties and an array of message IDs. This function returns the number of messages successfully dequeued.
DBMS_AQ.DEQUEUE_ARRAY ( queue_name IN VARCHAR2, dequeue_options IN dequeue_options_t, array_size IN pls_integer, message_properties_array OUT message_properties_array_t, payload_array OUT "<COLLECTION_1>", msgid_array OUT msgid_array_t, error_array OUT error_array_t) RETURN pls_integer;
Table 16-8 DEQUEUE_ARRAY Function Parameters
Parameter | Description |
---|---|
|
The queue name from which messages are dequeued (same as single-row dequeue). |
|
The set of options which will be applied to all messages in the array (same as single-row dequeue). |
|
The number of elements to dequeue. |
|
A record containing an array corresponding to each message property. Each payload element has a corresponding set of message properties. See MESSAGE_PROPERTIES_ARRAY_T Type. |
|
An array of dequeued payload data. "<COLLECTION_1>" can be an associative array, varray or nested table in its PL/SQL representation. |
|
An array of message IDs of the dequeued messages. See MSGID_ARRAY_T Type. |
|
Currently not implemented |
A nonzero wait time, as specified in dequeue_options
, is recognized only when there are no messages in the queue. If the queue contains messages that are eligible for dequeue, then the DEQUEUE_ARRAY
function will dequeue up to array_size
messages and return immediately.
Dequeue by message_id
is not supported. See DEQUEUE Procedure for more information on the navigation
parameter. Existing NAVIGATION
modes are supported. In addition, two new NAVIGATION
modes are supported for queues enabled for message grouping:
FIRST_MESSAGE_MULTI_GROUP
NEXT_MESSAGE_MULTI_GROUP
See Also:
ENQUEUE_OPTIONS_T TypeFor transaction grouped queues and ONE_GROUP
navigation, messages are dequeued from a single transaction group only, subject to the array_size
limit. In MULTI_GROUP
navigation, messages are dequeued across multiple transaction groups, still subject to the array_size
limit. ORA-25235 is returned to indicate the end of a transaction group.
DEQUEUE_ARRAY
is not supported for buffered messages, but you can still use this procedure on individual buffered messages by setting array_size
to one message.
This procedure adds a message to the specified queue.
DBMS_AQ.ENQUEUE ( queue_name IN VARCHAR2, enqueue_options IN enqueue_options_t, message_properties IN message_properties_t, payload IN "<ADT_1>", msgid OUT RAW);
Table 16-9 ENQUEUE Procedure Parameters
Parameter | Description |
---|---|
|
Specifies the name of the queue to which this message should be enqueued. The queue cannot be an exception queue. |
|
|
|
|
|
Not interpreted by Oracle Streams AQ. The payload must be specified according to the specification in the associated queue table. |
|
System generated identification of the message. This is a globally unique identifier that can be used to identify the message at dequeue time. |
The sequence_deviation
parameter in enqueue_options
can be used to change the order of processing between two messages. The identity of the other message, if any, is specified by the enqueue_options
parameter relative_msgid
. The relationship is identified by the sequence_deviation
parameter.
Specifying sequence_deviation
for a message introduces some restrictions for the delay and priority values that can be specified for this message. The delay of this message must be less than or equal to the delay of the message before which this message is to be enqueued. The priority of this message must be greater than or equal to the priority of the message before which this message is to be enqueued.
Note:
Thesequence_deviation
attribute has no effect in releases prior to Oracle Streams AQ 10g Release 1 (10.1) if message_grouping
is set to TRANSACTIONAL
. The sequence deviation feature is deprecated in Oracle Streams AQ 10g Release 2 (10.2).If a message is enqueued to a multiconsumer queue with no recipient, and if the queue has no subscribers (or rule-based subscribers that match this message), then Oracle error ORA
_24033
is raised. This is a warning that the message will be discarded because there are no recipients or subscribers to whom it can be delivered.
For secure queues, you must specify the sender_id
in the messages_properties
parameter. See MESSAGE_PROPERTIES_T Type for more information about sender_id
.
When you use secure queues, the following are required:
You must have created a valid Oracle Streams AQ agent using DBMS_AQADM.CREATE_AQ_AGENT
. See CREATE_AQ_AGENT Procedure .
You must map sender_id
to a database user with enqueue privileges on the secure queue. Use DBMS_AQADM.ENABLE_DB_ACCESS
to do this. See ENABLE_DB_ACCESS Procedure.
See Also:
Oracle Streams Concepts and Administration for information about secure queuesThis function enqueues an array of payloads using a corresponding array of message properties. The output will be an array of message IDs of the enqueued messages.
DBMS_AQ.ENQUEUE_ARRAY ( queue_name IN VARCHAR2, enqueue_options IN enqueue_options_t, array_size IN pls_integer, message_properties_array IN message_properties_array_t, payload_array IN "<COLLECTION_1>", msgid_array OUT msgid_array_t, error_array OUT error_array_t) RETURN pls_integer;
Table 16-10 ENQUEUE_ARRAY Function Parameters
Parameter | Description |
---|---|
|
The queue name in which messages are enqueued (same as single-row enqueue). |
|
|
|
The number of elements to enqueue. |
|
A record containing an array corresponding to each message property. For each property, the user must allocate |
|
An array of payload data. "< |
|
An array of message IDs for the enqueued messages. If an error occurs for a particular message, then its corresponding message ID is null. See MSGID_ARRAY_T Type. |
|
Currently not implemented |
ENQUEUE_ARRAY
is not supported for buffered messages, but you can still use this procedure on individual buffered messages by setting array_size
to one message.
This procedure listens on one or more queues on behalf of a list of agents. The address field of the agent indicates the queue the agent wants to monitor. Only local queues are supported as addresses. Protocol is reserved for future use.
DBMS_AQ.LISTEN ( agent_list IN AQ$_AGENT_LIST_T, wait IN BINARY_INTEGER DEFAULT DBMS_AQ.FOREVER, agent OUT SYS.AQ$_AGENT);
DBMS_AQ.LISTEN ( agent_list IN AQ$_AGENT_LIST_T, wait IN BINARY_INTEGER DEFAULT FOREVER, listen_delivery_mode IN PLS_INTEGER DEFAULT DBMS_AQ.PERSISTENT, agent OUT SYS.AQ$_AGENT, message_delivery_mode OUT PLS_INTEGER);
TYPE aq$_agent_list_t IS TABLE of aq$_agent INDEXED BY BINARY_INTEGER; TYPE aq$_agent_list_t IS TABLE of aq$_agent INDEXED BY BINARY_INTEGER;
Table 16-11 LISTEN Procedure Parameters
Parameter | Description |
---|---|
|
List of agents to listen for |
|
Time out for the listen call in seconds. By default, the call will block forever. |
|
The caller specifies whether it is interested in persistent, buffered messages or both types of messages, specifying a delivery mode of |
|
Agent with a message available for consumption |
|
Returns the message type along with the queue and consumer for which there is a message |
If agent-address is a multiconsumer queue, then agent-name is mandatory. For single-consumer queues, agent-name must not be specified.
This procedure takes a list of agents as an argument. You specify the queue to be monitored in the address field of each agent listed. You also must specify the name of the agent when monitoring multiconsumer queues. For single-consumer queues, an agent name must not be specified. Only local queues are supported as addresses. Protocol is reserved for future use.
This is a blocking call that returns when there is a message ready for consumption for an agent in the list. If there are messages for more than one agent, only the first agent listed is returned. If there are no messages found when the wait time expires, an error is raised.
A successful return from the LISTEN
call is only an indication that there is a message for one of the listed agents in one the specified queues. The interested agent must still dequeue the relevant message.
Note:
You cannot callLISTEN
on nonpersistent queues.This procedure posts to a list of anonymous subscriptions that allows all clients who are registered for the subscriptions to get notifications.
DBMS_AQ.POST ( post_list IN SYS.AQ$_POST_INFO_LIST, post_count IN NUMBER);
Table 16-12 POST Procedure Parameters
Parameter | Description |
---|---|
|
Specifies the list of anonymous subscriptions to which you want to post. It is a list of AQ$_POST_INFO_LIST Type. |
|
Specifies the number of entries in the |
This procedure is used to post to anonymous subscriptions which allows all clients who are registered for the subscriptions to get notifications. Several subscriptions can be posted to at one time.
This procedure registers an e-mail address, user-defined PL/SQL procedure, or HTTP URL for message notification.
DBMS_AQ.REGISTER ( reg_list IN SYS.AQ$_REG_INFO_LIST, count IN NUMBER);
Table 16-13 REGISTER Procedure Parameters
Parameter | Description |
---|---|
|
Specifies the list of subscriptions to which you want to register for message notifications. It is a list of AQ$_REG_INFO Type. |
|
Specifies the number of entries in the |
This procedure is used to register for notifications. You can specify an e-mail address to which message notifications are sent, register a procedure to be invoked on a notification, or register an HTTP URL to which the notification is posted. Interest in several subscriptions can be registered at one time.
If you register for e-mail notifications, you should set the host name and port name for the SMTP server that will be used by the database to send e-mail notifications. If required, you should set the send-from e-mail address, which is set by the database as the sent
from
field. You need a Java-enabled database to use this feature.
If you register for HTTP notifications, you may want to set the host name and port number for the proxy server and a list of no-proxy domains that will be used by the database to post HTTP notifications.
See Also:
Chapter 18, "DBMS_AQELM" for more information on e-mail and HTTP notificationsThis procedure removes the entry for an Oracle Streams AQ agent from the LDAP server.
DBMS_AQ.UNBIND_AGENT( agent IN SYS.AQ$_AGENT);
Table 16-14 BIND_AGENT Procedure Parameters
Parameter | Description |
---|---|
|
Agent that is to be removed from the LDAP server |
This procedure unregisters a subscription which turns off notifications.
DBMS_AQ.UNREGISTER ( reg_list IN SYS.AQ$_REG_INFO_LIST, reg_count IN NUMBER);
Table 16-15 UNREGISTER Procedure Parameters
Parameter | Description |
---|---|
|
Specifies the list of subscriptions to which you want to register for message notifications. It is a list of AQ$_REG_INFO Type. |
|
Specifies the number of entries in the |
This procedure is used to unregister a subscription which turns off notifications. Several subscriptions can be unregistered from at one time.