Search Results dbms_alert




Overview
Explore in AI Chat

Overview

SYS.DBMS_ALERT is a standard Oracle-supplied PL/SQL package that provides support for the asynchronous notification of database events. Rather than requiring an application to poll a table repeatedly, DBMS_ALERT allows a session to block and wait until a specific named alert is signalled by another session. The signalling session fires the alert from within a database trigger or application code, and any session waiting on that alert name is woken once the signalling transaction commits.

Within Oracle EBS 12.1.1 and 12.2.2, DBMS_ALERT is classified as an OTHER API owned by SYS. It is not an EBS business API in the sense of an interface table or an Open Interface, but a foundational database utility on which other components rely. The package is referenced by three other packages in the EBS schema, which indicates that it serves as infrastructure for internal coordination between concurrent sessions rather than as a direct end-user feature. The user query "causa alert" most likely relates to troubleshooting the cause of an alert — for example, determining why a waiting session did not receive notification — which is typically rooted in the transaction-based semantics of this package.

Key Procedures and Functions

The documented package exposes seven procedures and functions. Their purposes are as follows:

  • SET_DEFAULTS — Establishes default polling and timeout behaviour for subsequent alert operations performed by the session.
  • REGISTER — Registers the current session as a waiter for a named alert so that it can subsequently wait on that alert. A session must register before it can wait.
  • REMOVE — Removes the session's registration for a specific named alert, so the session will no longer receive notifications for that alert.
  • REMOVEALL — Removes all alert registrations currently held by the session in a single call.
  • WAITANY — Blocks the session until any one of the alerts on which it is registered is signalled, returning the name of the alert that fired along with its message.
  • WAITONE — Blocks the session until a specific named alert is signalled, returning the associated message and status.
  • SIGNAL — Raises the specified named alert, supplying an optional message payload to be delivered to all registered waiters. This is the only call in the package that does not implicitly commit.

Tables Accessed

The ETRM metadata documents no application tables accessed through APPS synonyms for this package. DBMS_ALERT maintains its internal alert registrations and pending signals within the database's own SYS-owned alert infrastructure rather than in EBS application tables. Alert names themselves are not stored in EBS data dictionary tables; they are registered at runtime by calling sessions. Consequently, there is no direct read or write footprint against EBS business tables such as those in the AP, GL, or INV schemas.

Usage Notes

DBMS_ALERT is most commonly invoked from custom PL/SQL code, database triggers, or concurrent program logic that needs to coordinate work across sessions. A typical pattern places SIGNAL inside an AFTER INSERT, UPDATE, or DELETE trigger on a monitored table, while a separate session, form, or concurrent program registers and waits on the corresponding alert name.

Several operational constraints govern its use. Alerts are transaction-based, so the waiting session is not awakened until the signalling transaction commits; a rollback discards the signal, which is a frequent root cause when an expected alert never arrives. A waiting session is blocked inside the database and cannot perform other work while waiting, so the package is unsuitable for sessions that must remain responsive to user input. Finally, apart from SIGNAL, all documented calls perform an implicit commit, which can interfere with surrounding transactional logic in EBS forms or interfaces. For high-volume or latency-sensitive requirements, DBMS_PIPE or DBMS_SCHEDULER-based polling is often preferred.