Oracle8i Supplied Packages Reference
Release 8.1.5
A68001-01

Library
Product
Contents
Index

Prev Next

4
DBMS_AQ

The DBMS_AQ package provides an interface to Oracle's Advanced Queuing.

See Also:

Oracle8i Application Developer's Guide - Advanced Queuing contains detailed information about DBMS_AQ.  

Java Classes

Java interfaces are available for DBMS_AQ and DBMS_AQADM. The java interfaces are provided in the $ORACLE_HOME/rdbms/jlib/aqapi.jar. In this release, these Java API are available only for queues with RAW type payloads. Users are required to have EXECUTE privileges on the DBMS_AQIN package to use these interfaces.

Enumerated Constants

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 4-1 Enumerated Constants
Parameter  Options  
visibility
 		 
IMMEDIATE, ON_COMMIT
 		 
dequeue mode
 		 
BROWSE, LOCKED, REMOVE, REMOVE_NODATA
 		 
navigation
 		 
FIRST_MESSAGE, NEXT_MESSAGE, NEXT_TRANSACTION
 		 
state
 		 
WAITING, READY, PROCESSED, EXPIRED
 		 
sequence_deviation
 		 
BEFORE, TOP
 		 
wait
 		 
FOREVER, NO_WAIT
 		 
delay
 		 
NO_DELAY
 		 
expiration
 		 
NEVER
 

Data Structures

The following data structures are used in both DBMS_AQ and DBMS_AQADM:

Object Name

This names database objects. This naming convention applies to queues, queue tables, agent names, and object types.

Syntax

object_name := VARCHAR2;
object_name := [<schema_name>.]<name>;
Usage

Names for objects are specified by an optional schema name and a name. If the schema name is not specified, then the current schema is assumed. The name must follow object name guidelines in the Oracle8i SQL Reference with regard to reserved characters. The schema name, agent name, and the object type name can each be up to 30 bytes long. However, queue names and queue table names have a maximum of 24 bytes.

Type Name

This defines queue types.

Syntax

type_name := VARCHAR2;
type_name := <object_type> | "RAW";
Usage
Table 4-2 Type Name
Parameter  Description  
<object_types>
 

Maximum number of attributes in the object type is limited to 900.

See Also: Oracle8i Concepts  

 
"RAW"
 

To store payload of type RAW, AQ creates a queue table with a LOB column as the payload repository. The theoretical maximum size of the message payload is the maximum amount of data that can be stored in a LOB column. However, the maximum size of the payload is determined by which programmatic environment you use to access AQ. For PL/SQL, Java and precompilers the limit is 32K; for the OCI the limit is 4G. Because the PL/SQL enqueue and dequeue interfaces accept RAW buffers as the payload parameters you will be limited to 32K bytes. In OCI, the maximum size of your RAW data will be limited to the maximum amount of contiguous memory (as an OCIRaw is simply an array of bytes) that the OCI Object Cache can allocate. Typically, this will be at least 32K bytes and much larger in many cases.

Because LOB columns are used for storing RAW payload, the AQ administrator can choose the LOB tablespace and configure the LOB storage by constructing a LOB storage string in the storage_clause parameter during queue table creation time.  

Agent

This identifies a producer or a consumer of a message.

Syntax

TYPE sys.aq$_agent IS OBJECT (
   name      VARCHAR2(30),
   address   VARCHAR2(1024),
   protocol  NUMBER);
Usage
Table 4-3 Agent
Parameter  Description  
name
 

Name of a producer or consumer of a message. The name must follow object name guidelines in the Oracle8i SQL Reference with regard to reserved characters.  

 
address
 

Protocol-specific address of the recipient. If the protocol is 0 (default), then the address is of the form [schema.]queue[@dblink].  

 
protocol
 

Protocol to interpret the address and propagate the message. The default is 0.  

Enqueue Options Type

This specifies the options available for the enqueue operation.

Syntax

TYPE enqueue_options_t IS RECORD (
   visibility            BINARY_INTEGER DEFAULT ON_COMMIT,
   relative_msgid        RAW(16)        DEFAULT NULL,
   sequence_deviation    BINARY_INTEGER DEFAULT NULL);
