PL/SQL Packages and Types Reference 10g Release 1 (10.1) Part Number B10802-01 |
|
|
View PDF |
The DBMS_AQADM
package provides procedures to manage Oracle Streams Advanced Queuing (AQ) configuration and administration information.
See Also:
|
This chapter contains the following topics:
When using enumerated constants, such as INFINITE
, TRANSACTIONAL
, or NORMAL_QUEUE
, the symbol must be specified with the scope of the packages defining it. All types associated with the administrative interfaces must be prepended with DBMS_AQADM
. For example: DBMS_AQADM.NORMAL_QUEUE
.
Parameter | Options |
---|---|
|
|
|
|
|
|
See Also:
For more information on the Java classes and data structures used in both |
This procedure creates an alias for a queue, agent, or a JMS ConnectionFactory in LDAP. The alias will be placed directly under the database server's distinguished name in LDAP hierarchy.
DBMS_AQADM.ADD_ALIAS_TO_LDAP( alias IN VARCHAR2, obj_location IN VARCHAR2);
Parameter | Description |
---|---|
|
Name of the alias. Example: |
|
The distinguished name of the object (queue, agent or connection factory) to which |
This method can be used to create aliases for Queues, Agents, and JMS ConnectionFactory objects. These object must exist before the alias is created. These aliases can be used for JNDI lookup in JMS and Oracle Streams AQ Internet access.
This procedure adds a default subscriber to a queue.
DBMS_AQADM.ADD_SUBSCRIBER ( queue_name IN VARCHAR2, subscriber IN sys.aq$_agent, rule IN VARCHAR2 DEFAULT NULL, transformation IN VARCHAR2 DEFAULT NULL);
A program can enqueue messages to a specific list of recipients or to the default list of subscribers. This operation only succeeds on queues that allow multiple consumers. This operation takes effect immediately, and the containing transaction is committed. Enqueue requests that are executed after the completion of this call will reflect the new behavior.
Any string within the rule must be quoted:
rule => 'PRIORITY <= 3 AND CORRID = ''FROM JAPAN'''
Note that these are all single quotation marks.
This procedure alters an agent registered for Oracle Streams AQ Internet access. It is also used to alter an Oracle Streams AQ agent that accesses secure queues.
See Also:
Oracle Streams Concepts and Administration for information about secure queues |
DBMS_AQADM.ALTER_AQ_AGENT ( agent_name IN VARCHAR2, certificate_location IN VARCHAR2 DEFAULT NULL, enable_http IN BOOLEAN DEFAULT FALSE, enable_smtp IN BOOLEAN DEFAULT FALSE, enable_anyp IN BOOLEAN DEFAULT FALSE )
This procedure alters parameters for a propagation schedule.
DBMS_AQADM.ALTER_PROPAGATION_SCHEDULE ( queue_name IN VARCHAR2, destination IN VARCHAR2 DEFAULT NULL, duration IN NUMBER DEFAULT NULL, next_time IN VARCHAR2 DEFAULT NULL, latency IN NUMBER DEFAULT 60);
This procedure alters existing properties of a queue. The parameters max_retries
, retention_time
, and retry_delay
are not supported for nonpersistent queues.
DBMS_AQADM.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, comment IN VARCHAR2 DEFAULT NULL);
This procedure alters the existing properties of a queue table.
DBMS_AQADM.ALTER_QUEUE_TABLE ( queue_table IN VARCHAR2, comment IN VARCHAR2 DEFAULT NULL, primary_instance IN BINARY_INTEGER DEFAULT NULL, secondary_instance IN BINARY_INTEGER DEFAULT NULL);
This procedure alters existing properties of a subscriber to a specified queue. Only the rule can be altered.
DBMS_AQADM.ALTER_SUBSCRIBER ( queue_name IN VARCHAR2, subscriber IN sys.aq$_agent, rule IN VARCHAR2 transformation IN VARCHAR2);
Parameter | Description |
---|---|
|
Name of the queue. |
|
Agent on whose behalf the subscription is being altered. See "AQ$_AGENT Type". |
|
A conditional expression based on the message properties, the message data properties and PL/SQL functions. The rule parameter cannot exceed 4000 characters. To eliminate the rule, set the rule parameter to |
|
Specifies a transformation that will be applied when this subscriber dequeues the message. The source type of the transformation must match the type of the queue. If the subscriber is remote, then the transformation is applied before propagation to the remote queue. |
This procedure alters both the rule and the transformation for the subscriber. If you want to retain the existing value for either of them, you must specify its old value. The current values for rule and transformation for a subscriber can be obtained from the schema
.AQ$
queue_table
_R
and schema
.AQ$
queue_table
_S
views.
This procedure registers an agent for Oracle Streams AQ Internet access using HTTP/SMTP protocols. It is also used to create an Oracle Streams AQ agent to access secure queues.
See Also:
Oracle Streams Concepts and Administration for information about secure queues |
DBMS_AQADM.CREATE_AQ_AGENT ( agent_name IN VARCHAR2, certificate_location IN VARCHAR2 DEFAULT NULL, enable_http IN BOOLEAN DEFAULT FALSE, enable_smtp IN BOOLEAN DEFAULT FALSE, enable_anyp IN BOOLEAN DEFAULT FALSE )
The SYS.AQ$INTERNET_USERS
view has a list of all Oracle Streams AQ Internet agents.
This procedure creates a nonpersistent RAW
queue.
DBMS_AQADM.CREATE_NP_QUEUE ( queue_name IN VARCHAR2, multiple_consumers IN BOOLEAN DEFAULT FALSE, comment IN VARCHAR2 DEFAULT NULL);
Parameter | Description |
---|---|
|
Name of the nonpersistent queue that is to be created. The name must be unique within a schema and must follow object name guidelines in Oracle Database SQL Reference. |
|
Note that thi |
|
User-specified description of the queue. This user comment is added to the queue catalog. |
The queue may be either single-consumer or multiconsumer queue. All queue names must be unique within a schema. The queues are created in a 8.1-compatible or higher system-created queue table (AQ$_MEM_SC
or AQ$_MEM_MC
) in the same schema as that specified by the queue name.
If the queue name does not specify a schema name, the queue is created in the login user's schema. After a queue is created with CREATE_NP_QUEUE
, it can be enabled by calling START_QUEUE
. By default, the queue is created with both enqueue and dequeue disabled.
You cannot dequeue from a nonpersistent queue. The only way to retrieve a message from a nonpersistent queue is by using the OCI notification mechanism. You cannot invoke the listen
call on a nonpersistent queue.
This procedure creates a queue in the specified queue table.
DBMS_AQADM.CREATE_QUEUE ( queue_name IN VARCHAR2, queue_table IN VARCHAR2, queue_type IN BINARY_INTEGER DEFAULT NORMAL_QUEUE, max_retries IN NUMBER DEFAULT NULL, 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);
Parameter | Description |
---|---|
|
Name of the queue that is to be created. The name must be unique within a schema and must follow object name guidelines in Oracle Database SQL Reference with regard to reserved characters. |
|
Name of the queue table that will contain the queue. |
|
Specifies whether the queue being created is an exception queue or a normal queue. |
|
Limits the number of times a dequeue with the A message is moved to an exception queue if Note that |
|
Delay time, in seconds, before this message is scheduled for processing again after an application rollback. The default is 0, which means the message can be retried as soon as possible. This parameter has no effect if |
|
Number of seconds for which a message is retained in the queue table after being dequeued from the queue. |
|
Reserved for future use. |
|
User-specified description of the queue. This user comment is added to the queue catalog. |
|
Caution: This parameter has been deprecated. |
All queue names must be unique within a schema. After a queue is created with CREATE_QUEUE
, it can be enabled by calling START_QUEUE
. By default, the queue is created with both enqueue and dequeue disabled.
This procedure creates a queue table for messages of a predefined type.
DBMS_AQADM.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);
Parameter | Description |
---|---|
|
Name of a queue table to be created |
|
Type of the user data stored. See Type Name for valid values for this parameter. |
|
Storage parameter. The storage parameter is included in the If a tablespace is not specified here, then the queue table and all its related objects are created in the default user tablespace. If a tablespace is specified here, then the queue table and all its related objects are created in the tablespace specified in the storage clause. See Oracle Database SQL Reference for the usage of these parameters. |
|
The columns to be used as the sort key in ascending order. This parameter has the following format: The allowed column names are After a queue table is created with a specific ordering mechanism, all queues in the queue table inherit the same defaults. The order of a queue table cannot be altered after the queue table has been created. If no sort list is specified, then all the queues in this queue table are sorted by the enqueue time in ascending order. This order is equivalent to FIFO order. Even with the default ordering defined, a dequeuer is allowed to choose a message to dequeue by specifying its |
|
|
|
Message grouping behavior for queues created in the table. |
|
User-specified description of the queue table. This user comment is added to the queue catalog. |
|
Note: This parameter has been deprecated. |
|
The primary owner of the queue table. Queue monitor scheduling and propagation for the queues in the queue table are done in this instance. The default value for primary instance is 0, which means queue monitor scheduling and propagation will be done in any available instance. |
|
The queue table fails over to the secondary instance if the primary instance is not available. The default value is 0, which means that the queue table will fail over to any available instance. |
|
The lowest database version with which the queue is compatible. Currently the possible values are either |
|
This parameter must be set to |
The sort keys for dequeue ordering, if any, must be defined at table creation time. The following objects are created at this time:
aq$_
queue_table_name
_e
, a default exception queue associated with the queue tableaq$
queue_table_name
, a read-only view, which is used by Oracle Streams AQ applications for querying queue dataaq$_
queue_table_name
_t
, an index (or an index organized table (IOT) in the case of multiple consumer queues) for the queue monitor operationsaq$_
queue_table_name
_i
, an index (or an index organized table in the case of multiple consumer queues) for dequeue operationsFor 8.1-compatible or higher queue tables, the following index-organized tables are created:
aq$_
queue_table_name
_s
, a table for storing information about the subscribersaq$_
queue_table_name
_r
, a table for storing information about rules on subscriptionsaq$_
queue_table_name
_h
, an index-organized table for storing the dequeue history data
CLOB
, BLOB
, and BFILE
are valid attributes for Oracle Streams AQ object type payloads. However, only CLOB
and BLOB
can be propagated using Oracle Streams AQ propagation in Oracle8i release 8.1.5 or later. See the Oracle Streams Advanced Queuing User's Guide and Reference for more information.
The default value of the compatible parameter depends on the database compatibility mode in the init.ora
. If the database is in 10.1-compatible mode, the default value is 10.0. If the database is in 8.1-compatible or 9.2-compatible mode, the default value is 8.1. If the database is in 8.0 compatible mode, the default value is 8.0
You can specify and modify the primary_instance
and secondary_instance
only in 8.1-compatible or higher mode. You cannot specify a secondary instance unless there is a primary instance.
This procedure drops an alias for a queue, agent, or JMS ConnectionFactory in LDAP.
DBMS_AQ.DEL_ALIAS_FROM_LDAP( alias IN VARCHAR2);
Parameter | Description |
---|---|
|
The alias to be removed. |
This procedure revokes the privileges of a specific database user from an Oracle Streams AQ Internet agent.
DBMS_AQADM.DISABLE_DB_ACCESS ( agent_name IN VARCHAR2, db_username IN VARCHAR2)
The Oracle Streams AQ Internet agent should have been previously granted those privileges using the ENABLE_DB_ACCESS Procedure.
This procedure disables a propagation schedule.
DBMS_AQADM.DISABLE_PROPAGATION_SCHEDULE ( queue_name IN VARCHAR2, destination IN VARCHAR2 DEFAULT NULL);
This procedure drops an agent that was previously registered for Oracle Streams AQ Internet access.
DBMS_AQADM.DROP_AQ_AGENT ( agent_name IN VARCHAR2)
Parameter | Description |
---|---|
|
Specifies the username of the Oracle Streams AQ Internet agent |
This procedure drops an existing queue.
DBMS_AQADM.DROP_QUEUE ( queue_name IN VARCHAR2, auto_commit IN BOOLEAN DEFAULT TRUE);
DROP_QUEUE
is not allowed unless STOP_QUEUE
has been called to disable the queue for both enqueuing and dequeuing. All the queue data is deleted as part of the drop operation.
This procedure drops an existing queue table.
DBMS_AQADM.DROP_QUEUE_TABLE ( queue_table IN VARCHAR2, force IN BOOLEAN DEFAULT FALSE, auto_commit IN BOOLEAN DEFAULT TRUE);
All the queues in a queue table must be stopped and dropped before the queue table can be dropped. You must do this explicitly unless the force
option is used, in which case this is done automatically.
This procedure grants an Oracle Streams AQ Internet agent the privileges of a specific database user.
DBMS_AQADM.ENABLE_DB_ACCESS ( agent_name IN VARCHAR2, db_username IN VARCHAR2)
The Oracle Streams AQ Internet agent should have been previously created using the CREATE_AQ_AGENT Procedure.
For secure queues, the sender and receiver agent of the message must be mapped to the database user performing the enqueue or dequeue operation.
See Also:
Oracle Streams Concepts and Administration for information about secure queues |
The SYS.AQ$INTERNET_USERS
view has a list of all Oracle Streams AQ Internet agents and the names of the database users whose privileges are granted to them.
Enqueue of JMS types and XML types does not work with Oracle Streams Sys.Anydata
queues unless you call this procedure after DBMS_STREAMS_ADM.SET_UP_QUEUE
. Enabling an Oracle Streams queue for these types may affect import/export of the queue table.
DBMS_AQADM.ENABLE_JMS_TYPES ( queue_table IN VARCHAR2);
Parameter | Description |
---|---|
|
Specifies name of the queue table to be enabled for JMS and XML types. |
This procedure enables a previously disabled propagation schedule.
DBMS_AQADM.ENABLE_PROPAGATION_SCHEDULE ( queue_name IN VARCHAR2, destination IN VARCHAR2 DEFAULT NULL);
This procedure retrieves the value of watermark set by SET_WATERMARK
.
DBMS_AQADM.GET_WATERMARK ( wmvalue OUT NUMBER);
Parameter | Description |
---|---|
|
Watermark value in megabytes. |
This procedure grants privileges on a queue to users and roles. The privileges are ENQUEUE
or DEQUEUE
. Initially, only the queue table owner can use this procedure to grant privileges on the queues.
DBMS_AQADM.GRANT_QUEUE_PRIVILEGE ( privilege IN VARCHAR2, queue_name IN VARCHAR2, grantee IN VARCHAR2, grant_option IN BOOLEAN := FALSE);
This procedure grants Oracle Streams AQ system privileges to users and roles. The privileges are ENQUEUE_ANY
, DEQUEUE_ANY
, and MANAGE_ANY
. Initially, only SYS
and SYSTEM
can use this procedure successfully.
DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE ( privilege IN VARCHAR2, grantee IN VARCHAR2, admin_option IN BOOLEAN := FALSE);
This procedure upgrades an 8.0-compatible queue table to an 8.1-compatible or higher queue table, or downgrades an 8.1-compatible or higher queue table to an 8.0-compatible queue table.
DBMS_AQADM.MIGRATE_QUEUE_TABLE ( queue_table IN VARCHAR2, compatible IN VARCHAR2);
This procedure purges messages from queue tables. You can perform various purge operations on both single-consumer and multiconsumer queue tables for persistent queues.
DBMS_AQADM.PURGE_QUEUE_TABLE( queue_table IN VARCHAR2, purge_condition IN VARCHAR2, purge_options IN aq$_purge_options_t);
where type aq$_purge_options_t
is described in Chapter 171, "Oracle Streams AQ TYPEs".
You can purge selected messages from the queue table by specifying additional parameters in the API call. Table 17-27 describes these parameters. Messages can be enqueued to and dequeued from the queue table while the queue table is being purged.
A trace file is generated in the udump destination when you run this procedure. It details what the procedure is doing. The procedure commits after it has processed all the messages.
This function returns the subscribers to an 8.0-compatible multiconsumer queue in the PL/SQL index by table collection type DBMS_AQADM.AQ$_subscriber_list_t
. Each element of the collection is of type sys.aq$_agent
. This functionality is provided for 8.1-compatible queues by the AQ$
queue_table_name
_S
view.
DBMS_AQADM.QUEUE_SUBSCRIBERS ( queue_name IN VARCHAR2); RETURN aq$_subscriber_list_t IS
Parameter | Description |
---|---|
|
Specifies the queue whose subscribers are to be printed. |
This procedure removes a default subscriber from a queue. This operation takes effect immediately, and the containing transaction is committed. All references to the subscriber in existing messages are removed as part of the operation.
DBMS_AQADM.REMOVE_SUBSCRIBER ( queue_name IN VARCHAR2, subscriber IN sys.aq$_agent);
Parameter | Description |
---|---|
|
Name of the queue. |
|
Agent who is being removed. See "AQ$_AGENT Type". |
This procedure revokes privileges on a queue from users and roles. The privileges are ENQUEUE
or DEQUEUE
.
DBMS_AQADM.REVOKE_QUEUE_PRIVILEGE ( privilege IN VARCHAR2, queue_name IN VARCHAR2, grantee IN VARCHAR2);
To revoke a privilege, the revoker must be the original grantor of the privilege. The privileges propagated through the GRANT
option are revoked if the grantor's privileges are revoked.
This procedure revokes Oracle Streams AQ system privileges from users and roles. The privileges are ENQUEUE_ANY
, DEQUEUE_ANY
and MANAGE_ANY
. The ADMIN
option for a system privilege cannot be selectively revoked.
DBMS_AQADM.REVOKE_SYSTEM_PRIVILEGE ( privilege IN VARCHAR2, grantee IN VARCHAR2);
This procedure schedules propagation of messages from a queue to a destination identified by a specific database link.
DBMS_AQADM.SCHEDULE_PROPAGATION ( queue_name IN VARCHAR2, destination IN VARCHAR2 DEFAULT NULL, start_time IN DATE DEFAULT SYSDATE, duration IN NUMBER DEFAULT NULL, next_time IN VARCHAR2 DEFAULT NULL, latency IN NUMBER DEFAULT 60);
Messages may also be propagated to other queues in the same database by specifying a NULL
destination. If a message has multiple recipients at the same destination in either the same or different queues, then the message is propagated to all of them at the same time.
This procedure is used for Oracle Streams AQ notification to specify and limit memory use.
DBMS_AQADM.SET_WATERMARK ( wmvalue IN NUMBER);
Parameter | Description |
---|---|
|
Watermark value in megabytes. |
This procedure enables the specified queue for enqueuing or dequeuing.
DBMS_AQADM.START_QUEUE ( queue_name IN VARCHAR2, enqueue IN BOOLEAN DEFAULT TRUE, dequeue IN BOOLEAN DEFAULT TRUE);
After creating a queue, the administrator must use START_QUEUE
to enable the queue. The default is to enable it for both ENQUEUE
and DEQUEUE
. Only dequeue operations are allowed on an exception queue. This operation takes effect when the call completes and does not have any transactional characteristics.
This procedure disables enqueuing or dequeuing on the specified queue.
DBMS_AQADM.STOP_QUEUE ( queue_name IN VARCHAR2, enqueue IN BOOLEAN DEFAULT TRUE, dequeue IN BOOLEAN DEFAULT TRUE, wait IN BOOLEAN DEFAULT TRUE);
By default, this call disables both ENQUEUE
and DEQUEUE
. A queue cannot be stopped if there are outstanding transactions against the queue. This operation takes effect when the call completes and does not have any transactional characteristics.
This procedure unschedules previously scheduled propagation of messages from a queue to a destination identified by a specific database link.
DBMS_AQADM.UNSCHEDULE_PROPAGATION ( queue_name IN VARCHAR2, destination IN VARCHAR2 DEFAULT NULL);
This procedure verifies that the source and destination queues have identical types. The result of the verification is stored in the table sys
.aq$_message_types
, overwriting all previous output of this command.
DBMS_AQADM.VERIFY_QUEUE_TYPES ( src_queue_name IN VARCHAR2, dest_queue_name IN VARCHAR2, destination IN VARCHAR2 DEFAULT NULL, rc OUT BINARY_INTEGER);