Creates a queue table.

Syntax

CREATE QUEUE TABLE name OF type_name [ ( { option_name option_value } [, ... ] ) ]

The following table lists valid options of the option_name and option_value parameters.

option_name option_value
SORT_LIST priority and enq_time
MULTIPLE_CONSUMERS FALSE and TRUE
MESSAGE_GROUPING NONE and TRANSACTIONAL
STORAGE_CLAUSE TABLESPACE tablespace_name, PCTFREE integer, PCTUSED integer, INITRANS integer, MAXTRANS integer, and STORAGE storage_option

storage_option can be one or more of the following clauses:

MINEXTENTS integer, MAXEXTENTS integer, PCTINCREASE integer, INITIAL size_clause, NEXT, FREELISTS integer, OPTIMAL size_clause, and BUFFER_POOL {KEEP|RECYCLE|DEFAULT}.

Note Only the TABLESPACE clause is enforced. You can skip all other options, which are supported for compatibility. You can use the TABLESPACE clause to specify the name of the tablespace in which the table will be created.

Description

You can use the CREATE QUEUE TABLE command to create a queue table if you are a superuser or a user who has the aq_administrator_role privilege.

If the call to CREATE QUEUE TABLE includes a schema name, the queue table is created in the specified schema. If you do not specify a schema, the queue table is created in the current schema.

The name of a queue table must be unique in a schema.

Parameters

Parameter Description
name The name of the queue table to be created. The name can be schema-qualified.
type_name The name of the current type that describes payloads of each entry in the queue table. For more information about how to define a type, see the CREATE TYPE topic.
option_name option_value The names and values of options that are associated with the new queue table. If the call to the CREATE QUEUE TABLE includes duplicate option names, the server returns an error. The following table lists valid options of these two parameters.
Table 1. Table of option names and values
Parameter Description
SORT_LIST Specifies the dequeuing order and the names of one or more columns that are used to sort the queue in ascending order. Valid values include the following combinations of enq_time and priority:
  • enq_time. priority
  • priority. enq_time
  • priority
  • enq_time
  • If you specify any other value, ERROR is returned.
MULTIPLE_CONSUMERS Specifies whether a message can be consumed by multiple users or only one user. Data type: BOOLEAN. Valid values: TRUE and FALSE.
MESSAGE_GROUPING Specifies the method in which a message is dequeued. none: indicates that each message is dequeued separately. transactional: indicates that multiple messages are added to the queue in a single transaction and dequeued as a group.
STORAGE_CLAUSE Specifies the parameters of a table. Valid values: TABLESPACE tablespace_name, PCTFREE integer, PCTUSED integer, INITRANS integer, MAXTRANS integer, and STORAGE storage_option.

Storage_option can be one or more of the following values: MINEXTENTS integer, MAXEXTENTS integer, PCTINCREASE integer, INITIAL size_clause, NEXT, FREELISTS integer, OPTIMAL size_clause, and BUFFER_POOL {KEEP|RECYCLE|DEFAULT}.

Note Only the TABLESPACE clause is executed. You can skip all other options, which are supported for compatibility. You can use the TABLESPACE clause to specify the name of the tablespace in which the table will be created.

Example

Before creating a queue table, you must create a custom type. This type describes the columns and data types in the table. You can run the following command to create a type named work_order:

CREATE TYPE work_order AS (name VARCHAR2, project TEXT, completed BOOLEAN);

You can run the following command to use the work_order type to create a queue table named work_order_table:

CREATE QUEUE TABLE work_order_table OF work_order (sort_list (enq_time, priority));