4
Administrative Interface: Basic Operations

In this chapter we describe the administrative interface to Oracle Advanced Queuing in terms of use cases. That is, we discuss each operation (such as "Create a Queue Table") as a use case by that name. The table listing all the use cases is provided at the head of the chapter (see "Use Case Model: Administrative Interface -- Basic Operations").

A summary figure, "Use Case Diagram: Administrator's Interface -- Basic Operations", locates all the use cases in single drawing. If you are using the HTML version of this document, you can use this figure to navigate to the use case in which you are interested by clicking on the relevant use case title.

The individual use cases are themselves laid out as follows:

A figure that depicts the use case (see "Preface" for a description of how to interpret these diagrams).
A listing of the syntax.
Basic examples
Usage Notes, if any.

Use Case Model: Administrative Interface -- Basic Operations

Table 4-1 Use Case Model: Administrative Interface -- Basic Operations

Use Case
Create a Queue Table
Create a Queue Table [Set Storage Clause]
Alter a Queue Table
Drop a Queue Table
Create a Queue
Create a Non-Persistent Queue
Alter a Queue
Drop a Queue
Start a Queue
Stop a Queue
Grant System Privilege
Revoke System Privilege
Grant Queue Privilege
Revoke Queue Privilege
Add a Subscriber
Alter a Subscriber
Remove a Subscriber
Schedule a Queue Propagation
Unschedule a Queue Propagation
Verify a Queue Type
Alter a Propagation Schedule
Enable a Propagation Schedule
Disable a Propagation Schedule

Figure 4-1 Use Case Diagram: Administrator's Interface -- Basic Operations

Create a Queue Table

Figure 4-2 Use Case Diagram: Create a Queue Table

To refer to the table of all basic operations having to do with the Administrative Interface see:

"Use Case Model: Administrative Interface -- Basic Operations"

Purpose:

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

The 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) for the queue monitor operations called aq$_<queue_table_name>_t.
An index or an index organized table (IOT) in the case of multiple consumer queues for dequeue operations called aq$_<queue_table_name>_i.

For 8.1-compatible multiconsumer queue tables the following additional objects 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 (IOT) 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 '8.0');

Usage:

Table 4-2 DBMS_AQADM.CREATE_QUEUE_TABLE

Parameter Description

Parameter	Description
`queue_table` `(IN VARCHAR2)`	specifies the name of a queue table to be created.
`queue_payload_type` `(IN VARCHAR2)`	specifies the type of the user data stored. Please see section entitled "Type name" for valid values for this parameter.
`storage_clause` `(IN VARCHAR2)`	specifies the storage parameter. The storage parameter will be 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 tablespace is not specified in the `storage_clause` parameter, the queue table and all its related objects are created in the default user tablespace. If a tablespace is specified in the storage_clause parameter, the queue table and all its related objects are created in the tablespace specified in the storage clause. Please refer to the SQL reference guide for the usage of these parameters.
`sort_list` `(IN VARCHAR2)`	specifies 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. Once 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 once the queue table has been created. If no sort list is specified all the queues in this queue table will be 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` `(IN BOOLEAN)`	`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` `(IN BINARY_INTEGER)`	specifies the 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` `(IN VARCHAR2)`	specifies the user-specified description of the queue table. This user comment will be added to the queue catalog.
`auto_commit` `(IN BOOLEAN)`	`TRUE`: causes the current transaction, if any, to commit before the `CREATE_QUEUE_TABL`E operation is carried out. The `CREATE_QUEUE_TABL`E operation becomes persistent when the call returns. This is the default. `FALSE`: The operation is part of the current transaction and will become persistent only when the caller issues a commit. Caution: This parameter has been deprecated.
`primary_instance (IN BINARY_INTEGER)`	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 for primary instance is `0`, which means queue monitor scheduling and propagation will be done in any available instance.
`secondary_instance (IN BINARY_INTEGER)`	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 (VARCHAR2)`	specifies 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'.

queue_table

(IN VARCHAR2)

specifies the name of a queue table to be created.

queue_payload_type

(IN VARCHAR2)

specifies the type of the user data stored. Please see section entitled "Type name" for valid values for this parameter.

storage_clause

(IN VARCHAR2)

specifies the storage parameter. The storage parameter will be 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 tablespace is not specified in the storage_clause parameter, the queue table and all its related objects are created in the default user tablespace. If a tablespace is specified in the storage_clause parameter, the queue table and all its related objects are created in the tablespace specified in the storage clause.

Please refer to the SQL reference guide for the usage of these parameters.

sort_list

(IN VARCHAR2)

specifies 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.

Once 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 once the queue table has been created.

If no sort list is specified all the queues in this queue table will be 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

(IN BOOLEAN)

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

(IN BINARY_INTEGER)

specifies the 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

(IN VARCHAR2)

specifies the user-specified description of the queue table. This user comment will be added to the queue catalog.

auto_commit

(IN BOOLEAN)

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 will become persistent only when the caller issues a commit.

Caution: This parameter has been deprecated.

primary_instance (IN BINARY_INTEGER)

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 for primary instance is 0, which means queue monitor scheduling and propagation will be done in any available instance.

secondary_instance (IN BINARY_INTEGER)

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 (VARCHAR2)

specifies 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 or BFILE objects are valid attributes for an AQ object type load. However, only CLOB and BLOB can be propagated using AQ propagation in Oracle8i release 8.1.x.
You can specify and modify the primary_instance and secondary_instance only when the database is in 8.1-compatible mode.
You cannot specify a secondary instance unless there is a primary instance.

Example: Create a Queue Table Using PL/SQL (DBMS_AQADM Package)

Note:
You may need to set up the following data structures for certain examples to work:
CONNECT system/manager; DROP USER aqadm CASCADE; CREATE USER aqadm IDENTIFIED BY aqadm; GRANT CONNECT, RESOURCE TO aqadm; GRANT EXECUTE ON DBMS_AQADM TO aqadm; GRANT Aq_administrator_role TO aqadm; DROP USER aq CASCADE; CREATE USER aq IDENTIFIED BY aq; GRANT CONNECT, RESOURCE TO aq; GRANT EXECUTE ON dbms_aq TO aq;

Create queue table for queues containing messages of object type

CREATE type aq.Message_typ as object (
   Subject                VARCHAR2(30),
   Text                   VARCHAR2(80));   

/* Note: if you do not stipulate a schema, you default to the user's schema. */
EXECUTE dbms_aqadm.create_queue_table (
   Queue_table            => 'aq.ObjMsgs_qtab',
   Queue_payload_type     => 'aq.Message_typ');

Create queue table for queues containing messages of RAW type

EXECUTE dbms_aqadm.create_queue_table ( 
   Queue_table            => 'aq.RawMsgs_qtab', 
   Queue_payload_type     => 'RAW');

Create a queue table for prioritized messages

EXECUTE dbms_aqadm.create_queue_table (
   Queue_table            => 'aq.PriorityMsgs_qtab', 
   Sort_list              => 'PRIORITY,ENQ_TIME', 
   Queue_payload_type     => 'aq.Message_typ');

Create a queue table for multiple consumers

EXECUTE dbms_aqadm.create_queue_table (
   Queue_table            => 'aq.MultiConsumerMsgs_qtab',
   Multiple_consumers     => TRUE, 
   Queue_payload_type     => 'aq.Message_typ');

Create a queue table for multiple consumers compatible with 8.1

EXECUTE dbms_aqadm.create_queue_table (
   Queue_table            => 'aq.Multiconsumermsgs8_1qtab',
   Multiple_consumers     =>  TRUE,   
   Compatible             => '8.1', 
   Queue_payload_type     => 'aq.Message_typ');

Create a queue table in a specified tablespace

EXECUTE dbms_aqadm.create_queue_table( 
        queue_table        => 'aq.aq_tbsMsg_qtab',  
        queue_payload_type => 'aq.Message_typ',  
        storage_clause     => 'tablespace aq_tbs');

Create a Queue Table [Set Storage Clause]

Figure 4-3 Use Case Diagram: Create a Queue Table [Set Storage Clause]

Alter a Queue Table

Figure 4-4 Use Case Diagram: Alter a Queue Table

To refer to the table of all basic operations having to do with the Administrative Interface see:

"Use Case Model: Administrative Interface -- Basic Operations"

Purpose:

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

Usage:

Table 4-3 DBMS_AQADM.ALTER_QUEUE_TABLE

Parameter	Description
`queue_table` `(IN VARCHAR2)`	specifies the name of a queue table to be altered.
`comment` `(IN VARCHAR2)`	modifies the user-specified description of the queue table. This user comment will be added to the queue catalog. The default value is `NULL` which means that the value will not be changed.
`primary_instance (IN BINARY_INTEGER)`	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 (IN BINARY_INTEGER)`	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.

