Creates a queue table.

Syntax

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

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

option_name option_value
SORT_LIST priority, enq_time
MULTIPLE_CONSUMERS FALSE, TRUE
MESSAGE_GROUPING NONE, 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 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 option is enforced. All the other options are supported for compatibility, but they can be ignored. You can use the TABLESPACE clause to specify the name of the tablespace in which the table is to be created.

Description

The CREATE QUEUE TABLE command allows you 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 no schema name is provided, 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 the 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 describes valid values of these two parameters.
Table 1. Valid value list of option_name and option_value
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 other values, ERROR is returned.
MULTIPLE_CONSUMERS Specifies whether a message can be consumed by multiple users (TRUE) or only one user (FALSE). Data type: BOOLEAN.
MESSAGE_GROUPING Specifies the method in which a message is dequeued. none indicates that each message is separately dequeued. transactional indicates that multiple messages that are the results of the same transaction are added to a queue and dequeued as a group.
STORAGE_CLAUSE The attributes 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 option is enforced. All the other options are supported for compatibility, but they can be ignored. You can use the TABLESPACE clause to specify the name of the tablespace in which the table is to be created.

Examples

Before you create a queue table, you must create a custom type. This type describes the columns and the 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));