3.3 DBMS_AQADM

Table of Contents Previous Next


3 Built-In Packages : 3.3 DBMS_AQADM

Advanced Server also provides extended (non-compatible) functionality for the DBMS_AQ package with SQL commands. Please see the Database Compatibility for Oracle Developers Reference Guide for detailed information about the following SQL commands:
ALTER QUEUE
ALTER QUEUE TABLE
CREATE QUEUE
CREATE QUEUE TABLE
DROP QUEUE
DROP QUEUE TABLE
Advanced Server's implementation of DBMS_AQADM is a partial implementation when compared to Oracle's version. Only those functions and procedures listed in the table above are supported.
Use with create_queue to specify queue_type.
Use with create_queue to specify queue_type.
Use with create_queue to specify retention_time.
Use the ALTER_QUEUE procedure to modify an existing queue. The signature is:
ALTER_QUEUE(
max_retries IN NUMBER DEFAULT NULL,
retry_delay IN NUMBER DEFAULT 0
retention_time IN NUMBER DEFAULT 0,
auto_commit IN BOOLEAN DEFAULT TRUE)
comment IN VARCHAR2 DEFAULT NULL,
max_retries specifies the maximum number of attempts to remove a message with a dequeue statement. The value of max_retries is incremented with each ROLLBACK statement. When the number of failed attempts reaches the value specified by max_retries, the message is moved to the exception queue. Specify 0 to indicate that no retries are allowed.
retry_delay specifies the number of seconds until a message is scheduled for re-processing after a ROLLBACK. Specify 0 to indicate that the message should be retried immediately (the default).
retention_time specifies the length of time (in seconds) that a message will be stored after being dequeued. You can also specify 0 (the default) to indicate the message should not be retained after dequeueing, or INFINITE to retain the message forever.
comment specifies a comment associated with the queue.
The following command alters a queue named work_order, setting the retry_delay parameter to 5 seconds:
Use the ALTER_QUEUE_TABLE procedure to modify an existing queue table. The signature is:
ALTER_QUEUE_TABLE (
queue_table IN VARCHAR2,
comment IN VARCHAR2 DEFAULT NULL,
primary_instance IN BINARY_INTEGER DEFAULT 0,
secondary_instance IN BINARY_INTEGER DEFAULT 0,
Use the comment parameter to provide a comment about the queue table.
primary_instance is accepted for compatibility and stored, but is ignored.
secondary_instance is accepted for compatibility, but is ignored.
The queue table is named work_order_table; the command adds a comment to the definition of the queue table.
Use the CREATE_QUEUE procedure to create a queue in an existing queue table. The signature is:
CREATE_QUEUE(
queue_name
IN VARCHAR2
queue_table IN VARCHAR2,
queue_type IN BINARY_INTEGER DEFAULT NORMAL_QUEUE,
max_retries IN NUMBER DEFAULT 5,
retry_delay IN NUMBER DEFAULT 0
retention_time IN NUMBER DEFAULT 0,
dependency_tracking IN BOOLEAN DEFAULT FALSE,
comment IN VARCHAR2 DEFAULT NULL,
auto_commit IN BOOLEAN DEFAULT TRUE)
DBMS_AQADM.NORMAL_QUEUE – This value specifies a normal queue (the default).
DBMS_AQADM.EXCEPTION_QUEUE – This value specifies that the new queue is an exception queue. An exception queue will support only dequeue operations.
max_retries specifies the maximum number of attempts to remove a message with a dequeue statement. The value of max_retries is incremented with each ROLLBACK statement. When the number of failed attempts reaches the value specified by max_retries, the message is moved to the exception queue. The default value for a system table is 0; the default value for a user created table is 5.
retry_delay specifies the number of seconds until a message is scheduled for re-processing after a ROLLBACK. Specify 0 to indicate that the message should be retried immediately (the default).
retention_time specifies the length of time (in seconds) that a message will be stored after being dequeued. You can also specify 0 (the default) to indicate the message should not be retained after dequeueing, or INFINITE to retain the message forever.
comment specifies a comment associated with the queue.
The following anonymous block creates a queue named work_order in the work_order_table table:
Use the CREATE_QUEUE_TABLE procedure to create a queue table. The signature is:
CREATE_QUEUE_TABLE (
queue_table IN VARCHAR2,
queue_payload_type IN VARCHAR2,
storage_clause IN VARCHAR2 DEFAULT NULL,
sort_list IN VARCHAR2 DEFAULT NULL,
multiple_consumers IN BOOLEAN DEFAULT FALSE,
message_grouping IN BINARY_INTEGER DEFAULT NONE,
comment IN VARCHAR2 DEFAULT NULL,
auto_commit IN BOOLEAN DEFAULT TRUE,
primary_instance IN BINARY_INTEGER DEFAULT 0,
secondary_instance IN BINARY_INTEGER DEFAULT 0,
compatible IN VARCHAR2 DEFAULT NULL,
secure IN BOOLEAN DEFAULT FALSE)
The user-defined type of the data that will be stored in the queue table. Please note that to specify a RAW data type, you must create a user-defined type that identifies a RAW type.
Use the storage_clause parameter to specify attributes for the queue table. 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.
storage_clause may be one or more of the following:
TABLESPACE tablespace_name, PCTFREE integer, PCTUSED integer, INITRANS integer, MAXTRANS integer or STORAGE storage_option.
storage_option may be 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}.
sort_list controls 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:
If specified, multiple_consumers must be FALSE.
If specified, message_grouping must be NONE.
Use the comment parameter to provide a comment about the queue table.
auto_commit is accepted for compatibility, but is ignored.
primary_instance is accepted for compatibility and stored, but is ignored.
secondary_instance is accepted for compatibility, but is ignored.
compatible is accepted for compatibility, but is ignored.
secure is accepted for compatibility, but is ignored.
The following anonymous block first creates a type (work_order) with attributes that hold a name (a VARCHAR2), and a project description (a TEXT). The block then uses that type to create a queue table:
The queue table is named work_order_table, and contains a payload of a type work_order. A comment notes that this is the Work order message queue table.
Use the DROP_QUEUE procedure to delete a queue. The signature is:
DROP_QUEUE(
queue_name IN VARCHAR2,
auto_commit IN BOOLEAN DEFAULT TRUE)
auto_commit is accepted for compatibility, but is ignored.
Use the DROP_QUEUE_TABLE procedure to delete a queue table. The signature is:
DROP_QUEUE_TABLE(
queue_table IN VARCHAR2,
force IN BOOLEAN default FALSE,
auto_commit IN BOOLEAN default TRUE)
The force keyword determines the behavior of the DROP_QUEUE_TABLE command when dropping a table that contain entries:
If the target table contains entries and force is FALSE, the command will fail, and the server will issue an error.
If the target table contains entries and force is TRUE, the command will drop the table and any dependent objects.
auto_commit is accepted for compatibility, but is ignored.
Use the PURGE_QUEUE_TABLE procedure to delete messages from a queue table. The signature is:
PURGE_QUEUE_TABLE(
queue_table IN VARCHAR2,
purge_condition IN VARCHAR2,
purge_options IN aq$_purge_options_t)
queue_table specifies the name of the queue table from which you are deleting a message.
Use purge_condition to specify a condition (a SQL WHERE clause) that the server will evaluate when deciding which messages to purge.
purge_options is an object of the type aq$_purge_options_t. An aq$_purge_options_t object contains:
Specify TRUE if an exclusive lock should be held on all queues within the table; the default is FALSE.
delivery_mode specifies the type of message that will be purged. The only accepted value is DBMS_AQ.PERSISTENT.
The following anonymous block removes any messages from the work_order_table with a value in the completed column of YES:
Use the START_QUEUE procedure to make a queue available for enqueuing and dequeueing. The signature is:
START_QUEUE(
queue_name IN VARCHAR2,
enqueue IN BOOLEAN DEFAULT TRUE,
dequeue IN BOOLEAN DEFAULT TRUE)
queue_name specifies the name of the queue that you are starting.
Specify TRUE to enable enqueueing (the default), or FALSE to leave the current setting unchanged.
Specify TRUE to enable dequeueing (the default), or FALSE to leave the current setting unchanged.
The following anonymous block makes a queue named work_order available for enqueueing:
Use the STOP_QUEUE procedure to disable enqueuing or dequeueing on a specified queue. The signature is:
STOP_QUEUE(
queue_name IN VARCHAR2,
enqueue IN BOOLEAN DEFAULT TRUE,
dequeue IN BOOLEAN DEFAULT TRUE,
wait IN BOOLEAN DEFAULT TRUE)
queue_name specifies the name of the queue that you are stopping.
Specify TRUE to disable enqueueing (the default), or FALSE to leave the current setting unchanged.
Specify TRUE to disable dequeueing (the default), or FALSE to leave the current setting unchanged.
Specify TRUE to instruct the server to wait for any uncompleted transactions to complete before applying the specified changes; while waiting to stop the queue, no transactions are allowed to enqueue or dequeue from the specified queue. Specify FALSE to stop the queue immediately.

3 Built-In Packages : 3.3 DBMS_AQADM

Table of Contents Previous Next