5
DBMS_AQADM

The DBMS_AQADM package provides procedures to manage Advanced Queuing configuration and administration information.

See Also:
Oracle8i Application Developer's Guide - Advanced Queuing contains detailed information about DBMS_AQADM.

Enumerated Constants

When using enumerated constants, such as INFINITE,TRANSACTIONAL, or NORMAL_QUEUE, the symbol must be specified with the scope of the packages defining it. All types associated with the administrative interfaces must be prepended with DBMS_AQADM. For example:

DBMS_AQADM.NORMAL_QUEUE

Table 5-1 Enumerated Types in the Administrative Interface

Parameter	Options
retention	0,1,2...INFINITE
message_grouping	TRANSACTIONAL, NONE
queue_type	NORMAL_QUEUE, EXCEPTION_QUEUE, NON_PERSISTENT_QUEUE

See Also:
For more information on the Java classes and data structures used in both DBMS_AQ and DBMS_AQADM, see Chapter 4, "DBMS_AQ"

Summary of Subprograms

Table 5-2 DBMS_AQADM Package Subprograms

Subprogram	Description
CREATE_QUEUE_TABLE procedure	Creates a queue table for messages of a pre-defined type.
ALTER_QUEUE_TABLE procedure	Alters an existing queue table.
DROP_QUEUE_TABLE procedure	Drops an existing queue table.
CREATE_QUEUE procedure	Creates a queue in the specified queue table.
CREATE_NP_QUEUE procedure	Creates a non-persistent `RAW` queue.
ALTER_QUEUE procedure	Alters existing properties of a queue.
DROP_QUEUE procedure	Drops an existing queue.
START_QUEUE procedure	Enables the specified queue for enqueuing and/or dequeueing.
STOP_QUEUE procedure	Disables enqueuing and/or dequeuing on the specified queue.
GRANT_SYSTEM_PRIVILEGE procedure	Grants AQ system privileges to users and roles.
REVOKE_SYSTEM_PRIVILEGE procedure	Revokes AQ system privileges from users and roles.
GRANT_QUEUE_PRIVILEGE procedure	Grants privileges on a queue to users and roles.
REVOKE_QUEUE_PRIVILEGE procedure	Revokes privileges on a queue from users and roles.
ADD_SUBSCRIBER procedure	Adds a default subscriber to a queue.
ALTER_SUBSCRIBER procedure	Alters existing properties of a subscriber to a specified queue.
REMOVE_SUBSCRIBER procedure	Removes a default subscriber from a queue.
SCHEDULE_PROPAGATION procedure	Schedules propagation of messages from a queue to a destination identified by a specific dblink.
UNSCHEDULE_PROPAGATION procedure	Unschedules previously scheduled propagation of messages from a queue to a destination identified by a specific dblink.
VERIFY_QUEUE_TYPES procedure	Verifies that the source and destination queues have identical types.
ALTER_PROPAGATION_SCHEDULE procedure	Alters parameters for a propagation schedule.
ENABLE_PROPAGATION_SCHEDULE procedure	Enables a previously disabled propagation schedule.
DISABLE_PROPAGATION_SCHEDULE Procedure	Disables a propagation schedule.

CREATE_QUEUE_TABLE procedure

This procedure creates a queue table for messages of a pre-defined type. The sort keys for dequeue ordering, if any, must be defined at table creation time. The following objects are created at this time:

A default exception queue associated with the queue table, called aq$_<queue_table_name>_e.
A read-only view, which is used by AQ applications for querying queue data, called aq$<queue_table_name>.
An index or an index organized table (IOT) in the case of multiple consumer queues for the queue monitor operations, called aq$_<queue_table_name>_t.
An index or an index organized table in the case of multiple consumer queues for dequeue operations, called aq$_<queue_table_name>_i.

For Oracle8i-compatible queue tables, the following two index organized tables are created:

A table called aq$_<queue_table_name>_s. This table stores information about the subscribers.
A table called aq$_<queue_table_name>_r. This table stores information about rules on subscriptions.
An index organized table called aq$_<queue_table_name>_h. This table stores the dequeue history data.

Syntax

DBMS_AQADM.CREATE_QUEUE_TABLE (
   queue_table        IN      VARCHAR2,
   queue_payload_type IN      VARCHAR2,
   storage_clause     IN      VARCHAR2       DEFAULT NULL,
   sort_list          IN      VARCHAR2       DEFAULT NULL,
   multiple_consumers IN      BOOLEAN        DEFAULT FALSE,
   message_grouping   IN      BINARY_INTEGER DEFAULT NONE,
   comment            IN      VARCHAR2       DEFAULT NULL,
   auto_commit        IN      BOOLEAN        DEFAULT TRUE,
   primary_instance   IN      BINARY_INTEGER DEFAULT 0, 
   secondary_instance IN      BINARY_INTEGER DEFAULT 0,
   compatible         IN      VARCHAR2       DEFAULT NULL);

Parameters

Table 5-3 CREATE_QUEUE_TABLE Procedure Parameters