Usage
Table 4-4 Enqueue Options Type
Parameter  Description 

visibility  

Specifies the transactional behavior of the enqueue request.

ON_COMMIT: The enqueue is part of the current transaction. The operation is complete when the transaction commits. This is the default case.

IMMEDIATE: The enqueue is not part of the current transaction. The operation constitutes a transaction on its own. This is the only value allowed when enqueuing to a non-persistent queue.  

relative_msgid  

Specifies the message identifier of the message which is referenced in the sequence deviation operation. This field is valid if, and only if, BEFORE is specified in sequence_deviation. This parameter is ignored if sequence deviation is not specified.  

sequence_deviation  

Specifies whether the message being enqueued should be dequeued before other message(s) already in the queue.

BEFORE: The message is enqueued ahead of the message specified by relative_msgid.

TOP: The message is enqueued ahead of any other messages.

NULL: Default  

Dequeue Options Type

This specifies the options available for the dequeue operation.

Syntax

TYPE dequeue_options_t IS RECORD (
   consumer_name    VARCHAR2(30)   DEFAULT NULL,
   dequeue_mode     BINARY_INTEGER DEFAULT REMOVE,
   navigation       BINARY_INTEGER DEFAULT NEXT_MESSAGE,
   visibility       BINARY_INTEGER DEFAULT ON_COMMIT,
   wait             BINARY_INTEGER DEFAULT FOREVER,
   msgid            RAW(16)        DEFAULT NULL,
   correlation      VARCHAR2(128)  DEFAULT NULL);
Usage
Table 4-5 Dequeue Options Type
Parameter  Description 

consumer_name  

Name of the consumer. Only those messages matching the consumer name are accessed. If a queue is not set up for multiple consumers, then this field should be set to NULL.  

dequeue_mode  

Specifies the locking behavior associated with the dequeue.

BROWSE: Read the message without acquiring any lock on the message. This is equivalent to a select statement.

LOCKED: Read and obtain a write lock on the message. The lock lasts for the duration of the transaction. This is equivalent to a select for update statement.

REMOVE: Read the message and update or delete it. This is the default. The message can be retained in the queue table based on the retention properties.

REMOVE_NODATA: Mark the message as updated or deleted. The message can be retained in the queue table based on the retention properties.  

navigation  

Specifies the position of the message that will be retrieved. First, the position is determined. Second, the search criterion is applied. Finally, the message is retrieved.

NEXT_MESSAGE: Retrieve the next message which is available and matches the search criteria. If the previous message belongs to a message group, then AQ retrieves the next available message which matches the search criteria and belongs to the message group. This is the default.

NEXT_TRANSACTION: Skip the remainder of the current transaction group (if any) and retrieve the first message of the next transaction group. This option can only be used if message grouping is enabled for the current queue.

FIRST_MESSAGE: Retrieves the first message which is available and matches the search criteria. This resets the position to the beginning of the queue.  

visibility  

Specifies whether the new message is dequeued as part of the current transaction.The visibility parameter is ignored when using the BROWSE mode.

ON_COMMIT: The dequeue will be part of the current transaction. This is the default case.

IMMEDIATE: The dequeued message is not part of the current transaction. It constitutes a transaction on its own.  

wait  

Specifies the wait time if there is currently no message available which matches the search criteria.

FOREVER: wait forever. This is the default.

NO_WAIT: do not wait

number: wait time in seconds  

msgid  

Specifies the message identifier of the message to be dequeued.  

correlation  

Specifies the correlation identifier of the message to be dequeued. Special pattern matching characters, such as the percent sign (%) and the underscore (_) can be used. If more than one message satisfies the pattern, then the order of dequeuing is undetermined.  

Message Properties Type

This describes the information that is used by AQ to manage individual messages. These are set at enqueue time, and their values are returned at dequeue time.

Syntax

