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