Parameter	Description
queue_table	Name of a queue table to be created.
queue_payload_type	Type of the user data stored. See "Type Name" for valid values for this parameter.
storage_clause	Storage parameter. The storage parameter is included in the `CREATE` `TABLE` statement when the queue table is created. The storage parameter can be made up of any combinations of the following parameters: `PCTFREE`, `PCTUSED`, `INITRANS`, `MAXTRANS`, `TABLEPSACE`, `LOB`, and a table storage clause. If a tablespace is not specified here, then the queue table and all its related objects are created in the default user tablespace. If a tablespace is specified here, then the queue table and all its related objects are created in the tablespace specified in the storage clause. See Oracle8i SQL Reference for the usage of these parameters.
sort_list	The columns to be used as the sort key in ascending order. `Sort_list` has the following format: '<`sort_column_1`>,<`sort_column_2`>' The allowed column names are `priority` and `enq_time`. If both columns are specified, then <`sort_column_1`> defines the most significant order. After a queue table is created with a specific ordering mechanism, all queues in the queue table inherit the same defaults. The order of a queue table cannot be altered after the queue table has been created. If no sort list is specified, then all the queues in this queue table are sorted by the enqueue time in ascending order. This order is equivalent to FIFO order. Even with the default ordering defined, a dequeuer is allowed to choose a message to dequeue by specifying its `msgid` or `correlation`. `Msgid`, `correlation`, and `sequence_deviation` take precedence over the default dequeueing order, if they are specified.
multiple_consumers	`FALSE`: Queues created in the table can only have one consumer per message. This is the default. `TRUE`: Queues created in the table can have multiple consumers per message.
message_grouping	Message grouping behavior for queues created in the table. `NONE`: Each message is treated individually. `TRANSACTIONAL`: Messages enqueued as part of one transaction are considered part of the same group and can be dequeued as a group of related messages.
comment	User-specified description of the queue table. This user comment is added to the queue catalog.
auto_commit	`TRUE`: causes the current transaction, if any, to commit before the `CREATE_QUEUE_TABLE` operation is carried out. The `CREATE_QUEUE_TABLE` operation becomes persistent when the call returns. This is the default. `FALSE`: The operation is part of the current transaction and becomes persistent only when the caller enters a commit. Caution: This parameter has been deprecated.
primary_instance	The primary owner of the queue table. Queue monitor scheduling and propagation for the queues in the queue table are done in this instance. The default value for primary instance is 0, which means queue monitor scheduling and propagation will be done in any available instance.
secondary_instance	The queue table fails over to the secondary instance if the primary instance is not available. The default value is 0, which means that the queue-table will fail over to any available instance.
compatible	The lowest database version with which the queue is compatible. Currently the possible values are either '8.0' or '8.1'. The default is '8.0'.

Usage Notes

CLOB, BLOB, and BFILE are valid attributes for AQ object type payloads. However, only CLOB and BLOB can be propagated using AQ propagation in Oracle8i release 8.1.5. See the Oracle8i Application Developer's Guide - Advanced Queuing for more information.

You can specify and modify the primary_instance and secondary_instance only in 8.1-compatible mode.

You cannot specify a secondary instance unless there is a primary instance.

ALTER_QUEUE_TABLE procedure

This procedure alters the existing properties of a queue table.

Syntax

DBMS_AQADM.ALTER_QUEUE_TABLE (
   queue_table          IN   VARCHAR2, 
   comment              IN   VARCHAR2       DEFAULT NULL,
   primary_instance     IN   BINARY_INTEGER DEFAULT NULL, 
   secondary_instance   IN   BINARY_INTEGER DEFAULT NULL);

Parameters

Table 5-4 ALTER_QUEUE_TABLE Procedure Parameters

Parameter	Description
queue_table	Name of a queue table to be created.
comment	Modifies the user-specified description of the queue table. This user comment is added to the queue catalog. The default value is `NULL` which means that the value will not be changed.
primary_instance	This is the primary owner of the queue table. Queue monitor scheduling and propagation for the queues in the queue table will be done in this instance. The default value is `NULL`, which means that the current value will not be changed.
secondary_instance	The queue table fails over to the secondary instance if the primary instance is not available. The default value is `NULL`, which means that the current value will not be changed.

DROP_QUEUE_TABLE procedure

This procedure drops an existing queue table. All the queues in a queue table must be stopped and dropped before the queue table can be dropped. You must do this explicitly unless the force option is used, in which case this done automatically.

Syntax

DBMS_AQADM.DROP_QUEUE_TABLE (
   queue_table       IN    VARCHAR2,
   force             IN    BOOLEAN DEFAULT FALSE,
   auto_commit       IN    BOOLEAN DEFAULT TRUE);

Parameters

Table 5-5 DROP_QUEUE_TABLE Procedure Parameters

Parameter Description

Parameter	Description
queue_table	Name of a queue table to be dropped.
force	`FALSE`: The operation does not succeed if there are any queues in the table. This is the default. `TRUE`: All queues in the table are stopped and dropped automatically.
auto_commit	`TRUE`: Causes the current transaction, if any, to commit before the `DROP_QUEUE_TABLE` operation is carried out. The `DROP_QUEUE_TABLE` operation becomes persistent when the call returns. This is the default. `FALSE`: The operation is part of the current transaction and becomes persistent only when the caller enters a commit. Caution: This parameter has been deprecated.

