Search Results revoke_queue_privilege

Overview

SYS.DBMS_AQADM is the Oracle Advanced Queuing (AQ) administrative package supplied by the database and exposed within the Oracle E-Business Suite 12.1.1 and 12.2.2 environments. It provides the programmatic interface through which queue tables, queues, subscribers, and privileges are created, altered, and removed. In the EBS context, DBMS_AQADM is the administrative half of the AQ feature set; the companion package DBMS_AQ performs the enqueue and dequeue operations. Business integrations such as Oracle Workflow Business Events, Oracle XML Gateway, Advanced Supply Chain Planning data propagation, and numerous custom interfaces rely on AQ queues configured through this package. Because the package is owned by SYS and classified as OTHER in the ETRM repository, it is a database-supplied API rather than an EBS-owned PL/SQL object, and its procedures are invoked through APPS synonyms rather than re-implemented by the applications schema.

Key Procedures and Functions

The ETRM metadata documents sixty-one procedures and functions in DBMS_AQADM. Among these, the queue table lifecycle procedures form the foundation of any AQ configuration.

• CREATE_QUEUE_TABLE — Creates the underlying database table that stores messages, specifying payload type, sort order, and storage characteristics. This is the object referenced when a user searches for "create_queue_table".
• ALTER_QUEUE_TABLE — Modifies an existing queue table, for example to change message grouping, retention, or sort order.
• DROP_QUEUE_TABLE — Removes a queue table and its associated queues.
• CREATE_QUEUE and CREATE_NP_QUEUE — Create persistent and non-persistent queues respectively on an existing queue table.
• CREATE_SHARDED_QUEUE and DROP_SHARDED_QUEUE — Manage sharded queue variants introduced for scalable deployments.
• ALTER_QUEUE, START_QUEUE, STOP_QUEUE, and DROP_QUEUE — Control the operational state and existence of individual queues.
• ADD_SUBSCRIBER, ALTER_SUBSCRIBER, REMOVE_SUBSCRIBER, and QUEUE_SUBSCRIBERS — Manage and report on subscribers, supporting both single-consumer and multiconsumer queue models.
• GRANT_TYPE_ACCESS, GRANT_QUEUE_PRIVILEGE, GRANT_SYSTEM_PRIVILEGE, REVOKE_QUEUE_PRIVILEGE, and REVOKE_SYSTEM_PRIVILEGE — Administer security so that application schemas such as APPS can enqueue and dequeue.

Tables Accessed

The ETRM metadata does not list application-level tables referenced by this package. DBMS_AQADM operates primarily against the AQ data dictionary views and the queue tables it creates and manages. It maintains metadata in AQ$ and ALL_QUEUE / USER_QUEUE dictionary structures, and it writes to the queue table storage itself when messages exist. From an EBS perspective, the most relevant artifacts are the queue tables created under the APPLSYS or APPS schema by this package during AQ setup, which are then referenced by synonym within the applications environment.

Usage Notes

DBMS_AQADM is typically invoked from SQL*Plus or a DBA session during initial AQ configuration, patches that introduce new business event queues, or integration setup. It is referenced by eighteen other packages in the ETRM metadata, which confirms its widespread dependency within the EBS framework. In Oracle EBS 12.1.1 and 12.2.2, the package is commonly used when configuring Workflow Business Events or XML Gateway queues, and its procedures may be called from concurrent programs or custom PL/SQL that provisions queues programmatically. Execute privilege is generally restricted to privileged schemas and the APPS schema, since queue table creation is an administrative DDL operation. Custom code should invoke DBMS_AQADM through the standard APPS synonym and should always pair creation calls with matching subscriber and privilege grants before enqueue or dequeue operations begin.