Example: Alter a Queue Table Using PL/SQL (DBMS_AQADM Package)

/* Altering the table to change the primary, secondary instances for queue owner 
   (only applicable for OPS environments).The primary instance is the instance 
   number of the primary owner of the queue table. The secondary instance is the
   instance number of the secondary owner of the queue table. */
 EXECUTE dbms_aqadm.alter_queue_table ( 
   Queue_table          => 'aq.ObjMsgs_qtab', 
   Primary_instance     => 3, 
   Secondary_instance   => 2); 
 
/* Altering the table to change the comment for a queue table: */ 
EXECUTE dbms_aqadm.alter_queue_table ( 
   Queue_table          => 'aq.ObjMsgs_qtab', 
   Comment              => 'revised usage for queue table');

Usage Notes

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.

Drop a Queue Table

Figure 4-5 Use Case Diagram: Drop a Queue Table

To refer to the table of all basic operations having to do with the Administrative Interface see:

"Use Case Model: Administrative Interface -- Basic Operations"

Purpose:

Drop an existing queue table. Note that you must stop and drop all the queues in a queue tables 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);

Usage:

Table 4-4 DBMS_AQADM.DROP_QUEUE_TABLE

Parameter Description

Parameter	Description
`queue_table` `(IN VARCHAR2)`	specifies the name of a queue table to be dropped.
`force` `(IN BOOLEAN)`	`FALSE`: The operation will 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` `(IN BOOLEAN)`	`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 will become persistent only when the caller issues a commit. Caution: This parameter has been deprecated.

queue_table

(IN VARCHAR2)

specifies the name of a queue table to be dropped.

force

(IN BOOLEAN)

FALSE: The operation will 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

(IN BOOLEAN)

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 will become persistent only when the caller issues a commit.

Caution: This parameter has been deprecated.

Caution:
You may need to set up or drop data structures for certain examples to work:

Example: Drop a Queue Table Using PL/SQL (DBMS_AQADM Package)

/* Drop the queue table (for which all queues have been previously dropped by
   the user) */
EXECUTE dbms_aqadm.drop_queue_table ( 
   queue_table        => 'aq.Objmsgs_qtab');

Caution:
You may need to set up or drop data structures for certain examples to work:

/* Drop the queue table and force all queues to be stopped and dropped by the
   system */ 
EXECUTE dbms_aqadm.drop_queue_table ( 
   queue_table        => 'aq.Objmsgs_qtab', 
   force              => TRUE);

Create a Queue

Figure 4-6 Use Case Diagram: Create a Queue

To refer to the table of all basic operations having to do with the Administrative Interface see:

"Use Case Model: Administrative Interface -- Basic Operations"

Purpose:

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

Usage:

Table 4-5 DBMS_AQADM.CREATE_QUEUE

Parameter Description

Parameter	Description
`queue_name` `(IN VARCHAR2)`	specifies the 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` `(IN VARCHAR2)`	specifies the name of the queue table that will contain the queue.
`queue_type` `(IN BINARY_INTEGER)`	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` `(IN NUMBER)`	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`. The default is `NULL` but is set internally to 5. 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` `(IN NUMBER)`	specifies the 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 will have no effect if `max_retries` is set to 0. 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` `(IN NUMBER)`	specifies the number of seconds for which a message will be retained in the queue table after being dequeued from the queue. `INFINITE`: Message will be retained forever. number: Number of seconds for which to retain the messages. The default is 0, i.e. no retention.
`dependency_tracking` `(IN BOOLEAN)`	Reserved for future use. `FALSE`: This is the default. `TRUE`: Not permitted in this release.
`comment` `(IN VARCHAR2)`	User-specified description of the queue. This user comment will be added to the queue catalog.
`auto_commit` `(IN BOOLEAN)`	`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 will become persistent only when the caller issues a commit. Caution: This parameter has been deprecated.

queue_name

(IN VARCHAR2)

specifies the 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

(IN VARCHAR2)

specifies the name of the queue table that will contain the queue.

queue_type

(IN BINARY_INTEGER)

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

(IN NUMBER)

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. The default is NULL but is set internally to 5. 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

(IN NUMBER)

specifies the 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 will have no effect if max_retries is set to 0. 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

(IN NUMBER)

specifies the number of seconds for which a message will be retained in the queue table after being dequeued from the queue.

INFINITE: Message will be retained forever.

number: Number of seconds for which to retain the messages. The default is 0, i.e. no retention.

dependency_tracking

(IN BOOLEAN)

Reserved for future use.

FALSE: This is the default.

TRUE: Not permitted in this release.

comment

(IN VARCHAR2)

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

auto_commit

(IN BOOLEAN)

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 will become persistent only when the caller issues a commit.

Caution: This parameter has been deprecated.

Usage Notes

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.

Example: Create a Queue Using PL/SQL (DBMS_AQADM)

Create a queue within a queue table for messages of object type

