Mixed case (upper and lower case together) queue table names are supported if database compatibility is 10.0, but the names must be enclosed in double quote marks. So abc.efg means the schema is ABC and the name is EFG, but "abc"."efg" means the schema is abc and the name is efg.

Payload type as RAW or an object type

To specify the payload type as an object type, you must define the object type.

CLOB, BLOB, and BFILE objects are valid in an Oracle Streams AQ message. You can propagate these object types using Oracle Streams AQ propagation with Oracle software since Oracle8i release 8.1.x. To enqueue an object type that has a LOB, you must first set the LOB_attribute to EMPTY_BLOB() and perform the enqueue. You can then select the LOB locator that was generated from the queue table's view and use the standard LOB operations.

You can specify and modify primary_instance and secondary_instance only in 8.1-compatible or higher mode. You cannot specify a secondary instance unless there is a primary instance.

Compatible as 8.0, 8.1, or 10.0

This parameter defaults to 8.0 if the database is in 8.0 compatible mode, 8.1 if the database is in 8.1 compatible mode, or 10.0 if the database is in 10.0 compatible mode.

Secure as TRUE or FALSE

This parameter must be set to TRUE if you want to use the queue table for secure queues. Secure queues are queues for which AQ agents must be associated explicitly with one or more database users who can perform queue operations, such as enqueue and dequeue. The owner of a secure queue can perform all queue operations on the queue, but other users cannot perform queue operations on a secure queue, unless they are configured as secure queue users.

Dropping a Queue Table

Purpose

Drops an existing queue table. You must stop and drop all the queues in a queue table before the queue table can be dropped. You must do this explicitly unless the force option is used, in which case these operations are accomplished automatically.

Purging a Queue Table

Purpose

Purges messages from a queue table.

Syntax

DBMS_AQADM.PURGE_QUEUE_TABLE(
queue_table IN VARCHAR2,
purge_condition IN VARCHAR2,
purge_options IN aq$_purge_options_t);

Usage Notes

You can perform various purge operations on both single-consumer and multiconsumer queue tables for persistent queues. You can purge selected messages from the queue table by specifying additional parameters in the API call.

The purge condition must be in the format of a SQL WHERE clause, and it is case-sensitive. The condition is based on the columns of aq$queue_table view.

To purge all queues in a queue table, set purge_condition to either NULL (a bare null word, no quotes) or '' (two single quotes).

A trace file is generated in the udump destination when you run this procedure. It details what the procedure is doing. The procedure commits after it has processed all the messages.

Migrating a Queue Table

If a schema was created by an import of an export dump from a lower release or has Oracle Streams AQ queues upgraded from a lower release, then attempts to drop it with DROP USER CASCADE will fail with ORA-24005. To drop such schemas:

Mixed case (upper and lower case together) queue names and queue table names are supported if database compatibility is 10.0, but the names must be enclosed in double quote marks. So abc.efg means the schema is ABC and the name is EFG, but "abc"."efg" means the schema is abc and the name is efg.

All queue names must be unique within a schema. Once a queue is created with CREATE_QUEUE, it can be enabled by calling START_QUEUE. By default, the queue is created with both enqueue and dequeue disabled. To view retained messages, you can either dequeue by message ID or use SQL. If GLOBAL_TOPIC_ENABLED = TRUE when a queue is created, then a corresponding LDAP entry is also created.

Examples

You must set up or drop data structures for certain examples to work.

Example 8-21 PL/SQL: Creating a Queue Within a Queue Table for Messages of Object Type

Creating a Nonpersistent Queue

The queue can be either single-consumer or multiconsumer. All queue names must be unique within a schema. The queues are created in a 8.1-compatible system-created queue table (AQ$_MEM_SC or AQ$_MEM_MC) in the same schema as that specified by the queue name. If the queue name does not specify a schema name, then the queue is created in the login user's schema.

Once a queue is created with CREATE_NP_QUEUE, it can be enabled by calling START_QUEUE. By default, the queue is created with both enqueue and dequeue disabled.

You can enqueue RAW and Oracle object type messages into a nonpersistent queue. You cannot dequeue from a nonpersistent queue. The only way to retrieve a message from a nonpersistent queue is by using the Oracle Call Interface (OCI) notification mechanism. You cannot invoke the listen call on a nonpersistent queue.

Altering a Queue

DBMS_AQADM.ALTER_QUEUE (
queue_name IN VARCHAR2,
max_retries IN NUMBER DEFAULT NULL,
retry_delay IN NUMBER DEFAULT NULL,
retention_time IN NUMBER DEFAULT NULL,
auto_commit IN BOOLEAN DEFAULT TRUE,
comment IN VARCHAR2 DEFAULT NULL);

Note:

Parameter auto_commit is deprecated.

Usage Notes

Only max_retries, comment, retry_delay, and retention_time can be altered. To view retained messages, you can either dequeue by message ID or use SQL. If GLOBAL_TOPIC_ENABLED = TRUE when a queue is modified, then a corresponding LDAP entry is also altered.

Starting a Queue

Purpose

Enables the specified queue for enqueuing or dequeuing.

Usage Notes

After creating a queue, the administrator must use START_QUEUE to enable the queue. The default is to enable it for both enqueue and dequeue. Only dequeue operations are allowed on an exception queue. This operation takes effect when the call completes and does not have any transactional characteristics.

By default, this call disables both enqueue and dequeue. A queue cannot be stopped if there are outstanding transactions against the queue. This operation takes effect when the call completes and does not have any transactional characteristics.