queue_table

Name of a queue table to be dropped.

force

FALSE: The operation does not succeed if there are any queues in the table. This is the default.

TRUE: All queues in the table are stopped and dropped automatically.

auto_commit

TRUE: Causes the current transaction, if any, to commit before the DROP_QUEUE_TABLE operation is carried out. The DROP_QUEUE_TABLE operation becomes persistent when the call returns. This is the default.

FALSE: The operation is part of the current transaction and becomes persistent only when the caller enters a commit.

Caution: This parameter has been deprecated.

CREATE_QUEUE procedure

This procedure creates a queue in the specified queue table.

Syntax

DBMS_AQADM.CREATE_QUEUE (
   queue_name          IN       VARCHAR2,
   queue_table         IN       VARCHAR2,
   queue_type          IN       BINARY_INTEGER DEFAULT NORMAL_QUEUE,
   max_retries         IN       NUMBER         DEFAULT NULL,
   retry_delay         IN       NUMBER         DEFAULT 0,
   retention_time      IN       NUMBER         DEFAULT 0,
   dependency_tracking IN       BOOLEAN        DEFAULT FALSE,
   comment             IN       VARCHAR2       DEFAULT NULL,
   auto_commit         IN       BOOLEAN        DEFAULT TRUE);

Parameters

Table 5-6 CREATE_QUEUE Procedure Parameters

Parameter	Description
queue_name	Name of the queue that is to be created. The name must be unique within a schema and must follow object name guidelines in the Oracle8i SQL Reference with regard to reserved characters.
queue_table	Name of the queue table that will contain the queue.
queue_type	Specifies whether the queue being created is an exception queue or a normal queue. `NORMAL_QUEUE`: The queue is a normal queue. This is the default. `EXCEPTION_QUEUE`: It is an exception queue. Only the dequeue operation is allowed on the exception queue.
max_retries	Limits the number of times a dequeue with the `REMOVE` mode can be attempted on a message. The count is incremented when the application issues a rollback after executing the dequeue. The message is moved to the exception queue when it is reaches its `max_retries`. Note that `max_retries` is supported for all single consumer queues and 8.1-compatible multiconsumer queues but not for 8.0-compatible multiconsumer queues.
retry_delay	Delay time, in seconds, before this message is scheduled for processing again after an application rollback. The default is 0, which means the message can be retried as soon as possible. This parameter has no effect if `max_retries` is set to 0. Note that `rety_delay` is supported for single consumer queues and 8.1-compatible multiconsumer queues but not for 8.0-compatible multiconsumer queues.
retention_time	Number of seconds for which a message is retained in the queue table after being dequeued from the queue. `INFINITE`: Message is retained forever. `NUMBER`: Number of seconds for which to retain the messages. The default is 0; i.e. no retention.
dependency_ tracking	Reserved for future use. `FALSE`: This is the default. `TRUE`: Not permitted in this release.
comment	User-specified description of the queue. This user comment is added to the queue catalog.
auto_commit	`TRUE`: Causes the current transaction, if any, to commit before the `CREATE_QUEUE` operation is carried out. The `CREATE_QUEUE` operation becomes persistent when the call returns. This is the default. `FALSE`: The operation is part of the current transaction and becomes persistent only when the caller enters a commit. Caution: This parameter has been deprecated.

Usage Notes

All queue names must be unique within a schema. After 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.

CREATE_NP_QUEUE procedure

Create a non-persistent RAW queue.

Syntax

DBMS_AQADM.CREATE_NP_QUEUE ( 
   queue_name              IN        VARCHAR2, 
   multiple_consumers      IN        BOOLEAN  DEFAULT FALSE, 
   comment                 IN        VARCHAR2 DEFAULT NULL);

Parameters

Table 5-7 CREATE_NP_QUEUE Procedure Parameters

Parameter Description

Parameter	Description
queue_name	Name of the non-persistent queue that is to be created. The name must be unique within a schema and must follow object name guidelines in the Oracle8i SQL Reference with regard to reserved characters.
multiple_consumers	`FALSE`: Queues created in the table can only have one consumer per message. This is the default. `TRUE`: Queues created in the table can have multiple consumers per message. Note that thi`s` parameter is distinguished at the queue level, because a non-persistent queue does not inherit this characteristic from any user-created queue table.
comment	User-specified description of the queue. This user comment is added to the queue catalog.

queue_name

Name of the non-persistent queue that is to be created. The name must be unique within a schema and must follow object name guidelines in the Oracle8i SQL Reference with regard to reserved characters.

multiple_consumers

FALSE: Queues created in the table can only have one consumer per message. This is the default.

TRUE: Queues created in the table can have multiple consumers per message.

Note that this parameter is distinguished at the queue level, because a non-persistent queue does not inherit this characteristic from any user-created queue table.

comment

User-specified description of the queue. This user comment is added to the queue catalog.

Usage Notes