/* Create a message type: */
CREATE type aq.Message_typ as object (
   Subject     VARCHAR2(30),
   Text        VARCHAR2(80));   

/* Create a object type queue table and queue: */
EXECUTE dbms_aqadm.create_queue_table (
   Queue_table        => 'aq.ObjMsgs_qtab',
   Queue_payload_type => 'aq.Message_typ');

EXECUTE dbms_aqadm.create_queue (
   Queue_name         =>  'msg_queue',
   Queue_table        => 'aq.ObjMsgs_qtab');

Create a queue within a queue table for messages of RAW type

/* Create a RAW type queue table and queue: */
EXECUTE dbms_aqadm.create_queue_table ( 
   Queue_table          => 'aq.RawMsgs_qtab', 
   Queue_payload_type   => 'RAW'); 
  
/* Create queue: */
EXECUTE dbms_aqadm.create_queue ( 
   Queue_name          => 'raw_msg_queue', 
   Queue_table         => 'aq.RawMsgs_qtab');

Create a prioritized message queue table and queue

Caution:
You may need to set up or drop data structures for certain examples to work:

/* Create a queue table for priortized messages: */
EXECUTE dbms_aqadm.create_queue_table (
   Queue_table        => 'aq.PriorityMsgs_qtab', 
   Sort_list          => 'PRIORITY,ENQ_TIME', 
   Queue_payload_type => 'aq.Message_typ');
/* Create queue: */
EXECUTE dbms_aqadm.create_queue (
   Queue_name         => 'priority_msg_queue', 
   Queue_table        => 'aq.PriorityMsgs_qtab');

Create a queue table and queue meant for multiple consumers

Caution:
You may need to set up or drop data structures for certain examples to work:

/* Create a queue table for multi-consumers: */
EXECUTE dbms_aqadm.create_queue_table (
   queue_table        => 'aq.MultiConsumerMsgs_qtab',
   Multiple_consumers => TRUE, 
   Queue_payload_type => 'aq.Message_typ');

/* Create queue: */
EXECUTE dbms_aqadm.create_queue (
   Queue_name         => 'MultiConsumerMsg_queue',
   Queue_table        => 'aq.MultiConsumerMsgs_qtab');

Create a queue table and queue to demonstrate propagation

/* Create queue: */
EXECUTE dbms_aqadm.create_queue (
   Queue_name        => 'AnotherMsg_queue',
   queue_table       => 'aq.MultiConsumerMsgs_qtab');

Create a queue table and queue for multiple consumers compatible with 8.1

/* Create a queue table for multi-consumers compatible with Release 8.1: */
EXECUTE dbms_aqadm.create_queue_table ( 
   Queue_table        => 'aq.MultiConsumerMsgs81_qtab', 
   Multiple_consumers => TRUE, 
   Compatible         => '8.1', 
   Queue_payload_type => 'aq.Message_typ'); 
 
EXECUTE dbms_aqadm.create_queue ( 
   Queue_name         => 'MultiConsumerMsg81_queue', 
   Queue_table        => 'aq.MultiConsumerMsgs81_qtab');

Create a Non-Persistent Queue

Figure 4-7 Use Case Diagram: Create a Non-Persistent Queue

To refer to the table of all basic operations having to do with the Administrative Interface see:

"Use Case Model: Administrative Interface -- Basic Operations"

Purpose

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

Usage:

Table 4-6 DBMS_AQADM.CREATE_NP_QUEUE

Parameter Description

Parameter	Description
`queue_name` `(IN VARCHAR2)`	specifies the 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` `(IN BOOLEAN)`	`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 the `multi_consumers` parameter is distinguished at the queue level because a non-persistent queue does not inherit this characteristic from any user-created queue table
`comment` `(IN VARCHAR2)`	User-specified description of the queue. This user comment will be added to the queue catalog.

queue_name

(IN VARCHAR2)

specifies the 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

(IN BOOLEAN)

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 the multi_consumers parameter is distinguished at the queue level because a non-persistent queue does not inherit this characteristic from any user-created queue table

comment

(IN VARCHAR2)

User-specified description of the queue. This user comment will be 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, 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 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 (see Register for Notification ).
You cannot invoke the listen call on a non-persistent queue (see Listen to One (Many) Queue(s)).
You cannot have rule based subscriptions on non-persistent queues.

Example: Create a Non-Persistent Queue Using PL/SQL (DBMS_AQADM)

/* Create a non-persistent single-consumer queue (Note: this is not preceded by
   creation of a queue table) */ 
EXECUTE dbms_aqadm.create_np_queue( 
   Queue_name           => 'Singleconsumersmsg_npque',  
   Multiple_consumers   => FALSE);  
 
/* Create a non-persistent multi-consumer queue (Note: this is not preceded by
   creation of a queue table) */ 
EXECUTE dbms_aqadm.create_np_queue( 
   Queue_name           => 'Multiconsumersmsg_npque',  
   Multiple_consumers   => TRUE);

Alter a Queue

Figure 4-8 Use Case Diagram: Alter a Queue

To refer to the table of all basic operations having to do with the Administrative Interface see:

"Use Case Model: Administrative Interface -- Basic Operations"

Purpose:

Alter existing properties of a queue. Only max_retries, retry_delay, and retention_time can be altered.

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

Usage:



Table 4-7  DBMS_AQADM.ALTER_QUEUE
 





Parameter 


Description 





queue_name


(IN VARCHAR2)  



specifies the name of the queue that is to be altered.  




max_retries


(IN NUMBER)  



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, no further retries will be attempted. The default is NULL which means that existing value will not be changed. 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


(IN NUMBER)  



specifies the delay time in seconds before this message is scheduled for processing again after an application rollback. The default is NULL which means that existing value will not be changed. 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


(IN NUMBER)  



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




auto_commit


(IN BOOLEAN)  



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 will become persistent only when the caller issues a commit. 


Caution: This parameter has been deprecated.  




comment


(IN VARCHAR2)  



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

