| Oracle® Streams Advanced Queuing User's Guide and Reference Release 10.1 Part Number B10785-01 |
|
|
View PDF |
| Oracle® Streams Advanced Queuing User's Guide and Reference Release 10.1 Part Number B10785-01 |
|
|
View PDF |
This chapter describes the Oracle Streams Advanced Queuing (AQ) administrative interface.
This chapter contains these topics:
Deleting an Alias from the LDAP Server
|
See Also:
|
This section contains these topics:
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);
|
See Also: http://otn.oracle.com/docs/products/aq/doc_library/ojms/index.html for information on Oracle Java Message Service |
To create a queue table, you must specify:
Queue table name
Mixed case (upper and lower case together) queue table names are supported if database compatibility is 10.0, but the names must be enclosed in double quote marks. So abc.efg means the schema is ABC and the name is EFG, but "abc"."efg" means the schema is abc and the name is efg.
Payload type as RAW or an object type
To specify the payload type as an object type, you must define the object type.
CLOB, BLOB, and BFILE objects are valid in an Oracle Streams AQ message. You can propagate these object types using Oracle Streams AQ propagation with Oracle software since Oracle8i release 8.1.x. To enqueue an object type that has a LOB, you must first set the LOB_attribute to EMPTY_BLOB() and perform the enqueue. You can then select the LOB locator that was generated from the queue table's view and use the standard LOB operations.
|
Note: Payloads containing LOBs require users to grant explicitSelect, Insert and Update privileges on the queue table for doing enqueues and dequeues. |
Single-consumer or multiconsumer queue
Message grouping as none (default), or transactional
Primary instance and secondary instance
You can specify and modify 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.
Compatible as 8.0, 8.1, or 10.0
This parameter defaults to 8.0 if the database is in 8.0 compatible mode, 8.1 if the database is in 8.1 compatible mode, or 10.0 if the database is in 10.0 compatible mode.
Secure as TRUE or FALSE
This parameter must be set to TRUE if you want to use the queue table for secure queues. Secure queues are queues for which AQ agents must be associated explicitly with one or more database users who can perform queue operations, such as enqueue and dequeue. The owner of a secure queue can perform all queue operations on the queue, but other users cannot perform queue operations on a secure queue, unless they are configured as secure queue users.
Further, you may optionally:
Specify sort keys for dequeue ordering
Specify the storage clause (only if you do not want to use the default tablespace)
The storage_clause argument can take any text that can be used in a standard CREATE TABLE storage_clause argument.
Add a table description
Set auto-commit to true (default) or false
|
Note: Auto-commit has been deprecated. |
The sort type, if specified, can be one of the following:
Enqueue time (default for sort time)
Priority
Enqueue time by priority
Priority by enqueue time
The following objects are created at table creation time:
aq$_queue_table_name_e, the default exception queue associated with the queue table
aq$queue_table_name, a read-only view which is used by Oracle Streams AQ applications for querying queue data
aq$_queue_table_name_t, an index for the queue monitor operations
aq$_queue_table_name_i, an index or an index-organized table (IOT) in the case of multiple consumer queues for dequeue operations
For 8.1-compatible multiconsumer queue tables, the following additional objects are created:
aq$_queue_table_name_s, a table for storing information about subscribers
aq$_queue_table_name_h, an index organized table (IOT) for storing dequeue history data
If you do not specify a schema, then you default to the user's schema.
If GLOBAL_TOPIC_ENABLED = TRUE when a queue table is created, then a corresponding Lightweight Directory Access Protocol (LDAP) entry is also created.
You must set up the following data structures for certain examples to work:
CONNECT system/manager; DROP USER aqadm CASCADE; CREATE USER aqadm IDENTIFIED BY aqadm; GRANT CONNECT, RESOURCE TO aqadm; GRANT EXECUTE ON DBMS_AQADM TO aqadm; GRANT Aq_administrator_role TO aqadm; DROP USER aq CASCADE; CREATE USER aq IDENTIFIED BY aq; GRANT CONNECT, RESOURCE TO aq; GRANT EXECUTE ON dbms_aq TO aq;
Example 8-1 PL/SQL: Creating a Queue Table for Queues Containing Messages of Object Type
CREATE type aq.Message_typ as object ( Subject VARCHAR2(30), Text VARCHAR2(80)); EXECUTE DBMS_AQADM.CREATE_QUEUE_TABLE ( Queue_table => 'aq.ObjMsgs_qtab', Queue_payload_type => 'aq.Message_typ');
Example 8-2 PL/SQL: Creating a Queue Table for Queues Containing Messages of RAW Type
EXECUTE DBMS_AQADM.CREATE_QUEUE_TABLE ( Queue_table => 'aq.RawMsgs_qtab', Queue_payload_type => 'RAW');
Example 8-3 PL/SQL: Creating a Queue Table for Queues Containing Messages of XMLType
EXECUTE DBMS_AQADM.CREATE_QUEUE_TABLE(
queue_table => 'TS_orders_pr_mqtab',
comment => 'Overseas Shipping MultiConsumer Orders queue table',
multiple_consumers => TRUE,
queue_payload_type => 'SYS.XMLType',
compatible => '8.1');
Example 8-4 PL/SQL: Creating a Queue Table for Prioritized Messages
EXECUTE DBMS_AQADM.CREATE_QUEUE_TABLE ( Queue_table => 'aq.PriorityMsgs_qtab', Sort_list => 'PRIORITY,ENQ_TIME', Queue_payload_type => 'aq.Message_typ');
Example 8-5 PL/SQL: Creating a Queue Table for Multiple Consumers
EXECUTE DBMS_AQADM.CREATE_QUEUE_TABLE ( Queue_table => 'aq.MultiConsumerMsgs_qtab', Multiple_consumers => TRUE, Queue_payload_type => 'aq.Message_typ');
Example 8-6 PL/SQL: Creating a Queue Table for Multiple Consumers Compatible with 8.1
EXECUTE DBMS_AQADM.CREATE_QUEUE_TABLE ( Queue_table => 'aq.Multiconsumermsgs8_1qtab', Multiple_consumers => TRUE, Compatible => '8.1', Queue_payload_type => 'aq.Message_typ');
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);
To alter a queue table, you must name the queue table. You may optionally:
Add a comment
Specify the primary instance
The primary instance is the instance number of the primary owner of the queue table.
Specify the secondary instance
The secondary instance is the instance number of the secondary owner of the queue table.
If GLOBAL_TOPIC_ENABLED = TRUE when a queue table is modified, then a corresponding LDAP entry is also altered.
Example 8-9 PL/SQL (DBMS_AQADM Package): Altering a Queue Table by Changing the Primary and Secondary Instances
EXECUTE DBMS_AQADM.ALTER_QUEUE_TABLE ( Queue_table => 'aq.ObjMsgs_qtab', Primary_instance => 3, Secondary_instance => 2);
Drops an existing queue table. You must stop and drop all the queues in a queue table before the queue table can be dropped. You must do this explicitly unless the force option is used, in which case these operations are accomplished automatically.
DBMS_AQADM.DROP_QUEUE_TABLE ( queue_table IN VARCHAR2, force IN BOOLEAN DEFAULT FALSE, auto_commit IN BOOLEAN DEFAULT TRUE);
|
Note: Parameterauto_commit is deprecated. |
If GLOBAL_TOPIC_ENABLED = TRUE when a queue table is dropped, then a corresponding LDAP entry is also dropped.
You must set up or drop data structures for certain examples to work.
Purges messages from a queue table.
DBMS_AQADM.PURGE_QUEUE_TABLE( queue_table IN VARCHAR2, purge_condition IN VARCHAR2, purge_options IN aq$_purge_options_t);
You can perform various purge operations on both single-consumer and multiconsumer queue tables for persistent queues. You can purge selected messages from the queue table by specifying additional parameters in the API call.
The purge condition must be in the format of a SQL WHERE clause, and it is case-sensitive. The condition is based on the columns of aq$queue_table view.
To purge all queues in a queue table, set purge_condition to either NULL (a bare null word, no quotes) or '' (two single quotes).
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.
|
See Also: "DBMS_AQADM" in PL/SQL Packages and Types Reference for more information onDBMS_AQADM.PURGE_QUEUE_TABLE |
Example 8-14 Purging All Messages in Queue Table tkaqqtdef
connect tkaqadmn/tkaqadmn
declare
po dbms_aqadm.aq$_purge_options_t;
begin
po.block := FALSE;
dbms_aqadm.purge_queue_table(
queue_table => 'tkaqqtdef',
purge_condition => NULL,
purge_options => po);
end;
/
Example 8-15 Purging All Messages in Queue Table tkaqqtdef That Correspond to Queue q1def
connect tkaqadmn/tkaqadmn
declare
po dbms_aqadm.aq$_purge_options_t;
begin
po.block := TRUE;
dbms_aqadm.purge_queue_table(
queue_table => 'tkaqqtdef',
purge_condition => 'queue = ''Q1DEF''',
purge_options => po);
end;
/
Example 8-16 Purging All Messages in Queue Table tkaqqtdef That Correspond to Queue q1def and Are in the PROCESSED State
connect tkaqadmn/tkaqadmn
declare
po dbms_aqadm.aq$_purge_options_t;
begin
po.block := TRUE;
dbms_aqadm.purge_queue_table(
queue_table => 'tkaqqtdef',
purge_condition => 'queue = ''Q1DEF'' and msg_state = ''PROCESSED''',
purge_options => po);
end;
/
Example 8-17 Purging All Messages in Queue Table tkaqqtdef That Correspond to Queue q1def and Are Intended for Consumer PAYROLL_APP
connect tkaqadmn/tkaqadmn
declare
po dbms_aqadm.aq$_purge_options_t;
begin
po.block := TRUE;
dbms_aqadm.purge_queue_table(
queue_table => 'tkaqqtdef',
purge_condition => 'queue = ''Q1DEF'' and consumer_name = ''PAYROLL_APP''',
purge_options => po);
end;
/
Example 8-18 Purging All Messages in Queue Table tkaqqtdef That Correspond to Sender Name PAYROLL_APP
connect tkaqadmn/tkaqadmn
declare
po dbms_aqadm.aq$_purge_options_t;
begin
po.block := TRUE;
dbms_aqadm.purge_queue_table(
queue_table => 'tkaqqtdef',
purge_condition => 'sender_name = ''PAYROLL_APP''',
purge_options => po);
end;
/
Example 8-19 Purging All Messages in Queue Table tkaqqtdef Where tab.city Is BELMONT
connect tkaqadmn/tkaqadmn
declare
po dbms_aqadm.aq$_purge_options_t;
begin
po.block := TRUE;
dbms_aqadm.purge_queue_table(
queue_table => 'tkaqqtdef',
purge_condition => 'tab.city = ''BELMONT''',
purge_options => po);
end;
/
Example 8-20 urging All Messages in Queue Table tkaqqtdef That Were Enqueued Before January 1, 2002
connect tkaqadmn/tkaqadmn
declare
po dbms_aqadm.aq$_purge_options_t;
begin
po.block := TRUE;
dbms_aqadm.purge_queue_table(
queue_table => 'tkaqqtdef',
purge_condition => 'enq_time < ''01-JAN-2002''',
purge_options => po);
end;
/
Migrating a queue table from 8.0, 8.1, or 10.0 to 8.0, 8.1, or 10.0.
DBMS_AQADM.MIGRATE_QUEUE_TABLE ( queue_table IN VARCHAR2, compatible IN VARCHAR2);
If a schema was created by an import of an export dump from a lower release or has Oracle Streams AQ queues upgraded from a lower release, then attempts to drop it with DROP USER CASCADE will fail with ORA-24005. To drop such schemas:
Event 10851 should be set to level 1.
Drop all tables of the form AQ$_queue_table_name_NR from the schema.
Turn off event 10851.
Drop the schema.
This section contains these topics:
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);
|
Note: Parameterauto_commit is deprecated. |
Mixed case (upper and lower case together) queue names and queue table names are supported if database compatibility is 10.0, but the names must be enclosed in double quote marks. So abc.efg means the schema is ABC and the name is EFG, but "abc"."efg" means the schema is abc and the name is efg.
All queue names must be unique within a schema. Once 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. To view retained messages, you can either dequeue by message ID or use SQL. If GLOBAL_TOPIC_ENABLED = TRUE when a queue is created, then a corresponding LDAP entry is also created.
You must set up or drop data structures for certain examples to work.
Example 8-21 PL/SQL: Creating a Queue Within a Queue Table for Messages of Object Type
/* Create a message type: */ CREATE type aq.Message_typ as object ( Subject VARCHAR2(30), Text VARCHAR2(80)); /* Create a object type queue table and queue: */ EXECUTE DBMS_AQADM.CREATE_QUEUE_TABLE ( Queue_table => 'aq.ObjMsgs_qtab', Queue_payload_type => 'aq.Message_typ'); EXECUTE DBMS_AQADM.CREATE_QUEUE ( Queue_name => 'msg_queue', Queue_table => 'aq.ObjMsgs_qtab');
Example 8-22 PL/SQL: Creating a Queue Within a Queue Table for Messages of RAW Type
/* Create a RAW type queue table and queue: */ EXECUTE DBMS_AQADM.CREATE_QUEUE_TABLE ( Queue_table => 'aq.RawMsgs_qtab', Queue_payload_type => 'RAW'); /* Create queue: */ EXECUTE DBMS_AQADM.CREATE_QUEUE ( Queue_name => 'raw_msg_queue', Queue_table => 'aq.RawMsgs_qtab');
Example 8-23 PL/SQL: Creating a Queue for Prioritized Messages
/* Create a queue table for prioritized messages: */ EXECUTE DBMS_AQADM.CREATE_QUEUE_TABLE ( Queue_table => 'aq.PriorityMsgs_qtab', Sort_list => 'PRIORITY,ENQ_TIME', Queue_payload_type => 'aq.Message_typ'); /* Create queue: */ EXECUTE DBMS_AQADM.CREATE_QUEUE ( Queue_name => 'priority_msg_queue', Queue_table => 'aq.PriorityMsgs_qtab');
Example 8-24 PL/SQL: Creating a Queue Table and Queue for Multiple Consumers
/* Create a multiconsumer queue table: */ EXECUTE DBMS_AQADM.CREATE_QUEUE_TABLE ( queue_table => 'aq.MultiConsumerMsgs_qtab', Multiple_consumers => TRUE, Queue_payload_type => 'aq.Message_typ'); /* Create queue: */ EXECUTE DBMS_AQADM.CREATE_QUEUE ( Queue_name => 'MultiConsumerMsg_queue', Queue_table => 'aq.MultiConsumerMsgs_qtab');
Example 8-25 PL/SQL: Creating a Queue Table and Queue to Demonstrate Propagation
/* Create queue: */ EXECUTE DBMS_AQADM.CREATE_QUEUE ( Queue_name => 'AnotherMsg_queue', queue_table => 'aq.MultiConsumerMsgs_qtab');
Example 8-26 PL/SQL: Creating a Queue Table and Queue for Multiple Consumers Compatible with 8.1
/* Create a multiconsumer queue table compatible with Release 8.1: */ EXECUTE DBMS_AQADM.CREATE_QUEUE_TABLE ( Queue_table => 'aq.MultiConsumerMsgs81_qtab', Multiple_consumers => TRUE, Compatible => '8.1', Queue_payload_type => 'aq.Message_typ'); /* Create queue: */ EXECUTE DBMS_AQADM.CREATE_QUEUE ( Queue_name => 'MultiConsumerMsg81_queue', Queue_table => 'aq.MultiConsumerMsgs81_qtab');
Creates a nonpersistent queue.
DBMS_AQADM.CREATE_NP_QUEUE ( queue_name IN VARCHAR2, multiple_consumers IN BOOLEAN DEFAULT FALSE, comment IN VARCHAR2 DEFAULT NULL);
The queue can be either single-consumer or multiconsumer. All queue names must be unique within a schema. The queues are created in a 8.1-compatible 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, then the queue is created in the login user's schema.
Once 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 can enqueue RAW and Oracle object type messages into a nonpersistent queue. You cannot dequeue from a nonpersistent queue. The only way to retrieve a message from a nonpersistent queue is by using the Oracle Call Interface (OCI) notification mechanism. You cannot invoke the listen call on a nonpersistent queue.
Alters existing properties of a queue.
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);
|
Note: Parameterauto_commit is deprecated. |
Only max_retries, comment, retry_delay, and retention_time can be altered. To view retained messages, you can either dequeue by message ID or use SQL. If GLOBAL_TOPIC_ENABLED = TRUE when a queue is modified, then a corresponding LDAP entry is also altered.
Drops an existing queue. 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.
DBMS_AQADM.DROP_QUEUE ( queue_name IN VARCHAR2, auto_commit IN BOOLEAN DEFAULT TRUE);
|
Note: Parameterauto_commit is deprecated. |
If GLOBAL_TOPIC_ENABLED = TRUE when a queue is dropped, then a corresponding LDAP entry is also dropped.
You must stop the queue before dropping it. A queue can be dropped only after it has been successfully stopped for enqueuing and dequeuing.
Enables the specified queue for enqueuing or dequeuing.
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.
DBMS_AQADM.START_QUEUE ( queue_name IN VARCHAR2, enqueue IN BOOLEAN DEFAULT TRUE, dequeue IN BOOLEAN DEFAULT TRUE);
Disables enqueuing, dequeuing, or both 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 section contains these topics:
Creates a message format transformation. The transformation must be a SQL function with input type from_type, returning an object of type to_type. It can also be a SQL expression of type to_type, referring to from_type. All references to from_type must be of the form source.user_data.
DBMS_TRANSFORM.CREATE_TRANSFORMATION (
schema VARCHAR2(30),
name VARCHAR2(30),
from_schema VARCHAR2(30),
from_type VARCHAR2(30),
to_schema VARCHAR2(30),
to_type VARCHAR2(30),
transformation VARCHAR2(4000));
You must be granted EXECUTE privileges on dbms_transform to use this feature. You must also have EXECUTE privileges on the user-defined types that are the source and destination types of the transformation, and have EXECUTE privileges on any PL/SQL function being used in the transformation function. The transformation cannot write the database state (that is, perform DML operations) or commit or rollback the current transaction.
Example 8-35 PL/SQL (DBMS_AQADM): Creating a Transformation
DBMS_TRANSFORM.CREATE_TRANSFORMATION(schema => 'scott',
name => 'test_transf', from_schema => 'scott',
from_type => 'type1', to_schema => 'scott',
to_type => 'type2',
transformation => 'scott.trans_func(source.user_data)');
Or you can do the following:
DBMS_TRANSFORM.CREATE_TRANSFORMATION(schema => 'scott',
name => 'test_transf',
from_schema => 'scott',
from_type => 'type1,
to_schema => 'scott',
to_type => 'type2',
transformation => 'scott.type2(source.user_data.attr2,
source.user_data.attr1)');
Changes the transformation function and specifies transformations for each attribute of the target type. If the attribute number 0 is specified, then the transformation expression singularly defines the transformation from the source to target types.
All references to from_type must be of the form source.user_data. All references to the attributes of the source type must be prefixed by source.user_data.
DBMS_TRANSFORM.MODIFY_TRANSFORMATION (
schema VARCHAR2(30),
name VARCHAR2(30),
attribute_number INTEGER,
transformation VARCHAR2(4000));
You must be granted EXECUTE privileges on dbms_transform to use this feature. You must also have EXECUTE privileges on the user-defined types that are the source and destination types of the transformation, and have EXECUTE privileges on any PL/SQL function being used in the transformation function.
Drops a transformation.
DBMS_TRANSFORM.DROP_TRANSFORMATION (
schema VARCHAR2(30),
name VARCHAR2(30));
You must be granted EXECUTE privileges on dbms_transform to use this feature. You must also have EXECUTE privileges on the user-defined types that are the source and destination types of the transformation, and have EXECUTE privileges on any PL/SQL function being used in the transformation function.
This section contains these topics:
Grants Oracle Streams AQ system privileges to users and roles. The privileges are ENQUEUE_ANY, DEQUEUE_ANY, 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);
Users granted the ENQUEUE_ANY privilege are allowed to enqueue messages to any queues in the database. Users granted the DEQUEUE_ANY privilege are allowed to dequeue messages from any queues in the database. Users granted the MANAGE_ANY privilege are allowed to run DBMS_AQADM calls on any schemas in the database.
You must set up the following data structures for this example to work:
CONNECT system/manager; CREATE USER aqadm IDENTIFIED BY aqadm; GRANT CONNECT, RESOURCE TO aqadm; GRANT EXECUTE ON DBMS_AQADM TO aqadm; GRANT Aq_administrator_role TO aqadm;
Example 8-36 PL/SQL (DBMS_AQADM): Granting System Privilege
CONNECT aqadm/aqadm; EXECUTE DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE( privilege => 'ENQUEUE_ANY', grantee => 'Jones', admin_option => FALSE); EXECUTE DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE( privilege => 'DEQUEUE_ANY', grantee => 'Jones', admin_option => FALSE);
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);
Users granted the ENQUEUE_ANY privilege are allowed to enqueue messages to any queues in the database. Users granted the DEQUEUE_ANY privilege are allowed to dequeue messages from any queues in the database. Users granted the MANAGE_ANY privilege are allowed to run DBMS_AQADM calls on any schemas in the database.
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);
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.
You can revoke the dequeue right of a grantee on a specific queue, leaving the grantee with only the enqueue right as in Example 8-39.
This section contains these topics:
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 succeeds only 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 reflect the new action. Any string within the rule must be quoted (with single quotation marks) as follows:
rule => 'PRIORITY <= 3 AND CORRID = ''FROM JAPAN'''
If GLOBAL_TOPIC_ENABLED is set to true when a subscriber is created, then a corresponding LDAP entry is also created.
Specify the name of the transformation to be applied during dequeue or propagation. The transformation must be created using the DBMS_TRANSFORM package.
For queues that contain payloads with XMLType attributes, you can specify rules that contain operators such as XMLType.existsNode() and XMLType.extract().
|
Note: ADD_SUBSCRIBER is an administrative operation on a queue. Although Oracle Streams AQ does not prevent applications from issuing administrative and operational calls concurrently, they are executed serially. ADD_SUBSCRIBER blocks until pending transactions that have enqueued or dequeued messages commit and release the resources they hold. |
Example 8-40 PL/SQL (DBMS_AQADM): Adding a Subscriber
/* Anonymous PL/SQL block for adding a subscriber at a designated queue in a designated schema at a database link: */
DECLARE
subscriber sys.aq$_agent;
BEGIN
subscriber := sys.aq$_agent ('subscriber1', 'aq2.msg_queue2@london', null);
DBMS_AQADM.ADD_SUBSCRIBER(
queue_name => 'aq.multi_queue',
subscriber => subscriber);
END;
Example 8-41 PL/SQL (DBMS_AQADM): Adding a Subscriber with a Rule
DECLARE
subscriber sys.aq$_agent;
BEGIN
subscriber := sys.aq$_agent('subscriber2', 'aq2.msg_queue2@london', null);
DBMS_AQADM.ADD_SUBSCRIBER(
queue_name => 'aq.multi_queue',
subscriber => subscriber,
rule => 'priority < 2');
END;
Example 8-42 PL/SQL: Adding a Subscriber and Specify a Transformation
DECLARE
subscriber sys.aq$_agent;
BEGIN
subscriber := sys.aq$_agent('subscriber2', 'aq2.msg_queue2@london', null);
DBMS_AQADM.ADD_SUBSCRIBER(
queue_name => 'aq.multi_queue',
subscriber => subscriber,
transformation => 'AQ.msg_map');
/* Where the transformation was created as */
EXECUTE DBMS_TRANSFORM.CREATE_TRANSFORMATION
( schema => 'AQ',
name => 'msg_map',
from_schema => 'AQ',
from_type => 'purchase_order1',
to_schema => 'AQ',
to_type => 'purchase_order2',
transformation => 'AQ.transform_PO(source.user_data)');
END;
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);
The rule, the transformation, or both can be altered. If you alter only one of these attributes, then specify the existing value of the other attribute to the alter call. If GLOBAL_TOPIC_ENABLED = TRUE when a subscriber is modified, then a corresponding LDAP entry is created.
You must set up the following data structures for the examples in this section to work:
EXECUTE DBMS_AQADM.CREATE_QUEUE_TABLE ( queue_table => 'aq.multi_qtab', multiple_consumers => TRUE, queue_payload_type => 'aq.message_typ', compatible => '8.1.5'); EXECUTE DBMS_AQADM.CREATE_QUEUE ( queue_name => 'multi_queue', queue_table => 'aq.multi_qtab');
Example 8-43 PL/SQL: Altering a Subscriber Rule
DECLARE
subscriber sys.aq$_agent;
BEGIN
subscriber := sys.aq$_agent('SUBSCRIBER1', 'aq2.msg_queue2@london', null);
DBMS_AQADM.ADD_SUBSCRIBER(
queue_name => 'aq.msg_queue',
subscriber => subscriber,
rule => 'priority < 2');
END;
/* Change rule for subscriber: */
DECLARE
subscriber sys.aq$_agent;
BEGIN
subscriber := sys.aq$_agent('SUBSCRIBER1', 'aq2.msg_queue2@london', null);
DBMS_AQADM.ALTER_SUBSCRIBER(
queue_name => 'aq.msg_queue',
subscriber => subscriber,
rule => 'priority = 1');
END;
Example 8-44 PL/SQL: Altering a Subscriber Transformation
EXECUTE DBMS_AQADM.ADD_SUBSCRIBER
('aq.msg_queue',
aq$_agent('subscriber1',
'aq2.msg_queue2@london',
null),
'AQ.MSG_MAP1');
/* Alter the subscriber*/
EXECUTE DBMS_AQADM.ALTER_SUBSCRIBER
('aq.msg_queue',
aq$_agent ('subscriber1',
'aq2.msg_queue2@london',
null),
'AQ.MSG.MAP2');
Removes a default subscriber from a queue.
DBMS_AQADM.REMOVE_SUBSCRIBER ( queue_name IN VARCHAR2, subscriber IN sys.aq$_agent);
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. If GLOBAL_TOPIC_ENABLED = TRUE when a subscriber is dropped, then a corresponding LDAP entry is also dropped.
|
Note: REMOVE_SUBSCRIBER is an administrative operation on a queue. Although Oracle Streams AQ does not prevent applications from issuing administrative and operational calls concurrently, they are executed serially. REMOVE_SUBSCRIBER blocks until pending transactions that have enqueued or dequeued messages commit and release the resources they hold. |
This section contains these topics:
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 can 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.
You must set up the following data structures for the examples in this section to work:
EXECUTE DBMS_AQADM.CREATE_QUEUE_TABLE ( queue_table => 'aq.objmsgs_qtab', queue_payload_type => 'aq.message_typ', multiple_consumers => TRUE); EXECUTE DBMS_AQADM.CREATE_QUEUE ( queue_name => 'aq.q1def', queue_table => 'aq.objmsgs_qtab');
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);
Verifies that the source and destination queues have identical types. The result of the verification is stored in sys.aq$_Message_types tables, 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);
Verify that the source and destination queues have the same type. The function has the side effect of inserting/updating the entry for the source and destination queues in the dictionary table AQ$_MESSAGE_TYPES.
If the source and destination queues do not have identical types and a transformation was specified, then the transformation must map the source queue type to the destination queue type.
|
Note: Thesys.aq$_message_types table can have multiple entries for the same source queue, destination queue, and database link, but with different transformations. |
You must set up the following data structures for this example to work:
EXECUTE DBMS_AQADM.CREATE_QUEUE ( queue_name => 'aq.q2def', queue_table => 'aq.objmsgs_qtab');
Example 8-50 PL/SQL (DBMS_AQADM): Verifying a Queue Type
/* Verify that the source and destination queues have the same type. */
DECLARE
rc BINARY_INTEGER;
BEGIN
/* Verify that the queues aquser.q1def and aquser.q2def in the local database
have the same payload type */
DBMS_AQADM.VERIFY_QUEUE_TYPES(
src_queue_name => 'aq.q1def',
dest_queue_name => 'aq.q2def',
rc => rc);
DBMS_OUTPUT.PUT_LINE(rc);
END;
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);
Enables a previously disabled propagation schedule.
DBMS_AQADM.ENABLE_PROPAGATION_SCHEDULE ( queue_name IN VARCHAR2, destination IN VARCHAR2 DEFAULT NULL);
Disables a previously enabled propagation schedule.
DBMS_AQADM.DISABLE_PROPAGATION_SCHEDULE ( queue_name IN VARCHAR2, destination IN VARCHAR2 DEFAULT NULL);
This section contains these topics:
Registers an agent for Oracle Streams AQ Internet access using HTTP protocols.
DBMS_AQADM.CREATE_AQ_AGENT ( agent_name IN VARCHAR2, certificate_location IN VARCHAR2 DEFAULT NULL, enable_http 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. When an agent is created, altered, or dropped, an LDAP entry is created for the agent if the following are true:
GLOBAL_TOPIC_ENABLED = TRUE
certificate_location is specified
Alters an agent registered for Oracle Streams AQ Internet access.
DBMS_AQADM.ALTER_AQ_AGENT ( agent_name IN VARCHAR2, certificate_location IN VARCHAR2 DEFAULT NULL, enable_http IN BOOLEAN DEFAULT FALSE, enable_anyp IN BOOLEAN DEFAULT FALSE )
When an Oracle Streams AQ agent is created, altered, or dropped, an LDAP entry is created for the agent if the following are true:
GLOBAL_TOPIC_ENABLED = TRUE
certificate_location is specified
Drops an agent that was previously registered for Oracle Streams AQ Internet access.
DBMS_AQADM.DROP_AQ_AGENT ( agent_name IN VARCHAR2)
When an Oracle Streams AQ agent is created, altered, or dropped, an LDAP entry is created for the agent if the following are true:
GLOBAL_TOPIC_ENABLED = TRUE
certificate_location is specified
Grants an Oracle Streams AQ Internet agent the privileges of a specific database user. The agent should have been previously created using the CREATE_AQ_AGENT procedure.
DBMS_AQADM.ENABLE_DB_ACCESS ( agent_name IN VARCHAR2, db_username IN VARCHAR2)
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.
Revokes the privileges of a specific database user from an Oracle Streams AQ Internet agent. The agent should have been previously granted those privileges using the ENABLE_DB_ACCESS procedure.
DBMS_AQADM.DISABLE_DB_ACCESS ( agent_name IN VARCHAR2, db_username IN VARCHAR2)
Adds an alias to the LDAP server.
DBMS_AQADM.ADD_ALIAS_TO_LDAP( alias IN VARCHAR2, obj_location IN VARCHAR2);
This call takes the name of an alias and the distinguished name of an Oracle Streams AQ object in LDAP, and creates the alias that points to the Oracle Streams AQ object. The alias is placed immediately under the distinguished name of the database server. The object to which the alias points can be a queue, an agent, or a connection factory.
Removes an alias from the LDAP server.
DBMS_AQ.DEL_ALIAS_FROM_LDAP( alias IN VARCHAR2);
This call takes the name of an alias as the argument, and removes the alias entry in the LDAP server. It is assumed that the alias is placed immediately under the database server in the LDAP directory.
|
 Copyright © 1996, 2003 Oracle Corporation All Rights Reserved. |
|