Examples

Example 8-34 PL/SQL (DBMS_AQADM): Stopping a Queue

EXECUTE DBMS_AQADM.STOP_QUEUE (
queue_name => 'aq.Msg_queue');

Managing Transformations

Creating a Transformation

Purpose

Creates a message format transformation. The transformation must be a SQL function with input type from_type, returning an object of type to_type. It can also be a SQL expression of type to_type, referring to from_type. All references to from_type must be of the form source.user_data.

You must be granted EXECUTE privileges on dbms_transform to use this feature. You must also have EXECUTE privileges on the user-defined types that are the source and destination types of the transformation, and have EXECUTE privileges on any PL/SQL function being used in the transformation function. The transformation cannot write the database state (that is, perform DML operations) or commit or rollback the current transaction.

Modifying a Transformation

Purpose

Changes the transformation function and specifies transformations for each attribute of the target type. If the attribute number 0 is specified, then the transformation expression singularly defines the transformation from the source to target types.

All references to from_type must be of the form source.user_data. All references to the attributes of the source type must be prefixed by source.user_data.

You must be granted EXECUTE privileges on dbms_transform to use this feature. You must also have EXECUTE privileges on the user-defined types that are the source and destination types of the transformation, and have EXECUTE privileges on any PL/SQL function being used in the transformation function.

Dropping a Transformation

You must be granted EXECUTE privileges on dbms_transform to use this feature. You must also have EXECUTE privileges on the user-defined types that are the source and destination types of the transformation, and have EXECUTE privileges on any PL/SQL function being used in the transformation function.

Users granted the ENQUEUE_ANY privilege are allowed to enqueue messages to any queues in the database. Users granted the DEQUEUE_ANY privilege are allowed to dequeue messages from any queues in the database. Users granted the MANAGE_ANY privilege are allowed to run DBMS_AQADM calls on any schemas in the database.

Example

You must set up the following data structures for this example to work:

Users granted the ENQUEUE_ANY privilege are allowed to enqueue messages to any queues in the database. Users granted the DEQUEUE_ANY privilege are allowed to dequeue messages from any queues in the database. Users granted the MANAGE_ANY privilege are allowed to run DBMS_AQADM calls on any schemas in the database.

A program can enqueue messages to a specific list of recipients or to the default list of subscribers. This operation succeeds only on queues that allow multiple consumers. This operation takes effect immediately and the containing transaction is committed. Enqueue requests that are executed after the completion of this call reflect the new action. Any string within the rule must be quoted (with single quotation marks) as follows:

rule => 'PRIORITY <= 3 AND CORRID = ''FROM JAPAN'''

If GLOBAL_TOPIC_ENABLED is set to true when a subscriber is created, then a corresponding LDAP entry is also created.

Specify the name of the transformation to be applied during dequeue or propagation. The transformation must be created using the DBMS_TRANSFORM package.

For queues that contain payloads with XMLType attributes, you can specify rules that contain operators such as XMLType.existsNode() and XMLType.extract().

Note:

ADD_SUBSCRIBER is an administrative operation on a queue. Although Oracle Streams AQ does not prevent applications from issuing administrative and operational calls concurrently, they are executed serially. ADD_SUBSCRIBER blocks until pending transactions that have enqueued or dequeued messages commit and release the resources they hold.

The rule, the transformation, or both can be altered. If you alter only one of these attributes, then specify the existing value of the other attribute to the alter call. If GLOBAL_TOPIC_ENABLED = TRUE when a subscriber is modified, then a corresponding LDAP entry is created.

Examples

You must set up the following data structures for the examples in this section to work:

Removing a Subscriber

This operation takes effect immediately and the containing transaction is committed. All references to the subscriber in existing messages are removed as part of the operation. If GLOBAL_TOPIC_ENABLED = TRUE when a subscriber is dropped, then a corresponding LDAP entry is also dropped.

Note:

REMOVE_SUBSCRIBER is an administrative operation on a queue. Although Oracle Streams AQ does not prevent applications from issuing administrative and operational calls concurrently, they are executed serially. REMOVE_SUBSCRIBER blocks until pending transactions that have enqueued or dequeued messages commit and release the resources they hold.

Messages can also be propagated to other queues in the same database by specifying a NULL destination. If a message has multiple recipients at the same destination in either the same or different queues, then the message is propagated to all of them at the same time.

Verify that the source and destination queues have the same type. The function has the side effect of inserting/updating the entry for the source and destination queues in the dictionary table AQ$_MESSAGE_TYPES.

If the source and destination queues do not have identical types and a transformation was specified, then the transformation must map the source queue type to the destination queue type.

Note:

The sys.aq$_message_types table can have multiple entries for the same source queue, destination queue, and database link, but with different transformations.

Examples

You must set up the following data structures for this example to work:

This call takes the name of an alias and the distinguished name of an Oracle Streams AQ object in LDAP, and creates the alias that points to the Oracle Streams AQ object. The alias is placed immediately under the distinguished name of the database server. The object to which the alias points can be a queue, an agent, or a connection factory.

Deleting an Alias from the LDAP Server

Purpose

Removes an alias from the LDAP server.

Syntax

DBMS_AQ.DEL_ALIAS_FROM_LDAP(
alias IN VARCHAR2);

Usage Notes

This call takes the name of an alias as the argument, and removes the alias entry in the LDAP server. It is assumed that the alias is placed immediately under the database server in the LDAP directory.