The queue may be either single-consumer or multiconsumer queue. 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. After 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 cannot dequeue from a non-persistent queue. The only way to retrieve a message from a non-persistent queue is by using the OCI notification mechanism.

You cannot invoke the listen call on a non-persistent queue.

ALTER_QUEUE procedure

This procedure alters existing properties of a queue. The parameters max_retries, retention_time, and retry_delay are not supported for non-persistent queues.

Syntax

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);

Parameters



Table 5-8  ALTER_QUEUE Procedure Parameters
 





Parameter 


Description 


    
queue_name
      



Name of the queue that is to be altered.  

    
max_retries
      



Limits the number of times a dequeue with REMOVE mode can be attempted on a message.


The count is incremented when the application issues a rollback after executing the dequeue. If the time at which one of the retries has passed the expiration time, then no further retries are attempted. Default is NULL, which means that the value will not be altered.


Note that max_retries is supported for all single consumer queues and 8.1-compatible multiconsumer queues but not for 8.0-compatible multiconsumer queues.  

    
retry_delay
      



Delay time in seconds before this message is scheduled for processing again after an application rollback. The default is NULL, which means that the value will not be altered. 


Note that retry_delay is supported for single consumer queues and 8.1-compatible multiconsumer queues but not for 8.0-compatible multiconsumer queues.  

    
retention_time
      



Retention time in seconds for which a message is retained in the queue table after being dequeued. The default is NULL, which means that the value will not be altered.  

    
auto_commit
      



TRUE: Causes the current transaction, if any, to commit before the ALTER_QUEUE operation is carried out. The ALTER_QUEUE operation become persistent when the call returns. This is the default.


FALSE: The operation is part of the current transaction and becomes persistent only when the caller enters a commit. 


Caution: This parameter has been deprecated.  

    
comment
      



User-specified description of the queue. This user comment is added to the queue catalog. The default value is NULL, which means that the value will not be changed.

Parameter	Description
queue_name	Name of the queue that is to be altered.
max_retries	Limits the number of times a dequeue with `REMOVE` mode can be attempted on a message. The count is incremented when the application issues a rollback after executing the dequeue. If the time at which one of the retries has passed the expiration time, then no further retries are attempted. Default is `NULL`, which means that the value will not be altered. Note that `max_retries` is supported for all single consumer queues and 8.1-compatible multiconsumer queues but not for 8.0-compatible multiconsumer queues.
retry_delay	Delay time in seconds before this message is scheduled for processing again after an application rollback. The default is `NULL`, which means that the value will not be altered. Note that `retry_delay` is supported for single consumer queues and 8.1-compatible multiconsumer queues but not for 8.0-compatible multiconsumer queues.
retention_time	Retention time in seconds for which a message is retained in the queue table after being dequeued. The default is `NULL`, which means that the value will not be altered.
auto_commit	`TRUE`: Causes the current transaction, if any, to commit before the `ALTER_QUEUE` operation is carried out. The `ALTER_QUEUE` operation become persistent when the call returns. This is the default. `FALSE`: The operation is part of the current transaction and becomes persistent only when the caller enters a commit. Caution: This parameter has been deprecated.
comment	User-specified description of the queue. This user comment is added to the queue catalog. The default value is `NULL`, which means that the value will not be changed.

DROP_QUEUE procedure

This procedure drops an existing queue. DROP_QUEUE is not allowed unless STOP_QUEUE has been called to disable the queue for both enqueuing and dequeuing. All the queue data is deleted as part of the drop operation.

Syntax

DBMS_AQADM.DROP_QUEUE (
   queue_name        IN    VARCHAR2,
   auto_commit       IN    BOOLEAN DEFAULT TRUE);

Parameters

Table 5-9 DROP_QUEUE Procedure Parameters

Parameter Description

queue_name

Name of the queue that is to be dropped.

auto_commit

TRUE: Causes the current transaction, if any, to commit before the DROP_QUEUE operation is carried out. The DROP_QUEUE operation becomes persistent when the call returns. This is the default.

FALSE: The operation is part of the current transaction and becomes persistent only when the caller enters a commit.

Caution: This parameter has been deprecated.

START_QUEUE procedure

This procedure enables the specified queue for enqueuing and/or dequeueing.

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.

Syntax

DBMS_AQADM.START_QUEUE ( 
   queue_name      IN     VARCHAR2,
   enqueue         IN     BOOLEAN DEFAULT TRUE,
   dequeue         IN     BOOLEAN DEFAULT TRUE);

Parameters

Table 5-10 START_QUEUE Procedure Parameters

Parameter Description

Parameter	Description
queue_name	Name of the queue to be enabled.
enqueue	Specifies whether `ENQUEUE` should be enabled on this queue. `TRUE`: Enable `ENQUEUE`. This is the default. `FALSE`: Do not alter the current setting.
dequeue	Specifies whether `DEQUEUE` should be enabled on this queue. `TRUE`: Enable `DEQUEUE`. This is the default. `FALSE`: Do not alter the current setting.

queue_name

Name of the queue to be enabled.

enqueue

Specifies whether ENQUEUE should be enabled on this queue.

TRUE: Enable ENQUEUE. This is the default.