Parameter	Description
`queue_name` `(IN VARCHAR2)`	specifies the name of the queue that is to be altered.
`max_retries` `(IN NUMBER)`	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, no further retries will be attempted. The default is `NULL` which means that existing value will not be changed. 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` `(IN NUMBER)`	specifies the delay time in seconds before this message is scheduled for processing again after an application rollback. The default is `NULL` which means that existing value will not be changed. 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` `(IN NUMBER)`	specifies the retention time in seconds for which a message will be retained in the queue table after being dequeued. The default is `NULL` which means that the value will not be altered.
`auto_commit` `(IN BOOLEAN)`	`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 will become persistent only when the caller issues a commit. Caution: This parameter has been deprecated.
`comment` `(IN VARCHAR2)`	User-specified description of the queue. This user comment will be added to the queue catalog. The default value is `NULL` which means that the value will not be changed.

Usage Notes

To view retained messages, you can either dequeue by message ID or use SQL.
You can only alter the comment field of a non-persistent queues.
Note that max_retries, retention, retry_delay and retry_count are not supported for non-persistent queues.

Example: Alter a Queue Using PL/SQL (DBMS_AQADM)

/* Alter queue to change retention time, saving messages for 1 day after
   dequeueing:  */
EXECUTE dbms_aqadm.alter_queue ( 
   queue_name        => 'aq.Anothermsg_queue', 
   retention_time    => 86400);

Drop a Queue

Figure 4-9 Use Case Diagram: Drop a Queue

To refer to the table of all basic operations having to do with the Administrative Interface see:

"Use Case Model: Administrative Interface -- Basic Operations"

Purpose:

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

Usage:

Table 4-8 DBMS_AQADM.DROP_QUEUE

Parameter Description

queue_name

(IN VARCHAR2)

specifies the name of the queue that is to be dropped.

auto_commit

(IN BOOLEAN)

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 will become persistent only when the caller issues a commit.

Caution: This parameter has been deprecated.

Example: Drop a Queue Using PL/SQL (DBMS_AQADM)

Drop a Standard Queue

/* Stop the queue preparatory to dropping it (a queue may be dropped only after
   it has been succesfully stopped for enqueing and dequeing): */ 
EXECUTE dbms_aqadm.stop_queue (  
   Queue_name        => 'aq.Msg_queue');   
 
/* Drop queue: */  
EXECUTE dbms_aqadm.drop_queue (  
   Queue_name         => 'aq.Msg_queue');

Drop a Non-Persistent Queue

EXECUTE DBMS_AQADM.DROP_QUEUE( queue_name => 'Nonpersistent_
singleconsumerq1');
EXECUTE DBMS_AQADM.DROP_QUEUE( queue_name => 'Nonpersistent_multiconsumerq1');

Start a Queue

Figure 4-10 Use Case Diagram: Start a Queue

To refer to the table of all basic operations having to do with the Administrative Interface see:

"Use Case Model: Administrative Interface -- Basic Operations"

Purpose:

Enables the specified queue for enqueuing and/or dequeueing.

Syntax:

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

Usage:

Table 4-9 DBMS_AQADM.START_QUEUE

Parameter Description

Parameter	Description
`queue_name` `(IN VARCHAR2)`	specifies the name of the queue to be enabled.
`enqueue` `(IN BOOLEAN)`	specifies whether `ENQUEUE` should be enabled on this queue. `TRUE`: Enable `ENQUEUE`. This is the default. `FALSE`: Do not alter the current setting.
`dequeue` `(IN BOOLEAN)`	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

(IN VARCHAR2)

specifies the name of the queue to be enabled.

enqueue

(IN BOOLEAN)

specifies whether ENQUEUE should be enabled on this queue.

TRUE: Enable ENQUEUE. This is the default.

FALSE: Do not alter the current setting.

dequeue

(IN BOOLEAN)

specifies whether DEQUEUE should be enabled on this queue.

TRUE: Enable DEQUEUE. This is the default.

FALSE: Do not alter the current setting.

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.

Example: Start a Queue using PL/SQL (DBMS_AQADM Package)

/* Start a queue and enable both enqueue and dequeue: */
EXECUTE dbms_aqadm.start_queue (
   queue_name         => 'Msg_queue');
    

    /* Start a previously stopped queue for dequeue only */ 
EXECUTE dbms_aqadm.start_queue ( 
   queue_name         => 'aq.msg_queue', 
   dequeue            => TRUE, 
   enqueue            => FALSE);

Stop a Queue

Figure 4-11 Use Case Diagram: Stop a Queue

To refer to the table of all basic operations having to do with the Administrative Interface see:

"Use Case Model: Administrative Interface -- Basic Operations"

Purpose:

Disables enqueuing and/or dequeuing on the specified queue.

Syntax:

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

Usage:

Table 4-10 DBMS_AQADM.STOP_QUEUE

Parameter Description

Parameter	Description
`queue_name` `(IN VARCHAR2)`	specifies the name of the queue to be disabled.
`enqueue` `(IN BOOLEAN)`	specifies whether `ENQUEUE` should be disabled on this queue. `TRUE`: Disable `ENQUEUE`. This is the default. `FALSE`: Do not alter the current setting.
`dequeue` `(IN BOOLEAN)`	specifies whether `DEQUEUE` should be disabled on this queue. `TRUE`: Disable `DEQUEUE`. This is the default. `FALSE`: Do not alter the current setting.
`wait` `(IN BOOLEAN)`	The wait parameter allows you to specify 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.

queue_name

(IN VARCHAR2)

specifies the name of the queue to be disabled.

enqueue

(IN BOOLEAN)

specifies whether ENQUEUE should be disabled on this queue.

TRUE: Disable ENQUEUE. This is the default.

FALSE: Do not alter the current setting.

dequeue

(IN BOOLEAN)

specifies whether DEQUEUE should be disabled on this queue.

TRUE: Disable DEQUEUE. This is the default.

FALSE: Do not alter the current setting.

wait

(IN BOOLEAN)

The wait parameter allows you to specify 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.

Usage Notes

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.

Example: Stop a Queue Using PL/SQL (DBMS_AQADM)

/* Stop the queue: */ 
EXECUTE dbms_aqadm.stop_queue (  
   queue_name        => 'aq.Msg_queue');

Grant System Privilege

Figure 4-12 Use Case Diagram: Grant System Privilege

To refer to the table of all basic operations having to do with the Administrative Interface see:

"Use Case Model: Administrative Interface -- Basic Operations"

Purpose:

To grant AQ system privileges to users and roles. The privileges are ENQUEUE_ANY, DEQUEUE_ANY, 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);

Usage:

Table 4-11 DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE

Parameter Description

privilege

(IN VARCHAR2)

specifies the AQ system privilege to grant.

Options are: ENQUEUE_ANY, DEQUEUE_ANY, 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 execute DBMS_AQADM calls on any schemas in the database.

grantee

(IN VARCHAR2)

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

admin_option

(IN BOOLEAN)

specifies if the system privilege is granted with the ADMIN option or not. If the privilege is granted with the ADMIN option, the grantee is allowed to use this procedure to grant the system privilege to other users or roles.

Default:FALSE

Example: Grant System Privilege Using PL/SQL (DBMS_AQADM)

/* User AQADM grants the rights to enqueue and dequeue to ANY queues: */

Note:
You may need to set up the following data structures for certain examples to work:
CONNECT system/manager; CREATE USER aqadm IDENTIFIED BY aqadm; GRANT CONNECT, RESOURCE TO aqadm; GRANT EXECUTE ON DBMS_AQADM TO aqadm; GRANT Aq_administrator_role TO aqadm;

CONNECT aqadm/aqadm; 
EXECUTE DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE(
   privilege          =>    'ENQUEUE_ANY', 
   grantee            =>    'Jones', 
   admin_option       =>     FALSE);
EXECUTE DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE(
   privilege          =>     'DEQUEUE_ANY', 
   grantee            =>     'Jones', 
   admin_option       =>      FALSE);

Revoke System Privilege

Figure 4-13 Use Case Diagram: Revoke System Privilege

To refer to the table of all basic operations having to do with the Administrative Interface see:

"Use Case Model: Administrative Interface -- Basic Operations"

Purpose:

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

Usage:

Table 4-12 DBMS_AQADM.REVOKE_SYSTEM_PRIVILEGE

Parameter Description

privilege

(IN VARCHAR2)

specifies the AQ system privilege to revoke.

Options are: ENQUEUE_ANY, DEQUEUE_ANY, MANAGE_ANY.

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

grantee

(IN VARCHAR2)

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

Example: Revoke System Privilege Using PL/SQL (DBMS_AQADM)

/* To revoke the DEQUEUE_ANY system privilege from Jones. */
CONNECT system/manager;
   execute DBMS_AQADM.REVOKE_SYSTEM_PRIVILEGE(privilege=>'DEQUEUE_ANY', 
                             grantee=>'Jones');

Grant Queue Privilege

Figure 4-14 Use Case Diagram: Grant Queue Privilege

To refer to the table of all basic operations having to do with the Administrative Interface see:

"Use Case Model: Administrative Interface -- Basic Operations"

Purpose:

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

Usage:

Table 4-13 DBMS_AQADM.GRANT_QUEUE_PRIVILEGE

Parameter Description

privilege

(IN VARCHAR2)

specifies the AQ queue privilege to grant.

Options are: ENQUEUE, DEQUEUE and ALL. ALL means both ENQUEUE and DEQUEUE.

queue_name

(IN VARCHAR2)

specifies the name of the queue.

grantee

(IN VARCHAR2)

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

grant_option

(IN BOOLEAN)

specifies if the access privilege is granted with the GRANT option or not. If the privilege is granted with the GRANT option, 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.

Default:FALSE

Example: Grant Queue Privilege Using PL/SQL (DBMS_AQADM)

/* User grants the access right for both enqueue and dequeue rights using
   DBMS_AQADM.GRANT. */ 
EXECUTE DBMS_AQADM.GRANT_QUEUE_PRIVILEGE (
   privilege     =>     'ALL', 
   queue_name    =>     'aq.multiconsumermsg81_queue',
   grantee       =>     'Jones', 
   grant_option  =>      TRUE);

Revoke Queue Privilege

Figure 4-15 Use Case Diagram: Revoke Queue Privilege

To refer to the table of all basic operations having to do with the Administrative Interface see:

"Use Case Model: Administrative Interface -- Basic Operations"

Purpose:

To revoke privileges on a queue from users and roles. The privileges are ENQUEUE or DEQUEUE.

Syntax:

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

Usage:

Table 4-14 DBMS_AQADM.REVOKE_QUEUE_PRIVILEGE

Parameter Description

privilege

(IN VARCHAR2)

specifies the AQ queue privilege to revoke.

Options are: ENQUEUE, DEQUEUE and ALL. ALL means both ENQUEUE and DEQUEUE.

queue_name

(IN VARCHAR2)

specifies the name of the queue.

grantee

(IN VARCHAR2)

specifies the 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, the propagated privilege is also revoked.

Usage Notes

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.

Example: Revoke Queue Privilege Using PL/SQL (DBMS_AQADM)

/* User can revoke the dequeue right of a grantee on a specific queue 
   leaving the grantee with only the enqueue right: */ 
CONNECT scott/tiger; 
EXECUTE DBMS_AQADM.REVOKE_QUEUE_PRIVILEGE(
   privilege     =>     'DEQUEUE', 
   queue_name    =>     'scott.ScottMsgs_queue', 
   grantee       =>     'Jones');

Add a Subscriber

Figure 4-16 Use Case Diagram: Add a Subscriber

To refer to the table of all basic operations having to do with the Administrative Interface see:

"Use Case Model: Administrative Interface -- Basic Operations"

Purpose:

Adds a default subscriber to a queue.

Syntax:

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

Usage:

Table 4-15 DBMS_AQADM.ADD_SUBSCRIBER

Parameter Description

queue_name

(IN VARCHAR2)

specifies the name of the queue.

subscriber

(IN aq$_agent)

The agent on whose behalf the subscription is being defined (see definition of "Agent").

rule

(IN VARCHAR2)

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 Note:

A program can enqueue messages to a specific list of recipients or to the default list of subscribers. This operation will only succeed 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.
Note that any string within the rule has to be quoted as shown below;
```
rule   => 'PRIORITY <= 3 AND CORRID =  ''FROM JAPAN'''
    
 
         
