Table of Contents
This chapter briefly introduces the DBMS_AQADM package, and describes how to use the procedures of the package.
This section describes the procedures provided by the DBMS_AQADM package, in alphabetical order.
Adds a default subscriber to the target queue. A message can be enqueued to be delivered to a list of receivers or default subscribers.
This procedure is used only for multiple-consumer queues. If it is executed, its transaction is committed. If the execution is successful, any subsequent enqueue will include the newly added subscriber.
Details about the ADD_SUBSCRIBER procedure are as follows:
Prototype
DBMS_AQADM.ADD_SUBSCRIBER ( queue_name IN VARCHAR2, subscriber IN DBMS_AQ.AQ$_AGENT );
Parameter
Parameter | Description |
---|---|
queue_name | Name of the queue to add the subscriber to. |
subscriber | DBMS_AQ.AQ$_AGENT agent that specifies the subscriber. |
Example
DECLARE subscriber dbms_aq.aq$_agent; BEGIN subscriber.name := 'RED'; DBMS_AQADM.ADD_SUBSCRIBER( queue_name => 'my_multi_q', subscriber => subscriber); END; /
Creates a queue in the target queue table in inactive enqueue/dequeue state by default. A queue created using CREATE_QUEUE can be activated using the START_QUEUE procedure.
Details about the CREATE_QUEUE procedure are as follows:
Prototype
DBMS_AQADM.CREATE_QUEUE ( queue_name IN VARCHAR2, queue_table IN VARCHAR2, queue_comment IN VARCHAR2 DEFAULT NULL );
Parameter
Parameter | Description |
---|---|
queue_name | Name of the queue to create. It must be unique within the schema. The same rule as for general object names applies. |
queue_table | Name of the queue table to save the queue to. |
queue_comment | User-specified comment about the queue to create. It can be queried from a static view. |
Example
begin dbms_aqadm.create_queue_table ('my_qtbl', 'RAW', multiple_consumers=>false); dbms_aqadm.create_queue ('my_q', 'my_qtbl'); end; /
Creates a queue table. A sort key that determines the dequeue order can be specified.
Details about the CREATE_QUEUE_TABLE procedure are as follows:
Prototype
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 'ENQ_TIME', multiple_consumers IN BOOLEAN DEFAULT FALSE, table_comment IN VARCHAR2 DEFAULT NULL );
Parameter
Parameter | Description |
---|---|
queue_table | Name of the queue table to create. |
queue_payload_type | Type of user data to save. Currently, only the RAW type is supported. |
storage_clause | Storage parameters used when executing the CREATE TABLE statement. |
sort_list | Sort key column used to sort messages in ascending order. If not set, messages are sorted in the enqueued order like FIFO. (Default value: ENQ_TIME) This parameter is specified in the following format. Available column names are PRIORITY and ENQ_TIME. If both columns are specified, priority is given to the column that appears first. sort_column_1[,sort_column_2]
If a queue table is created with a sort order, the order cannot be changed and all queues created in the queue table have the same sort order. However, a msgid or correlation can be specified during dequeue to override this setting to dequeue a specific message first. |
multiple_consumers |
|
table_comment | User-specified comment about the queue to create. It can be queried through a static view. |
Example
begin dbms_aqadm.create_queue_table ('my_multi_qtbl', 'RAW', sort_list=> 'PRIORITY,ENQ_TIME', multiple_consumers=>true); end; /
Drops an existing queue. To execute this procedure, both enqueue and dequeue must be inactivated using the STOP_QUEUE procedure. When a queue is dropped, all data in the queue is deleted.
Details about the DROP_QUEUE procedure are as follows:
Prototype
DBMS_AQADM.DROP_QUEUE ( queue_name IN VARCHAR2 );
Parameter
Parameter | Description |
---|---|
queue_name | Name of the queue to drop. |
Example
begin dbms_aqadm.stop_queue ('my_multi_q'); dbms_aqadm.drop_queue ('my_multi_q'); end; /
Drops an existing queue table. To execute this procedure, all queues in the queue table must be stopped and dropped, or the force option must be used to stop and drop all the queues.
Details about the DROP_QUEUE_TABLE procedure are as follows:
Prototype
DBMS_AQADM.DROP_QUEUE_TABLE ( queue_table IN VARCHAR2, force IN BOOLEAN DEFAULT FALSE );
Parameter
Parameter | Description |
---|---|
queue_table | Name of the queue table to drop. |
force |
|
Example
begin dbms_aqadm.drop_queue_table ('my_multi_qtbl', true); end; /
Grants a Tibero Advanced Queuing system privilege to a user or role. Available privileges are ENQUEUE_ANY, DEQUEUE_ANY, and MANAGE_ANY. Initially, a SYS user or a user who has a DBA role can execute this procedure.
The EXECUTE privilege for DBMS_AQADM is required to execute procedures of the DBMS_AQADM package. Similarly, the EXECUTE privilege for DBMS_AQ package is required to execute enqueue and dequeue.
If an object processed by a DBMS_AQADM procedure is included in the schema that called the procedure, only the EXECUTE privilege for the package is required to execute the procedure. Similarly, only the EXECUTE privilege for DBMS_AQ is required to execute enqueue and dequeue on a queue that is created in the schema of the DBMS_AQ procedure.
Details about the GRANT_SYSTEM_PRIVILEGE procedure are as follows:
Prototype
DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE ( privilege IN VARCHAR2, grantee IN VARCHAR2, admin_option IN BOOLEAN DEFAULT FALSE );
Parameter
Parameter | Description |
---|---|
privilege | Tibero Advanced Queuing system privilege to grant. Options are:
|
grantee | Target (user, role, and PUBLIC) to which a privilege is granted. |
admin_option | Option to grant the system privilege with the ADMIN option. A grantee who is granted the system privilege with the ADMIN option can grant the privilege to another user or role by calling this procedure. (Default value: FALSE) |
Example
begin dbms_aqadm.grant_system_privilege ('MANAGE_ANY', 'test_aq'); end; /
Removes a default subscriber from the target queue. When this procedure is executed, its transaction is committed. If the execution is successful, messages and information related to the removed subscriber are also removed.
Details about the REMOVE_SUBSCRIBER procedure are as follows:
Prototype
DBMS_AQADM.REMOVE_SUBSCRIBER ( queue_name IN VARCHAR2, subscriber IN DBMS_AQ.AQ$_AGENT );
Parameter
Parameter | Description |
---|---|
queue_name | Queue name. |
subscriber | Agent to remove. (Type: DBMS_AQ.AQ$_AGENT) |
Example
DECLARE subscriber dbms_aq.aq$_agent; BEGIN subscriber.name := 'RED'; DBMS_AQADM.remove_subscriber ( queue_name => 'my_multi_q', subscriber => subscriber); END; /
Revokes the Tibero Advanced Queuing system privilege. Revocable privileges are ENQUEUE_ANY, DEQUEUE_ANY, and MANAGE_ANY.
Details about the REVOKE_SYSTEM_PRIVILEGE procedure are as follows:
Prototype
DBMS_AQADM.REVOKE_SYSTEM_PRIVILEGE ( privilege IN VARCHAR2, grantee IN VARCHAR2 );
Parameter
Parameter | Description |
---|---|
privilege | Tibero Advanced Queuing system privilege to revoke. The following privileges are revocable. The ADMIN option cannot be revoked selectively without the revoking the system privilege.
|
grantee | Target (user, role, or PUBLIC) from which to revoke the system privilege. |
Example
begin dbms_aqadm.revoke_system_privilege ('MANAGE_ANY', 'test_aq'); end; /
Activates a queue which enables enqueue and dequeue by default. After a queue is created, it must be activated using this procedure. The procedure activates the queue and enables enqueue and dequeue. The result is reflected immediately after execution, but it does not affect any transactions that include this procedure.
Details about the START_QUEUE procedure are as follows:
Prototype
DBMS_AQADM.START_QUEUE ( queue_name IN VARCHAR2, enqueue IN BOOLEAN DEFAULT TRUE, dequeue IN BOOLEAN DEFAULT TRUE );
Parameter
Parameter | Description |
---|---|
queue_name | Name of the queue to activate. |
enqueue | Option to enable enqueue.
|
dequeue | Option to enable dequeue.
|
Example
begin dbms_aqadm.create_queue_table ('my_qtbl', 'RAW', multiple_consumers=>false); dbms_aqadm.create_queue ('my_q', 'my_qtbl'); dbms_aqadm.start_queue ('my_q'); end; /
Inactivates a queue which disables enqueue and dequeue by default. A queue cannot be inactivated while a transaction is being processed on the queue. A queue is inactivated and enqueue and dequeue are disabled immediately after this procedure completes, and this does not affect any transactions that use this procedure.
Details about the STOP_QUEUE procedure are as follows:
Prototype
DBMS_AQADM.STOP_QUEUE ( queue_name IN VARCHAR2, enqueue IN BOOLEAN DEFAULT TRUE, dequeue IN BOOLEAN DEFAULT TRUE, wait IN BOOLEAN DEFAULT TRUE );
Parameter
Parameter | Description |
---|---|
queue_name | Name of the queue to inactivate. |
enqueue | Option to disable enqueue.
|
dequeue | Option to disable dequeue.
|
wait | Option to wait if a transaction is being processed on the queue.
|
Example
begin dbms_aqadm.stop_queue ('my_multi_q'); end; /