ALTER QUEUE v11

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

Name

ALTER QUEUE -- allows a superuser or a user with the aq_administrator_role privilege to modify the attributes of a queue.

Synopsis

This command is available in four forms. The first form of this command changes the name of a queue.

ALTER QUEUE <queue_name> RENAME TO <new_name>

Parameters

queue_name

The name (optionally schema-qualified) of an existing queue.

RENAME TO

Include the RENAME TO clause and a new name for the queue to rename the queue.

new_name

New name for the queue.

The second form of the ALTER QUEUE command modifies the attributes of the queue:

ALTER QUEUE <queue_name> SET [ ( { <option_name option_value>  ) } [,SET <option_name> ] ]

Parameters

queue_name

The name (optionally schema-qualified) of an existing queue.

Include the SET clause and option_name/option_value pairs to modify the attributes of the queue:

option_name option_value

The name of the option or options to be associated with the new queue and the corresponding value of the option. If you provide duplicate option names, the server will return an error.

• If option_name is retries, provide an integer that represents the number of times a dequeue may be attempted.
• If option_name is retrydelay, provide a double-precision value that represents the delay in seconds.
• If option_name is retention, provide a double-precision value that represents the retention time in seconds.

Use the third form of the ALTER QUEUE command to enable or disable enqueuing and/or dequeuing on a particular queue:

ALTER QUEUE <queue_name> ACCESS { START | STOP } [ FOR { enqueue | dequeue } ] [ NOWAIT ]

Parameters

queue_name

The name (optionally schema-qualified) of an existing queue.

ACCESS

Include the ACCESS keyword to enable or disable enqueuing and/or dequeuing on a particular queue.

START | STOP

Use the START and STOP keywords to indicate the desired state of the queue.

FOR enqueue|dequeue

Use the FOR clause to indicate if you are specifying the state of enqueueing or dequeueing activity on the specified queue.

NOWAIT

Include the NOWAIT keyword to specify that the server should not wait for the completion of outstanding transactions before changing the state of the queue. The NOWAIT keyword can only be used when specifying an ACCESS value of STOP. The server will return an error if NOWAIT is specified with an ACCESS value of START.

Use the fourth form to ADD or DROP callback details for a particular queue.

ALTER QUEUE <queue_name> { ADD | DROP } CALL TO <location_name> [ WITH <callback_option> ]

Parameters

queue_name

The name (optionally schema-qualified) of an existing queue.

ADD | DROP

Include the ADD or DROP keywords to enable add or remove callback details for a queue.

location_name

location_name specifies the name of the callback procedure.

callback_option

callback_option can be context; specify a RAW value when including this clause.

Examples

The following example changes the name of a queue from work_queue_east to work_order:

ALTER QUEUE work_queue_east RENAME TO work_order;

The following example modifies a queue named work_order, setting the number of retries to 100, the delay between retries to 2 seconds, and the length of time that the queue will retain dequeued messages to 10 seconds:

ALTER QUEUE work_order SET (retries 100, retrydelay 2, retention 10);

The following commands enable enqueueing and dequeueing in a queue named work_order:

ALTER QUEUE work_order ACCESS START;
ALTER QUEUE work_order ACCESS START FOR enqueue;
ALTER QUEUE work_order ACCESS START FOR dequeue;

The following commands disable enqueueing and dequeueing in a queue named work_order:

ALTER QUEUE work_order ACCESS STOP NOWAIT;
ALTER QUEUE work_order ACCESS STOP FOR enqueue;
ALTER QUEUE work_order ACCESS STOP FOR dequeue;

See Also

CREATE QUEUE, DROP QUEUE