```
Note that these are all single quotation marks.

Example: Add Subscriber Using PL/SQL (DBMS_AQADM)

/* Anonymous PL/SQL block for adding a subscriber at a designated queue in a 
designated schema at a database link: */ 
DECLARE 
   subscriber          aq$_agent; 
BEGIN 
   subscriber := aq$_agent ('subscriber1', 'aq2.msg_queue2@london', null); 
   DBMS_AQADM.ADD_SUBSCRIBER(
     queue_name         => 'aq.multi_queue', 
      subscriber            =>  subscriber); 
 END; 

/* Add a subscriber with a rule: */ 
DECLARE 
   subscriber       aq$_agent; 
BEGIN 
   subscriber := aq$_agent('subscriber2', 'aq2.msg_queue2@london', null); 
   DBMS_AQADM.ADD_SUBSCRIBER(
     queue_name         =>  'aq.multi_queue', 
      subscriber            =>    subscriber, 
      rule                  =>    'priority < 2'); 
END;

Example: Add Rule-Based Subscriber Using PL/SQL (DBMS_AQADM)

DECLARE 
   subscriber          aq$_agent; 
BEGIN 
   subscriber :=  aq$_agent('East_Shipping','ES.ES_bookedorders_que',null); 
   DBMS_AQADM.ADD_SUBSCRIBER(
      queue_name           => 'OE.OE_bookedorders_que', 
      subscriber           => subscriber, 
      rule                 => 'tab.user_data.orderregion =  ''EASTERN'' OR 
                              (tab.user_data.ordertype =  ''RUSH'' AND 
                               tab.user_data.customer.country = ''USA'') '); 
END;

Alter a Subscriber

Figure 4-17 Use Case Diagram: Alter a Subscriber

To refer to the table of all basic operations having to do with the Administrative Interface see:

"Use Case Model: Administrative Interface -- Basic Operations"

Purpose:

Alter 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    aq$_agent
   rule           IN    VARCHAR2);

Usage:

Table 4-16 DBMS_AQADM.ALTER_SUBSCRIBER

Parameter Description

queue_name

(IN VARCHAR2)

specifies the name of the queue.

subscriber

(IN aq$_agent)

The agent on whose behalf the subscription is being altered (see definition of "Agent").

rule

(IN VARCHAR2)

A conditional expression based on the message properties, the message data properties and PL/SQL functions.The rule parameter cannot exceed 4000 characters.To eliminate the rule, set the rule parameter to NULL.

Example: Alter Subscriber Using PL/SQL (DBMS_AQADM)

Note:
You may need to set up the following data structures for certain examples to work:
EXECUTE DBMS_AQADM.CREATE_QUEUE_TABLE ( queue_table => 'aq.multi_qtab', multiple_consumers => TRUE, queue_payload_type => 'aq.message_typ', compatible => '8.1.5'); EXECUTE DBMS_AQADM.CREATE_QUEUE ( queue_name => 'multi_queue', queue_table => 'aq.multi_qtab');


/* Add a subscriber with a rule: */ 
DECLARE 
   subscriber       aq$_agent; 
BEGIN 
   subscriber := aq$_agent('SUBSCRIBER1', 'aq2.msg_queue2@london', null); 
   DBMS_AQADM.ADD_SUBSCRIBER(
      queue_name         =>    'aq.msg_queue', 
      subscriber         =>     subscriber, 
      rule               =>    'priority < 2'); 
END; 
/* Change rule for subscriber: */ 
DECLARE 
   subscriber          aq$_agent; 
BEGIN 
   subscriber := aq$_agent('SUBSCRIBER1', 'aq2.msg_queue2@london', null); 
   DBMS_AQADM.ALTER_SUBSCRIBER(
      queue_name         =>    'aq.msg_queue', 
      subscriber         =>     subscriber, 
      rule              =>     'priority = 1'); 
END;

Remove a Subscriber

Figure 4-18 Use Case Diagram: Remove a Subscriber

To refer to the table of all basic operations having to do with the Administrative Interface see:

"Use Case Model: Administrative Interface -- Basic Operations"

Purpose:

Remove a default subscriber from a queue.

Syntax:

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

Usage:

Table 4-17

Parameter Description

queue_name

(IN VARCHAR2)

specifies the name of the queue.

subscriber

(IN aq$_agent)

The agent who is being removed from the (see definition of "Agent").

Usage Notes

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.

Example: Remove Subscriber Using PL/SQL (DBMS_AQADM)

DECLARE
   subscriber       aq$_agent;
BEGIN
   subscriber := aq$_agent('subscriber1','aq2.msg_queue2', NULL);
   DBMS_AQADM.REMOVE_SUBSCRIBER(
      queue_name => 'aq.multi_queue',
      subscriber => subscriber);
END;

Schedule a Queue Propagation

Figure 4-19 Use Case Diagram: Schedule a Queue Propagation

To refer to the table of all basic operations having to do with the Administrative Interface see:

"Use Case Model: Administrative Interface -- Basic Operations"

Purpose:

Schedule propagation of messages from a queue to a destination identified by a specific dblink.

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

Usage:

Table 4-18 DBMS_AQADM.SCHEDULE_PROPAGATION

Parameter	Description
`queue_name` `(IN VARCHAR2)`	specifies the name of the source queue whose messages are to be propagated, including the schema name. If the schema name is not specified, it defaults to the schema name of the administrative user.
`destination` `(IN VARCHAR2)`	specifies the destination dblink. Messages in the source queue for recipients at this destination will be propagated. If it is `NULL`, the destination is the local database and messages will be 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 the default domain name is used.
`start_time` `(IN DATE)`	specifies the initial start time for the propagation window for messages from the source queue to the destination.
`duration` `(IN NUMBER)`	specifies the duration of the propagation window in seconds. A `NULL` value means the propagation window is forever or until the propagation is unscheduled.
`next_time` `(IN VARCHAR2)`	date function to compute the start of the next propagation window from the end of the current window. If this value is NULL, propagation will be 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` `(IN NUMBER)`	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, 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.