TYPE message_properties_t IS RECORD (
   priority               BINARY_INTEGER DEFAULT 1,
   delay                  BINARY_INTEGER DEFAULT NO_DELAY,
   expiration             BINARY_INTEGER DEFAULT NEVER,
   correlation            VARCHAR2(128)  DEFAULT NULL,
   attempts               BINARY_INTEGER,
   recipient_list         aq$_recipient_list_t,
   exception_queue        VARCHAR2(51)   DEFAULT NULL,
   enqueue_time           DATE,
   state                  BINARY_INTEGER,
   sender_id              aq$_agent      DEFAULT NULL, 
   original_msgid         RAW(16)        DEFAULT NULL);

TYPE aq$_recipient_list_t IS TABLE OF sys.aq$_agent
   INDEX BY BINARY_INTEGER;
Usage
Table 4-6 Message Properties Type
Parameter  Description 

priority  

Specifies/returns the priority of the message. A smaller number indicates higher priority. The priority can be any number, including negative numbers.  

delay  

Specifies/returns the delay of the enqueued message. The delay represents the number of seconds after which a message is available for dequeuing. Dequeuing by msgid overrides the delay specification. A message enqueued with delay set will be in the WAITING state, when the delay expires the messages goes to the READY state. DELAY processing requires the queue monitor to be started. Note that delay is set by the producer who enqueues the message.

NO_DELAY: the message is available for immediate dequeuing.

number: the number of seconds to delay the message.  

expiration  

Specifies/returns the expiration of the message. It determines, in seconds, the duration the message is available for dequeuing. This parameter is an offset from the delay. Expiration processing requires the queue monitor to be running.

NEVER: message does not expire.

number: number of seconds message remains in READY state. If the message is not dequeued before it expires, then it is moved to the exception queue in the EXPIRED state.  

correlation  

Returns the identification supplied by the producer for a message at enqueuing.  

attempts  

Returns the number of attempts that have been made to dequeue this message. This parameter cannot be set at enqueue time.  

recipient_list  

For type definition, see the "Agent".

This parameter is only valid for queues which allow multiple consumers. The default recipients are the queue subscribers. This parameter is not returned to a consumer at dequeue time.  

exception_queue  

Specifies/returns the name of the queue to which the message is moved if it cannot be processed successfully. Messages are moved in two cases: The number of unsuccessful dequeue attempts has exceeded max_retries or the message has expired. All messages in the exception queue are in the EXPIRED state.

The default is the exception queue associated with the queue table. If the exception queue specified does not exist at the time of the move, then the message is moved to the default exception queue associated with the queue table, and a warning is logged in the alert file. If the default exception queue is used, then the parameter returns a NULL value at dequeue time.  

enqueue_time  

Returns the time the message was enqueued. This value is determined by the system and cannot be set by the user. This parameter can not be set at enqueue time.  

state  

Returns the state of the message at the time of the dequeue. This parameter can not be set at enqueue time.

0: The message is ready to be processed.

1: The message delay has not yet been reached.

2: The message has been processed and is retained.

3: The message has been moved to the exception queue.  

sender_id  

Specifies/returns the application-specified sender identification.

DEFAULT: NULL  

original_msgid  

This parameter is used by Oracle AQ for propagating messages.

DEFAULT: NULL  

AQ Recipient List Type

This identifies the list of agents that will receive the message. This structure is used only when the queue is enabled for multiple dequeues.

Syntax

TYPE aq$_recipient_list_t IS TABLE OF sys.aq$_agent
   INDEX BY BINARY_INTEGER;

AQ Agent List Type

This identifies the list of agents for DBMS_AQ.LISTEN to listen for.

Syntax

TYPE aq$_agent_list_t IS TABLE of sys.aq$_agent 
   INDEX BY BINARY INTEGER;

AQ Subscriber List Type

This identifies the list of subscribers that subscribe to this queue.

Syntax

TYPE aq$_subscriber_list_t IS TABLE OF sys.aq$_agent
   INDEX BY BINARY_INTEGER;

Summary of Subprograms

Table 4-7 DBMS_AQ Package Subprograms
Subprograms  Description  
ENQUEUE procedure
 

Adds a message to the specified queue.  

 
DEQUEUE procedure
 

Dequeues a message from the specified queue.  

 
LISTEN procedure
 

