Oracle8i Supplied PL/SQL Packages Reference
Release 2 (8.1.6)
Part Number A76936-01

Library
Product
Contents
Index

Go to previous page Go to next page

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;
Go to previous page Go to next page
Oracle
Copyright © 1996-2000, Oracle Corporation.
All Rights Reserved.

Library
Product
Contents
Index