Usage Notes

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 the message will be propagated to all of them at the same time.

Example: Schedule a Propagation Using PL/SQL (DBMS_AQADM)

Note:
You may need to set up the following data structures for certain examples to work:
EXECUTE DBMS_AQADM.CREATE_QUEUE_TABLE ( queue_table => 'aq.objmsgs_qtab', queue_payload_type => 'aq.message_typ', multiple_consumers => TRUE); EXECUTE DBMS_AQADM.CREATE_QUEUE ( queue_name => 'aq.q1def', queue_table => 'aq.objmsgs_qtab');

Schedule a Propagation from a Queue to other Queues in the Same Database

/* Schedule propagation from queue aq.q1def to other queues in the same 
   database  */
EXECUTE DBMS_AQADM.SCHEDULE_PROPAGATION(
   Queue_name    =>    'aq.q1def');

Schedule a Propagation from a Queue to other Queues in Another Database

/* Schedule a propagation from queue aq.q1def to other queues in another 
   database */
EXECUTE DBMS_AQADM.SCHEDULE_PROPAGATION(
   Queue_name    =>    'aq.q1def', 
   Destination   =>    'another_db.world');

Unschedule a Queue Propagation

Figure 4-20 Use Case Diagram: Unschedule a Queue Propagation

To refer to the table of all basic operations having to do with the Administrative Interface see:

"Use Case Model: Administrative Interface -- Basic Operations"

Purpose:

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

Usage:

Table 4-19 DBMS_AQADM.UNSCHEDULE_PROPAGATION

Parameter Description

queue_name

(IN VARCHAR2)

specifies the name of the source queue whose messages are to be propagated, including the schema name. If the schema name is not specified, it defaults to the schema name of the administrative user.

destination

(IN VARCHAR2)

specifies the destination dblink. Messages in the source queue for recipients at this destination will be propagated. If it is NULL, the destination is the local database and messages will be 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 the default domain name is used.

Example: Unschedule a Propagation Using PL/SQL (DBMS_AQADM)

Unschedule Propagation from Queue To Other Queues in the Same Database

/* Unschedule propagation from queue aq.q1def to other queues in the same  
   database: */
EXECUTE DBMS_AQADM.UNSCHEDULE_PROPAGATION(queue_name => 'aq.q1def');

Unschedule Propagation from a Queue to other Queues in Another Database

/* Unschedule propagation from queue aq.q1def to other queues in another 
   database reached by the database link another_db.world */
EXECUTE DBMS_AQADM.UNSCHEDULE_PROPAGATION(
   Queue_name    =>   'aq.q1def', 
   Destination   =>   'another_db.world');

Verify a Queue Type

Figure 4-21 Use Case Diagram: Verify a Queue Type

To refer to the table of all basic operations having to do with the Administrative Interface see:

"Use Case Model: Administrative Interface -- Basic Operations"

Purpose:

