CREATE_QUEUE_TABLE Procedure

This procedure creates a queue table for messages of a predefined 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 Oracle9i 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. Note: 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'. If the database is in 8.1 or higher compatible mode, the default value is '8.1' If the database is in 8.0 compatible mode, the default value 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 or later. See the Oracle9i Application Developer's Guide - Advanced Queuing for more information.

The default value of the compatible parameter depends on the database compatibility mode in the init.ora

• If the database is in 8.1 or higher compatible mode, the default value is '8.1'

• If the database is in 8.0 compatible mode, the default value is '8.0'

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.