FALSE: Do not alter the current setting.

dequeue

Specifies whether DEQUEUE should be enabled on this queue.

TRUE: Enable DEQUEUE. This is the default.

FALSE: Do not alter the current setting.

STOP_QUEUE procedure

This procedure disables enqueuing and/or dequeuing on the specified queue.

By default, this call disables both ENQUEUEs or DEQUEUEs. 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.

Syntax

DBMS_AQADM.STOP_QUEUE (   
   queue_name      IN   VARCHAR2,
   enqueue         IN   BOOLEAN DEFAULT TRUE,
   dequeue         IN   BOOLEAN DEFAULT TRUE,
   wait            IN   BOOLEAN DEFAULT TRUE);

Parameters

Table 5-11 STOP_QUEUE Procedure Parameters

Parameter	Description
queue_name	Name of the queue to be disabled.
enqueue	Specifies whether `ENQUEUE` should be disabled on this queue. `TRUE`: Disable `ENQUEUE`. This is the default. `FALSE`: Do not alter the current setting.
dequeue	Specifies whether `DEQUEUE` should be disabled on this queue. `TRUE`: Disable `DEQUEUE`. This is the default. `FALSE`: Do not alter the current setting.
wait	Specifies whether to wait for the completion of outstanding transactions. `TRUE`: Wait if there are any outstanding transactions. In this state no new transactions are allowed to enqueue to or dequeue from this queue. `FALSE`: Return immediately either with a success or an error.

GRANT_SYSTEM_PRIVILEGE procedure

This procedure grants AQ system privileges to users and roles. The privileges are ENQUEUE_ANY, DEQUEUE_ANY, and MANAGE_ANY. Initially, only SYS and SYSTEM can use this procedure successfully.

Syntax

DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE (
   privilege         IN    VARCHAR2,
   grantee           IN    VARCHAR2,
   admin_option      IN    BOOLEAN := FALSE);

Parameters

Table 5-12 GRANT_SYSTEM_PRIVILEGE Procedure Parameters

Parameter Description

Parameter	Description
privilege	The AQ system privilege to grant. The options are `ENQUEUE_ANY`, `DEQUEUE_ANY`, and `MANAGE_ANY`. The operations allowed for each system privilege are specified as follows: `ENQUEUE_ANY`: users granted with this privilege are allowed to enqueue messages to any queues in the database. `DEQUEUE_ANY`: users granted with this privilege are allowed to dequeue messages from any queues in the database. `MANAGE_ANY`: users granted with this privilege are allowed to run `DBMS_AQADM` calls on any schemas in the database.
grantee	Grantee(s). The grantee(s) can be a user, a role, or the `PUBLIC` role.
admin_option	Specifies if the system privilege is granted with the `ADMIN` option or not. If the privilege is granted with the `ADMIN` option, then the grantee is allowed to use this procedure to grant the system privilege to other users or roles. The default is `FALSE`.

privilege

The AQ system privilege to grant. The options are ENQUEUE_ANY, DEQUEUE_ANY, and MANAGE_ANY.

The operations allowed for each system privilege are specified as follows:

ENQUEUE_ANY: users granted with this privilege are allowed to enqueue messages to any queues in the database.

DEQUEUE_ANY: users granted with this privilege are allowed to dequeue messages from any queues in the database.

MANAGE_ANY: users granted with this privilege are allowed to run DBMS_AQADM calls on any schemas in the database.

grantee

Grantee(s). The grantee(s) can be a user, a role, or the PUBLIC role.

admin_option

Specifies if the system privilege is granted with the ADMIN option or not.

If the privilege is granted with the ADMIN option, then the grantee is allowed to use this procedure to grant the system privilege to other users or roles. The default is FALSE.

REVOKE_SYSTEM_PRIVILEGE procedure

This procedure revokes AQ system privileges from users and roles. The privileges are ENQUEUE_ANY, DEQUEUE_ANY and MANAGE_ANY. The ADMIN option for a system privilege cannot be selectively revoked.

Syntax

DBMS_AQADM.REVOKE_SYSTEM_PRIVILEGE (
   privilege         IN   VARCHAR2,
   grantee           IN   VARCHAR2);

Parameters

Table 5-13 REVOKE_SYSTEM_PRIVILEGE Procedure Parameters

Parameter Description

Parameter	Description
privilege	The AQ system privilege to revoke. The options are `ENQUEUE_ANY`, `DEQUEUE_ANY`, and `MANAGE_ANY`. The `ADMIN` option for a system privilege cannot be selectively revoked.
grantee	Grantee(s). The grantee(s) can be a user, a role, or the `PUBLIC` role.

privilege

The AQ system privilege to revoke. The options are ENQUEUE_ANY, DEQUEUE_ANY, and MANAGE_ANY.

The ADMIN option for a system privilege cannot be selectively revoked.

grantee

Grantee(s). The grantee(s) can be a user, a role, or the PUBLIC role.

GRANT_QUEUE_PRIVILEGE procedure

This procedure grants privileges on a queue to users and roles. The privileges are ENQUEUE or DEQUEUE. Initially, only the queue table owner can use this procedure to grant privileges on the queues.