Verify that the source and destination queues have identical types. The result of the verification is stored in aq$_Message_types tables, 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);

Usage:

Table 4-20 DBMS_AQADM.VERIFY_QUEUE_TYPES

Parameter	Description
`src_queue_name` `(IN VARCHAR2)`	specifies the name of the source queue whose messages are to be propagated, including the schema name. If the schema name is not specified, it defaults to the schema name of the user.
`dest_queue_name` `(IN VARCHAR2)`	specifies the name of the destination queue where messages are to be propagated, including the schema name. If the schema name is not specified, it defaults to the schema name of the user.
`destination` `(IN VARCHAR2)`	specifies the destination dblink. The destination queue is in the database that is specified by the dblink. If the destination is `NULL`, the destination queue is the same database as the source queue. The length of this field is currently limited to 128 bytes and if the name is not fully qualified the default domain name is used.
`rc` `(OUT BINARY_INTEGER)`	return code for the result of the procedure. If there is no error and if the source and destination queue types match the result is 1, if they do not match the result is 0. If an Oracle error is encountered it is returned in rc.

Example: Verify a Queue Type Using PL/SQL (DBMS_AQADM)

Note:
You may need to set up the following data structures for certain examples to work:
EXECUTE DBMS_AQADM.CREATE_QUEUE ( queue_name => 'aq.q2def', queue_table => 'aq.objmsgs_qtab');

/*  Verify if 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 */ 
DECLARE 
rc      BINARY_INTEGER; 
BEGIN 
/* Verify if the queues aq.q1def and aq.q2def in the local database 
   have the same payload type */ 
   DBMS_AQADM.VERIFY_QUEUE_TYPES(
      src_queue_name  => 'aq.q1def', 
      dest_queue_name => 'aq.q2def',
      rc              => rc); 
   DBMS_OUTPUT.PUT_LINE(rc);
END;

Alter a Propagation Schedule

Figure 4-22 Use Case Diagram: Alter a Propagation Schedule

To refer to the table of all basic operations having to do with the Administrative Interface see:

"Use Case Model: Administrative Interface -- Basic Operations"

Purpose:

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

Usage:

Table 4-21 DBMS_AQADM.ALTER_PROPAGATION_SCHEDULE

