CREATE QUEUE TABLE v13

Advanced Server includes extra syntax (not offered by Oracle) with the CREATE QUEUE TABLE SQL command. This syntax can be used in association with DBMS_AQADM.

Name

CREATE QUEUE TABLE -- create a new queue table.

Synopsis

Use CREATE QUEUE TABLE to define a new queue table:

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

where option_name and the corresponding option_value can be:

• SORT_LIST: Specify option_value as priority, enq_time
• MULTIPLE_CONSUMERS: Specify option_value asFALSE, TRUE
• MESSAGE_GROUPING: Specify option_value as NONE, TRANSACTIONAL
• STORAGE_CLAUSE: Specify option_value as TABLESPACE tablespace_name, PCTFREE integer, PCTUSED integer, INITRANS integer, MAXTRANS integer, STORAGE storage_option Where storage_option is one or more of the following: MINEXTENTS integer, MAXEXTENTS integer, PCTINCREASE integer, INITIAL size_clause, NEXT, FREELISTS integer, OPTIMAL size_clause, BUFFER_POOL {KEEP|RECYCLE|DEFAULT}.

Please note that only the TABLESPACE option is enforced; all others are accepted for compatibility and ignored. Use the TABLESPACE clause to specify the name of a tablespace in which the table will be created.

Description

CREATE QUEUE TABLE allows a superuser or a user with the aq_administrator_role privilege to create a new queue table.

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 new queue table is created in the current schema.

The name of the queue table must be unique from the name of any other queue table in the same schema.

Parameters

name

The name (optionally schema-qualified) of the new queue table.

type_name

The name of an existing type that describes the payload of each entry in the queue table. For information about defining a type, see CREATE TYPE.

option_name option_value

The name of any options that will be associated with the new queue, and the corresponding value for the option. If the call to CREATE QUEUE includes duplicate option names, the server will return an error. The following values are accepted:

• SORT_LIST: Use the SORT_LIST option to control the dequeueing order of the queue; specify the names of the column(s) that will be used to sort the queue (in ascending order). The currently accepted values are the following combinations of enq_time and priority:

  enq_time. priority

  priority. enq_time

  priority

  enq_time

  Any other value will return an ERROR.

• MULTIPLE_CONSUMERS: A BOOLEAN value that indicates if a message can have more than one consumer (TRUE), or are limited to one consumer per message (FALSE).

• MESSAGE_GROUPING: Specify none to indicate that each message should be dequeued individually, or transactional to indicate that messages that are added to the queue as a result of one transaction should be dequeued as a group.

• STORAGE_CLAUSE: Use STORAGE_CLAUSE to specify table attributes. STORAGE_CLAUSE may be TABLESPACE tablespace_name, PCTFREE integer, PCTUSED integer, INITRANS integer, MAXTRANS integer, STORAGE storage_option Where storage_option is one or more of the following:

  MINEXTENTS integer,

  MAXEXTENTS integer,

  PCTINCREASE integer,

  INITIAL size_clause,

  NEXT,

  FREELISTS integer,

  OPTIMAL size_clause,

  BUFFER_POOL {KEEP|RECYCLE|DEFAULT}.

Please note that only the TABLESPACE option is enforced; all others are accepted for compatibility and ignored. Use the TABLESPACE clause to specify the name of a tablespace in which the table will be created.

Examples

You must create a user-defined type before creating a queue table; the type describes the columns and data types within the table. The following command creates a type named work_order:

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

The following command uses 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));

See Also

ALTER QUEUE TABLE, DROP QUEUE TABLE