Table of Contents Previous Next


2 The SQL Language : 2.3 SQL Commands : 2.3.4 ALTER QUEUE

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.
ALTER QUEUE -- allows a superuser or a user with the aq_administrator_role privilege to modify the attributes of a queue.
ALTER QUEUE queue_name RENAME TO new_name
The name (optionally schema-qualified) of an existing queue.
Include the RENAME TO clause and a new name for the queue to rename 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
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
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 ]
The name (optionally schema-qualified) of an existing queue.
Include the ACCESS keyword to enable or disable enqueuing and/or dequeuing on a particular queue.
Use the START and STOP keywords to indicate the desired state of the queue.
Use the FOR clause to indicate if you are specifying the state of enqueueing or dequeueing activity on the specified queue.
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 ]
The name (optionally schema-qualified) of an existing queue.
Include the ADD or DROP keywords to enable add or remove callback details for a queue.
location_name specifies the name of the callback procedure.
callback_option
callback_option can be context; specify a RAW value when including this clause.
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 ACCESS STOP NOWAIT;
ALTER QUEUE work_order ACCESS STOP FOR enqueue;
ALTER QUEUE work_order ACCESS STOP FOR dequeue;

2 The SQL Language : 2.3 SQL Commands : 2.3.4 ALTER QUEUE

Table of Contents Previous Next