Parameter	Description
`queue_name` `(IN VARCHAR2)`	specifies the name of the source queue whose messages are to be propagated, including the schema name. If the schema name is not specified, it defaults to the schema name of the user.
`destination` `(IN VARCHAR2)`	specifies the destination dblink. The destination queue is in the database that is specified by the dblink. If the destination is `NULL`, the destination queue is the same database as the source queue. The length of this field is currently limited to 128 bytes and if the name is not fully qualified the default domain name is used.
`duration` `(IN NUMBER)`	specifies the duration of the propagation window in seconds. A `NULL` value means the propagation window is forever or until the propagation is unscheduled.
`next_time` `(IN VARCHAR2)`	the date function to compute the start of the next propagation window from the end of the current window. If this value is NULL, propagation will be 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` `(IN NUMBER)`	the 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, 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, 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.

Example: Alter a Propagation Schedule Using PL/SQL (DBMS_AQADM)

Alter a Schedule from a Queue to Other Queues in the Same Database

/* Alter schedule from queue aq.q1def to other queues in the same database */
EXECUTE DBMS_AQADM.ALTER_PROPAGATION_SCHEDULE(
   Queue_name    =>    'aq.q1def', 
   Duration      =>    '2000', 
   Next_time     =>    'SYSDATE + 3600/86400',
   Latency       =>    '32');

Alter a Schedule from a Queue to Other Queues in Another Database

/* Alter schedule from queue aq.q1def to other queues in another database 
reached by the database link another_db.world */
EXECUTE DBMS_AQADM.ALTER_PROPAGATION_SCHEDULE(
   Queue_name    =>    'aq.q1def', 
   Destination   =>    'another_db.world', 
   Duration      =>    '2000', 
   Next_time     =>    'SYSDATE + 3600/86400',
   Latency       =>    '32');

Enable a Propagation Schedule

Figure 4-23 Use Case Diagram: Enable a Propagation Schedule

To refer to the table of all basic operations having to do with the Administrative Interface see:

"Use Case Model: Administrative Interface -- Basic Operations"

Purpose:

To enable a previously disabled propagation schedule.

Syntax:

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

Usage:

Table 4-22 DBMS_AQADM.ENABLE_ PROPAGATION_SCHEDULE

Parameter Description

queue_name

(IN VARCHAR2)

specifies the name of the source queue whose messages are to be propagated, including the schema name. If the schema name is not specified, it defaults to the schema name of the user.

destination

(IN VARCHAR2)

specifies the destination dblink. The destination queue is in the database that is specified by the dblink. If the destination is NULL, the destination queue is the same database as the source queue. The length of this field is currently limited to 128 bytes and if the name is not fully qualified the default domain name is used.

Example: Enable a Propagation Using PL/SQL (DBMS_AQADM)

Enable Propagation from a Queue to Other Queues in the Same Database

/* Enable propagation from queue aq.q1def to other queues in the same 
   database */
EXECUTE DBMS_AQADM.ENABLE_PROPAGATION_SCHEDULE(
   Queue_name   =>   'aq.q1def');

Enable Propagation from a Queue to Queues in Another Database

/* Enable propagation from queue aq.q1def to other queues in another 
  database reached by the database link another_db.world */
EXECUTE DBMS_AQADM.ENABLE_PROPAGATION_SCHEDULE(
   Queue_name    =>    'aq.q1def', 
   Destination   =>    'another_db.world');

Disable a Propagation Schedule

Figure 4-24 Use Case Diagram: Disable a Propagation Schedule

To refer to the table of all basic operations having to do with the Administrative Interface see:

"Use Case Model: Administrative Interface -- Basic Operations"

Purpose:

To disable a previously disabled propagation schedule.

Syntax:

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

Usage:

Table 4-23 DBMS_AQADM.DISABLE_PROPAGATION_SCHEDULE

Parameter Description

queue_name

(IN VARCHAR2)

specifies the name of the source queue whose messages are to be propagated, including the schema name. If the schema name is not specified, it defaults to the schema name of the user.

destination

(IN VARCHAR2)

specifies the destination dblink; the destination queues are in the database that is specified by the dblink. If the destination is NULL, the destination queue is the same database as the source queue. The length of this field is currently limited to 128 bytes and if the name is not fully qualified the default domain name is used.

Example: Disable a Propagation Using PL/SQL (DBMS_AQADM)

Disable Propagation from a Queue to Other Queues in the Same Database

/* Disable a propagation from queue aq.q1def to other queues in the same 
   database */
EXECUTE DBMS_AQADM.DISABLE_PROPAGATION_SCHEDULE(
   Queue_name   =>   'aq.q1def');

Disable Propagation from a Queue to Queues in Another Database

/* Disable a propagation from queue aq.q1def to other queues in another 
  database reached by the database link another_db.world */
EXECUTE DBMS_AQADM.DISABLE_PROPAGATION_SCHEDULE(
   Queue_name    =>    'aq.q1def', 
   Destination   =>    'another_db.world');

Usage Notes

This section describes some troubleshooting tips to diagnose problems with message propagation.

Message history

AQ updates the message history when a message has been successfully propagated to a destination. The message history is stored as a collection in the queue table. An administrator can execute a SQL query to determine if a message has been propagated. For example, to check if a message with msgid

105E7A2EBFF11348E03400400B40F149'

in queue table aqadmn.queue_tab has been propagated to destination 'boston', the following query can be executed:


SELECT consumer, transaction_id, deq_time, deq_user, propagated_msgid 
   FROM THE(select cast(history as aq$_dequeue_history_t)  
   FROM adadmn.queue_tab 
      WHERE msgid='105E7A2EBFF11348E03400400B40F149') 
      WHERE consumer LIKE '%BOSTON%';

A non-NULL transaction_id indicates that the message was successfully propagated. Further, the deq_time indicates the time of propagation, the deq_user indicates the userid used for propagation, and the propagated_msgid indicates the msgid of the message that was enqueued at the destination. If the message with the msgid cannot be found in the queue table, an administrator can check the exception queue (if the exception queue is in a different queue table) for the message history.

Propagation Schedules

To verify that propagation is working successfully, examine the schedule information using the DBA_QUEUE_SCHEDULES view. Check the error message field to discover if any error occurred during propagation. If there was an error, the error time and error date field display when the error last occurred. After you have corrected the problem, propagation should resume.

You should also determine if the schedule has been disabled (DISABLED field is Y). Propagation should resume once you have enabled the schedule by invoking ENABLE_PROPAGATION_SCHEDULE. If the schedule is already enabled, check if the schedule is active. A schedule is active if a PROCESS_NAME exists for that schedule. If one does not exist, which means that the schedule is inactive, check the time of the last successful execution and when the schedule will be next executed. If the next scheduled execution is too far away, change the NEXT_TIME parameter of the schedule so that schedules are executed more frequently (assuming that the window is not set to be infinite).

Parameters of a schedule can be changed using the ALTER_PROPAGATION_SCHEDULE call. If a schedule is active then the source queue may not have any messages to be propagated.

Database link

There are a number of points at which propagation may break down:

You may want to determine if the destination is reachable with regard to whether the network connection to the destination is available. You do this by executing a simple distributed query, or by creating a connection descriptor that has the same connect string, and then by trying to connect to the remote database.
You need to ensure that the userid that scheduled the propagation (using dbms_aqadm.schedule_propagation) has access to the database link for the destination.
Verify that the userid used to login to the destination through the database link has been granted privileges to use the AQ.
Check if the queue name specified in the address attribute of the aq$_agent type (in the subscriber list for the source queue or in the recipient list of the enqueuer) both (a) exists at the specified destination, and (b) has been enabled for enqueuing. All these and other errors that the propagator encounters are logged into trace file(s) generated by the job_queue processes in $ORACLE_HOME/log directory.

Type checking

AQ will not propagate messages from one queue to another if the payload-types of the two queues are not equivalent. An administrator can verify if the source and destination's payload types match by executing the DBMS_AQADM.VERIFY_QUEUE_TYPES procedure. The results of the type checking will be stored in the aq$_message_types table. This table can be accessed using the OID of the source queue and the address of the destination queue (i.e. [schema.]queue_name[@destination]).

4 Administrative Interface: Basic Operations

Use Case Model: Administrative Interface -- Basic Operations

Table 4-1 Use Case Model: Administrative Interface -- Basic Operations

Figure 4-1 Use Case Diagram: Administrator's Interface -- Basic Operations

Create a Queue Table

Figure 4-2 Use Case Diagram: Create a Queue Table

Purpose:

Syntax

Usage:

Table 4-2 DBMS_AQADM.CREATE_QUEUE_TABLE

Usage Notes:

Example: Create a Queue Table Using PL/SQL (DBMS_AQADM Package)

Create queue table for queues containing messages of object type

Create queue table for queues containing messages of RAW type

Create a queue table for prioritized messages

Create a queue table for multiple consumers

Create a queue table for multiple consumers compatible with 8.1

Create a queue table in a specified tablespace

Create a Queue Table [Set Storage Clause]

Figure 4-3 Use Case Diagram: Create a Queue Table [Set Storage Clause]

Alter a Queue Table

Figure 4-4 Use Case Diagram: Alter a Queue Table

Purpose:

Syntax:

Usage:

Table 4-3 DBMS_AQADM.ALTER_QUEUE_TABLE

Example: Alter a Queue Table Using PL/SQL (DBMS_AQADM Package)

Usage Notes

Drop a Queue Table

Figure 4-5 Use Case Diagram: Drop a Queue Table

Purpose:

Syntax:

Usage:

Table 4-4 DBMS_AQADM.DROP_QUEUE_TABLE

Example: Drop a Queue Table Using PL/SQL (DBMS_AQADM Package)

Create a Queue

Figure 4-6 Use Case Diagram: Create a Queue

Purpose:

Syntax:

Usage:

Table 4-5 DBMS_AQADM.CREATE_QUEUE

Usage Notes

Example: Create a Queue Using PL/SQL (DBMS_AQADM)

Create a queue within a queue table for messages of object type

Create a queue within a queue table for messages of RAW type

Create a prioritized message queue table and queue

Create a queue table and queue meant for multiple consumers

Create a queue table and queue to demonstrate propagation

Create a queue table and queue for multiple consumers compatible with 8.1

Create a Non-Persistent Queue

Figure 4-7 Use Case Diagram: Create a Non-Persistent Queue

Purpose

Syntax

Usage:

Table 4-6 DBMS_AQADM.CREATE_NP_QUEUE

Usage Notes

Example: Create a Non-Persistent Queue Using PL/SQL (DBMS_AQADM)

Alter a Queue

Figure 4-8 Use Case Diagram: Alter a Queue

Purpose:

Syntax:

Usage:

Table 4-7 DBMS_AQADM.ALTER_QUEUE

Usage Notes

Example: Alter a Queue Using PL/SQL (DBMS_AQADM)

Drop a Queue

Figure 4-9 Use Case Diagram: Drop a Queue

Purpose:

Syntax:

Usage:

Table 4-8 DBMS_AQADM.DROP_QUEUE

Example: Drop a Queue Using PL/SQL (DBMS_AQADM)

Drop a Standard Queue

Drop a Non-Persistent Queue

Start a Queue

Figure 4-10 Use Case Diagram: Start a Queue

Purpose:

Syntax:

Usage:

Table 4-9 DBMS_AQADM.START_QUEUE

4
Administrative Interface: Basic Operations