Chapter 6. DBMS_AQADM
Prev   Next

Chapter 6. DBMS_AQADM

Table of Contents

6.1. Overview
6.2. Procedures
6.2.1. ADD_SUBSCRIBER
6.2.2. CREATE_QUEUE
6.2.3. CREATE_QUEUE_TABLE
6.2.4. DROP_QUEUE
6.2.5. DROP_QUEUE_TABLE
6.2.6. GRANT_SYSTEM_PRIVILEGE
6.2.7. REMOVE_SUBSCRIBER
6.2.8. REVOKE_SYSTEM_PRIVILEGE
6.2.9. START_QUEUE
6.2.10. STOP_QUEUE

This chapter briefly introduces the DBMS_AQADM package, and describes how to use the procedures of the package.

6.1. Overview

DBMS_AQADM provides procedures and functions used to configure and manage Tibero Advanced Queuing.

6.2. Procedures

This section describes the procedures provided by the DBMS_AQADM package, in alphabetical order.

6.2.1. ADD_SUBSCRIBER

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

    ParameterDescription
    queue_nameName of the queue to add the subscriber to.
    subscriberDBMS_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;
/

6.2.2. CREATE_QUEUE

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

    ParameterDescription
    queue_nameName of the queue to create. It must be unique within the schema. The same rule as for general object names applies.
    queue_tableName of the queue table to save the queue to.
    queue_commentUser-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;
/

6.2.3. CREATE_QUEUE_TABLE

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

    ParameterDescription
    queue_tableName of the queue table to create.
    queue_payload_typeType of user data to save. Currently, only the RAW type is supported.
    storage_clauseStorage 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

    • FALSE: create a queue table that stores single-consumer queues. (Default value)

    • TRUE: create a queue table that stores multiple-consumer queues.

    table_commentUser-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; /

6.2.4. DROP_QUEUE

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

    ParameterDescription
    queue_nameName of the queue to drop.

  • Example

    begin dbms_aqadm.stop_queue
          ('my_multi_q'); dbms_aqadm.drop_queue ('my_multi_q'); end;
          /

6.2.5. DROP_QUEUE_TABLE

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

    ParameterDescription
    queue_tableName of the queue table to drop.
    force

    • FALSE: do not drop the queue table if a queue exists in the table. (Default value)

    • TRUE: automatically stops and drops all queues in the queue table before dropping the table.

  • Example

    begin
          dbms_aqadm.drop_queue_table ('my_multi_qtbl', true); end;
          /

6.2.6. GRANT_SYSTEM_PRIVILEGE

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

    ParameterDescription
    privilege

    Tibero Advanced Queuing system privilege to grant.

    Options are:

    • ENQUEUE_ANY: a message is enqueued to any queue in the database.

    • DEQUEUE_ANY: a message can be dequeued from any queue in the database.

    • MANAGE_ANY: a DBMS_AQADM procedure can be executed for any schema object in the database.

    granteeTarget (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;
          /

6.2.7. REMOVE_SUBSCRIBER

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

    ParameterDescription
    queue_nameQueue name.
    subscriberAgent 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;
/

6.2.8. REVOKE_SYSTEM_PRIVILEGE

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

    ParameterDescription
    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.

    • ENQUEUE_ANY

    • DEQUEUE_ANY

    • MANAGE_ANY

    granteeTarget (user, role, or PUBLIC) from which to revoke the system privilege.

  • Example

    begin
          dbms_aqadm.revoke_system_privilege ('MANAGE_ANY', 'test_aq'); end;
          /

6.2.9. START_QUEUE

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

    ParameterDescription
    queue_nameName of the queue to activate.
    enqueue

    Option to enable enqueue.

    • TRUE: enable enqueue. (Default value)

    • FALSE: keep existing setting.

    dequeue

    Option to enable dequeue.

    • TRUE: enable dequeue. (Default value)

    • FALSE: keep existing setting.

  • 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; /

6.2.10. STOP_QUEUE

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

    ParameterDescription
    queue_nameName of the queue to inactivate.
    enqueue

    Option to disable enqueue.

    • TRUE: disable enqueue. (Default value)

    • FALSE: keep existing setting.

    dequeue

    Option to disable dequeue.

    • TRUE: disable dequeue. (Default value)

    • FALSE: keep existing setting.

    wait

    Option to wait if a transaction is being processed on the queue.

    • TRUE: wait until the transaction completes. Enqueue or dequeue is not executed on the queue. (Default value)

    • FALSE: raise an error and return immediately.

  • Example

    begin dbms_aqadm.stop_queue
          ('my_multi_q'); end; /
Prev   Next
Chapter 5. DBMS_AQ Home Chapter 7. DBMS_CRYPTO