Syntax

DBMS_AQADM.GRANT_QUEUE_PRIVILEGE (
   privilege        IN    VARCHAR2,
   queue_name       IN    VARCHAR2,
   grantee          IN    VARCHAR2,
   grant_option     IN    BOOLEAN := FALSE);

Parameters

Table 5-14 GRANT_QUEUE_PRIVILEGE Procedure Parameters

Parameter	Description
privilege	The AQ queue privilege to grant. The options are `ENQUEUE`, `DEQUEUE`, and `ALL`. `ALL` means both `ENQUEUE` and `DEQUEUE`.
queue_name	Name of the queue.
grantee	Grantee(s). The grantee(s) can be a user, a role, or the `PUBLIC` role.
grant_option	Specifies if the access privilege is granted with the `GRANT` option or not. If the privilege is granted with the `GRANT` option, then the grantee is allowed to use this procedure to grant the access privilege to other users or roles, regardless of the ownership of the queue table. The default is `FALSE`.

REVOKE_QUEUE_PRIVILEGE procedure

This procedure revokes privileges on a queue from users and roles. The privileges are ENQUEUE or DEQUEUE. To revoke a privilege, the revoker must be the original grantor of the privilege. The privileges propagated through the GRANT option are revoked if the grantor's privileges are revoked.

Syntax

DBMS_AQADM.REVOKE_QUEUE_PRIVILEGE (
   privilege         IN      VARCHAR2,
   queue_name        IN      VARCHAR2,
   grantee           IN      VARCHAR2);

Parameters

Table 5-15 REVOKE_QUEUE_PRIVILEGE Procedure Parameters

Parameter	Description
privilege	The AQ queue privilege to revoke. The options are `ENQUEUE`, `DEQUEUE`, and `ALL`. `ALL` means both `ENQUEUE` and `DEQUEUE`.
queue_name	Name of the queue.
grantee	Grantee(s). The grantee(s) can be a user, a role, or the `PUBLIC` role. If the privilege has been propagated by the grantee through the `GRANT` option, then the propagated privilege is also revoked.

ADD_SUBSCRIBER procedure

This procedure adds a default subscriber to a queue.

Syntax

DBMS_AQADM.ADD_SUBSCRIBER (
   queue_name     IN    VARCHAR2,
   subscriber     IN    sys.aq$_agent,
   rule           IN    VARCHAR2 DEFAULT NULL);

Parameters

Table 5-16 ADD_SUBSCRIBER Procedure Parameters

Parameter Description

queue_name

Name of the queue.

subscriber

Agent on whose behalf the subscription is being defined.

rule

A conditional expression based on the message properties, the message data properties and PL/SQL functions.

A rule 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). Currently supported message properties are priority and corrid.

To specify rules 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. The rule parameter cannot exceed 4000 characters.

Usage Notes

A program can enqueue messages to a specific list of recipients or to the default list of subscribers. This operation only succeeds 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 will reflect the new behavior.

Any string within the rule must be quoted as shown below;

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

Note that these are all single quotation marks.

ALTER_SUBSCRIBER procedure

This procedure alters existing properties of a subscriber to a specified queue. Only the rule can be altered.

Syntax

DBMS_AQADM.ALTER_SUBSCRIBER (
   queue_name     IN    VARCHAR2,
   subscriber     IN    sys.aq$_agent,
   rule           IN    VARCHAR2);

Parameters

Table 5-17 ALTER_SUBSCRIBER Procedure Parameters

Parameter Description

queue_name

Name of the queue.

subscriber

Agent on whose behalf the subscription is being altered. See "Agent".

rule

A conditional expression based on the message properties, the message data properties and PL/SQL functions.

Note: The rule parameter cannot exceed 4000 characters. To eliminate the rule, set the rule parameter to NULL.

REMOVE_SUBSCRIBER procedure

This procedure removes a default subscriber from a queue. 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.

Syntax

DBMS_AQADM.REMOVE_SUBSCRIBER (
   queue_name         IN         VARCHAR2,
   subscriber         IN         sys.aq$_agent);

Parameters

Table 5-18 REMOVE_SUBSCRIBER Procedure Parameters

Parameter	Description
queue_name	Name of the queue.
subscriber	Agent who is being removed. See "Agent".

SCHEDULE_PROPAGATION procedure

This procedure schedules propagation of messages from a queue to a destination identified by a specific dblink.

Messages may 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.

Syntax

DBMS_AQADM.SCHEDULE_PROPAGATION (
   queue_name      IN    VARCHAR2,
   destination     IN    VARCHAR2 DEFAULT NULL,
   start_time      IN    DATE     DEFAULT SYSDATE,
   duration        IN    NUMBER   DEFAULT NULL,
   next_time       IN    VARCHAR2 DEFAULT NULL,
   latency         IN    NUMBER   DEFAULT 60);

Parameters

Table 5-19 SCHEDULE_PROPAGATION Procedure Parameters

