Previous Table of Contents Next


DBMS_AQADM

Administrators must be granted AQ_ADMINISTRATOR_ROLE and rights to the object types used for queues, queue tables, and subscribers. To grant use of these object types, DBMS_AQADM.GRANT_TYPE_ACCESS must be run by SYS for each user.

Several data dictionary views are provided for keeping track of queues. These are AQ$QUEUE_TABLE_NAME, DBA_QUEUE_TABLES, USER_QUEUE_TABLES, DBA_QUEUES, and USER_QUEUES.


PROCEDURE 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);
queue_table Name to queue table being created.
queue_payload_type Specifies data type of messages. Can be RAW or an object type.
storage_clause Table storage parameters for CREATE TABLE statement. See “SQL Reference Guide” for details.
sort_list Specifies columns to be used for sorting messages. Valid columns are priority and enq_time. The default is to sort by enq_time.
multiple_consumers TRUE means multiple consumers can exist for each message.
message_grouping TRANSACTIONAL means all messages queued during the same transaction will be dequeued together. NONE means each message is treated separately.
comment Gets recorded in the data dictionary.
auto_commit TRUE means the queue table is created regardless of the current transaction status. FALSE means the queue table is not created until the current transaction commits.

This procedure creates a table to hold queues, a default exception queue, a read-only view of the queue table, and an index for queues set up for multiple consumers. If you named the queue table monitor_qt, these objects would be named aq$_monitor_qt_e, aq$_monitor_qt, and aq$_monitor_qt_i, respectively.


PROCEDURE CREATE_QUEUE(

     queue_name IN VARCHAR2,

     queue_table IN VARCHAR2,

     queue_type IN BINARY_INTEGER default NORMAL_QUEUE,

     max_retries IN NUMBER default 0,

     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);
queue_name Name of queue to be created.
queue_table Queue table to create queue in.
queue_type NORMAL_QUEUE or EXCEPTION_QUEUE.
max_retries Maximum number of times an agent can dequeue a message with REMOVE and then roll back. When max_retries is reached, the message is moved to the exception queue.
retry_delay Time to wait between retries. Cannot be set for queues with multiple consumers.
retention_time Seconds to keep a message after it is dequeued. Valid values are 0 to INFINITE.
dependency_tracking For future use. TRUE produces an error.
comment Stored in queue catalog.
auto_commit TRUE means the queue is created independently of any transactions, and FALSE means the current transaction must commit before the queue becomes persistent.

This creates a queue in which to store messages. Before it can be used, START_QUEUE must be run on it.


PROCEDURE START_QUEUE(

     queue_name IN VARCHAR2,

     enqueue IN BOOLEAN default TRUE,

     dequeue IN BOOLEAN default TRUE);
queue_name Name of queue to start.
enqueue TRUE allows enqueuing of messages.
dequeue TRUE allows dequeuing of messages.

This procedure must be used to enable a queue. Messages in an exception queue can only be dequeued, so the enqueue option does not apply to them. 


PROCEDURE STOP_QUEUE(

     queue_name IN VARCHAR2,

     enqueue IN BOOLEAN default TRUE,

     dequeue IN BOOLEAN default TRUE,

     wait IN BOOLEAN default TRUE);
queue_name Name of queue to be stopped.
enqueue TRUE disables enqueuing.
dequeue TRUE disables dequeuing.
wait TRUE blocks new processes on the queue while it waits for all pending processes to commit before it stops the queue. 

PROCEDURE DROP_QUEUE(

     queue_name IN VARCHAR2,

     auto_commit IN BOOLEAN DEFAULT TRUE);

This procedure removes the specified queue from the queue table. If auto_commit is TRUE, the drop occurs immediately. If it’s FALSE, the drop does not occur until the transaction commits.


PROCEDURE DROP_QUEUE_TABLE(

     queue_table IN VARCHAR2,

     force IN BOOLEAN default FALSE,

     auto_commit IN BOOLEAN default TRUE);
queue_table Name of queue table to drop.
force TRUE means all queues in the table are stopped and dropped. FALSE returns an error if any queues are in the table.
auto_commit TRUE means the queue table is dropped regardless of the current transaction status. FALSE means the queue table is not dropped until the current transaction commits. 

PROCEDURE ALTER_QUEUE(

     queue_name IN VARCHAR2,

     max_retries IN NUMBER default NULL,

     retry_delay IN NUMBER default NULL,

     retention_time IN NUMBER default NULL,

     auto_commit IN BOOLEAN default TRUE);


Previous Table of Contents Next
Используются технологии uCoz