CREATE QUEUE v11

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

Name

CREATE QUEUE -- create a queue.

Synopsis

Use CREATE QUEUE to define a new queue:

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

where option_name and the corresponding option_value can be:

TYPE [normal_queue | exception_queue]
RETRIES [INTEGER]
RETRYDELAY [DOUBLE PRECISION]
RETENTION [DOUBLE PRECISION]

Description

The CREATE QUEUE command allows a database superuser or any user with the system-defined aq_administrator_role privilege to create a new queue in the current database.

If the name of the queue is schema-qualified, the queue is created in the specified schema. If a schema is not included in the CREATE QUEUE command, the queue is created in the current schema. A queue may only be created in the schema in which the queue table resides. The name of the queue must be unique from the name of any other queue in the same schema.

Use DROP QUEUE to remove a queue.

Parameters

name

The name (optionally schema-qualified) of the queue to be created.

queue_table_name

The name of the queue table with which this queue is associated.

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:

• TYPE: Specify normal_queue to indicate that the queue is a normal queue, or exception_queue to indicate that the queue is an exception queue. An exception queue will only accept dequeue operations.
• RETRIES: An INTEGER value that specifies the maximum number of attempts to remove a message from a queue.
• RETRYDELAY: A DOUBLE PRECISION value that specifies the number of seconds after a ROLLBACK that the server will wait before retrying a message.
• RETENTION: A DOUBLE PRECISION value that specifies the number of seconds that a message will be saved in the queue table after dequeueing.

Examples

The following command creates a queue named work_order that is associated with a queue table named work_order_table:

CREATE QUEUE work_order QUEUE TABLE work_order_table (RETRIES 5, RETRYDELAY 2);

The server will allow 5 attempts to remove a message from the queue, and enforce a retry delay of 2 seconds between attempts.

See Also

ALTER QUEUE, DROP QUEUE