Parameter	Description
queue_name	Name of the source queue whose messages are to be propagated, including the schema name. If the schema name is not specified, then it defaults to the schema name of the administrative user.
destination	Destination dblink. Messages in the source queue for recipients at this destination are propagated. If it is `NULL`, then the destination is the local database and messages are propagated to other queues in the local database. The length of this field is currently limited to 128 bytes, and if the name is not fully qualified, then the default domain name is used.
start_time	Initial start time for the propagation window for messages from the source queue to the destination.
duration	Duration of the propagation window in seconds. A `NULL` value means the propagation window is forever or until the propagation is unscheduled.
next_time	Date function to compute the start of the next propagation window from the end of the current window. If this value is `NULL`, then propagation is stopped at the end of the current window. For example, to start the window at the same time every day, next_time should be specified as '`SYSDATE` + 1 - duration/86400'.
latency	Maximum wait, in seconds, in the propagation window for a message to be propagated after it is enqueued. For example: If the latency is 60 seconds, then during the propagation window; if there are no messages to be propagated, then messages from that queue for the destination are not propagated for at least 60 more seconds. It is at least 60 seconds before the queue is checked again for messages to be propagated for the specified destination. If the latency is 600, then the queue is not checked for 10 minutes, and if the latency is 0, then a job queue process will be waiting for messages to be enqueued for the destination. As soon as a message is enqueued, it is propagated.

UNSCHEDULE_PROPAGATION procedure

This procedure unschedules previously scheduled propagation of messages from a queue to a destination identified by a specific dblink.

Syntax

DBMS_AQADM.UNSCHEDULE_PROPAGATION (
   queue_name     IN   VARCHAR2,
   destination    IN   VARCHAR2 DEFAULT NULL);

Parameters

Table 5-20 UNSCHEDULE_PROPAGATION Procedure Parameters

Parameter Description

queue_name

Name of the source queue whose messages are to be propagated, including the schema name.

If the schema name is not specified, then it defaults to the schema name of the administrative user.

destination

Destination dblink.

Messages in the source queue for recipients at this destination are propagated. If it is NULL, then the destination is the local database and messages are propagated to other queues in the local database. The length of this field is currently limited to 128 bytes, and if the name is not fully qualified, then the default domain name is used.

VERIFY_QUEUE_TYPES procedure

This procedure verifies that the source and destination queues have identical types. The result of the verification is stored in the table sys.aq$_message_types, overwriting all previous output of this command.

Syntax

DBMS_AQADM.VERIFY_QUEUE_TYPES (
   src_queue_name    IN    VARCHAR2,
   dest_queue_name   IN    VARCHAR2,
   destination       IN    VARCHAR2 DEFAULT NULL,
   rc                OUT   BINARY_INTEGER);

Parameters

Table 5-21 VERIFY_QUEUE_TYPES Procedure Parameters

Parameter	Description
src_queue_name	Name of the source queue whose messages are to be propagated, including the schema name. If the schema name is not specified, then it defaults to the schema name of the user.
dest_queue_name	Name of the destination queue where messages are to be propagated, including the schema name. If the schema name is not specified, then it defaults to the schema name of the user.
destination	Destination dblink. Messages in the source queue for recipients at this destination are propagated. If it is `NULL`, then the destination is the local database and messages are propagated to other queues in the local database. The length of this field is currently limited to 128 bytes, and if the name is not fully qualified, then the default domain name is used.
rc	Return code for the result of the procedure. If there is no error, and if the source and destination queue types match, then the result is 1. If they do not match, then the result is 0. If an Oracle error is encountered, then it is returned in `rc`.

ALTER_PROPAGATION_SCHEDULE procedure

This procedure alters parameters for a propagation schedule.

Syntax

DBMS_AQADM.ALTER_PROPAGATION_SCHEDULE ( 
   queue_name      IN     VARCHAR2, 
   destination     IN     VARCHAR2 DEFAULT NULL,
   duration        IN     NUMBER   DEFAULT NULL, 
   next_time       IN     VARCHAR2 DEFAULT NULL, 
   latency         IN     NUMBER   DEFAULT 60);

Parameters

Table 5-22 ALTER_PROPAGATION_SCHEDULE Procedure Parameters

Parameter	Description
queue_name	Name of the source queue whose messages are to be propagated, including the schema name. If the schema name is not specified, then it defaults to the schema name of the user.
destination	Destination dblink. Messages in the source queue for recipients at this destination are propagated. If it is `NULL`, then the destination is the local database and messages are propagated to other queues in the local database. The length of this field is currently limited to 128 bytes, and if the name is not fully qualified, then the default domain name is used.
duration	Duration of the propagation window in seconds. A `NULL` value means the propagation window is forever or until the propagation is unscheduled.
next_time	Date function to compute the start of the next propagation window from the end of the current window. If this value is `NULL`, then propagation is stopped at the end of the current window. For example, to start the window at the same time every day, `next_time` should be specified as '`SYSDATE` `+` `1` `-` `duration/86400`'.
latency	Maximum wait, in seconds, in the propagation window for a message to be propagated after it is enqueued. The default value is 60. Caution: if latency is not specified for this call, then latency will over-write any existing value with the default value. For example, if the latency is 60 seconds, then during the propagation window; if there are no messages to be propagated, then messages from that queue for the destination will not be propagated for at least 60 more seconds. It will be at least 60 seconds before the queue will be checked again for messages to be propagated for the specified destination. If the latency is 600, then the queue will not be checked for 10 minutes and if the latency is 0, then a job queue process will be waiting for messages to be enqueued for the destination and as soon as a message is enqueued it will be propagated.