Listen on one or more queues on behalf of a list of agents.  

ENQUEUE procedure

This procedure adds a message to the specified queue.

Syntax

DBMS_AQ.ENQUEUE (
   queue_name          IN      VARCHAR2,
   enqueue_options     IN      enqueue_options_t,
   message_properties  IN      message_properties_t,
   payload             IN      "<type_name>",
   msgid               OUT     RAW);

Parameters

Table 4-8 ENQUEUE Procedure Parameters
Parameter  Description  
queue_name
 

Specifies the name of the queue to which this message should be enqueued. The queue cannot be an exception queue.  

 
enqueue_options
 

See "Enqueue Options Type".  

 
message_properties
 

See "Message Properties Type".  

 
payload
 

Not interpreted by Oracle AQ.

The payload must be specified according to the specification in the associated queue table. NULL is an acceptable parameter.

For the definition of <type_name> please refer to "Type Name".  

 
msgid
 

System generated identification of the message.

This is a globally unique identifier that can be used to identify the message at dequeue time.  

Usage Notes

Using Sequence Deviation

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.

Sending a Message when there are No Recipients

If a message is enqueued to a multi-consumer queue with no recipient, and if the queue has no subscribers (or rule-based subscribers that match this message), then the 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.

DEQUEUE procedure

This procedure dequeues a message from the specified queue.

Syntax

DBMS_AQ.DEQUEUE (
   queue_name          IN      VARCHAR2,
   dequeue_options     IN      dequeue_options_t,
   message_properties  OUT     message_properties_t,
   payload             OUT     "<type_name>",
   msgid               OUT     RAW);

Parameters

Table 4-9 DEQUEUE Procedure Parameters
Parameter  Description  
queue_name
 

Specifies the name of the queue.  

 
dequeue_options
 

See "Dequeue Options Type".  

 
message_properties
 

See "Message Properties Type".  

 
payload
 

Not interpreted by Oracle AQ. The payload must be specified according to the specification in the associated queue table.

For the definition of <type_name> please refer to "Type Name".  

 
msgid
 

System generated identification of the message.  

Usage Notes

Search criteria and dequeue order for messages

The search criteria for messages to be dequeued is determined by the consumer_name, msgid and correlation parameters in dequeue_options. Msgid uniquely identifies the message to be dequeued. Correlation identifiers are application-defined identifiers that are not interpreted by AQ.

Only messages in the READY state are dequeued unless msgid is specified.

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.

Navigating through a queue

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 navigation 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 also be more efficient to use the FIRST_MESSAGE navigation option when there are messages being concurrently enqueued. If the FIRST_MESSAGE option is not specified, then AQ continually generates the snapshot as of the first dequeue command, leading to poor performance. If the FIRST_MESSAGE option is specified, then AQ uses a new snapshot for every dequeue command.  

Dequeue by Message Grouping

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.

LISTEN procedure

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.

If agent-address is a multi-consumer queue, then agent-name is mandatory. For single-consumer queues, agent-name must not be specified.

This is a blocking call that returns when there is a message ready for consumption for an agent in the list. If there are no messages found when the wait time expires, then an error is raised.

Syntax

DBMS_AQ.LISTEN (
   agent_list IN    aq$_agent_list_t,
   wait       IN    BINARY_INTEGER DEFAULT DBMS_AQ.FOREVER, 
   agent      OUT   sys.aq$_agent);

TYPE aq$_agent_list_t IS TABLE of aq$_agent INDEXED BY BINARY_INTEGER;

Parameters

Table 4-10 LISTEN Procedure Parameters
Parameter  Description  
agent_list
 

List of agents for which to 'listen'.  

 
wait
 

Time-out for the listen call (in seconds). By default, the call will block forever.  

 
agent
 

Agent with a message available for consumption.  

Usage Notes

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, then only the first agent listed is returned. If there are no messages found when the wait time expires, then 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 that you cannot call listen on non-persistent queues.


Prev
Next
Oracle
Copyright © 1999 Oracle Corporation.
All Rights Reserved.

Library
Product
Contents
Index