ENABLE_PROPAGATION_SCHEDULE procedure

This procedure enables a previously disabled propagation schedule.

Syntax

DBMS_AQADM.ENABLE_PROPAGATION_SCHEDULE ( 
   queue_name      IN     VARCHAR2, 
   destination     IN     VARCHAR2 DEFAULT NULL);

Parameters

Table 5-23 ENABLE_PROPAGATION_SCHEDULE Procedure Parameters

Parameter Description

queue_name

Name of the source queue whose messages are to be propagated, including the schema name.

If the schema name is not specified, then it defaults to the schema name of the user.

destination

Destination dblink.

DISABLE_PROPAGATION_SCHEDULE Procedure

This procedure disables a propagation schedule.

Syntax

DBMS_AQADM.DISABLE_PROPAGATION_SCHEDULE ( 
   queue_name      IN     VARCHAR2, 
   destination     IN     VARCHAR2 DEFAULT NULL);

Parameters

Table 5-24 DISABLE_PROPAGATION_SCHEDULE Procedure Parameters

Parameter Description

queue_name

Name of the source queue whose messages are to be propagated, including the schema name.

If the schema name is not specified, then it defaults to the schema name of the user.

destination

Destination dblink.

MIGRATE_QUEUE_TABLE procedure

This procedure upgrades an 8.0-compatible queue table to an 8.1-compatible queue table, or downgrades an 8.1-compatible queue table to an 8.0-compatible queue table.

Syntax

DBMS_AQADM.MIGRATE_QUEUE_TABLE (
   queue_table   IN   VARCHAR2,
   compatible    IN   VARCHAR2);

Parameters

Table 5-25 MIGRATE_QUEUE_TABLE Procedure Parameters

Parameter	Description
queue_table	Specifies name of the queue table to be migrated.
compatible	Set this to '8.1' to upgrade an 8.0-compatible queue table, or set this to '8.0' to downgrade an 8.1-compatible queue table.

5DBMS_AQADM

Enumerated Constants

Table 5-1 Enumerated Types in the Administrative Interface

Summary of Subprograms

Table 5-2 DBMS_AQADM Package Subprograms

CREATE_QUEUE_TABLE procedure

Syntax

Parameters

Table 5-3 CREATE_QUEUE_TABLE Procedure Parameters

Usage Notes

ALTER_QUEUE_TABLE procedure

Syntax

Parameters

Table 5-4 ALTER_QUEUE_TABLE Procedure Parameters

DROP_QUEUE_TABLE procedure

Syntax

Parameters

Table 5-5 DROP_QUEUE_TABLE Procedure Parameters

CREATE_QUEUE procedure

Syntax

Parameters

Table 5-6 CREATE_QUEUE Procedure Parameters

Usage Notes

CREATE_NP_QUEUE procedure

Syntax

Parameters

Table 5-7 CREATE_NP_QUEUE Procedure Parameters

Usage Notes

ALTER_QUEUE procedure

Syntax

Parameters

Table 5-8 ALTER_QUEUE Procedure Parameters

DROP_QUEUE procedure

Syntax

Parameters

Table 5-9 DROP_QUEUE Procedure Parameters

START_QUEUE procedure

Syntax

Parameters

Table 5-10 START_QUEUE Procedure Parameters

STOP_QUEUE procedure

Syntax

Parameters

Table 5-11 STOP_QUEUE Procedure Parameters

GRANT_SYSTEM_PRIVILEGE procedure

Syntax

Parameters

Table 5-12 GRANT_SYSTEM_PRIVILEGE Procedure Parameters

REVOKE_SYSTEM_PRIVILEGE procedure

Syntax

Parameters

Table 5-13 REVOKE_SYSTEM_PRIVILEGE Procedure Parameters

GRANT_QUEUE_PRIVILEGE procedure

Syntax

Parameters

Table 5-14 GRANT_QUEUE_PRIVILEGE Procedure Parameters

REVOKE_QUEUE_PRIVILEGE procedure

Syntax

Parameters

Table 5-15 REVOKE_QUEUE_PRIVILEGE Procedure Parameters

ADD_SUBSCRIBER procedure

Syntax

Parameters

Table 5-16 ADD_SUBSCRIBER Procedure Parameters

Usage Notes

ALTER_SUBSCRIBER procedure

Syntax

Parameters

Table 5-17 ALTER_SUBSCRIBER Procedure Parameters

REMOVE_SUBSCRIBER procedure

Syntax

Parameters

Table 5-18 REMOVE_SUBSCRIBER Procedure Parameters

SCHEDULE_PROPAGATION procedure

Syntax

Parameters

Table 5-19 SCHEDULE_PROPAGATION Procedure Parameters

UNSCHEDULE_PROPAGATION procedure

Syntax

Parameters

5
DBMS_AQADM