Search Results close_database_link

Overview

SYS.DBMS_SESSION is an Oracle-supplied PL/SQL package that exposes session-level control primitives to the database session in which it is invoked. Within Oracle E-Business Suite 12.1.1 and 12.2.2, the package functions as a foundational utility rather than an application-specific API. It allows callers to inspect and modify attributes of the current session, including role enablement, national language settings, session identifiers, application context, SQL tracing, and package state. Because these operations act on the connected session, DBMS_SESSION is frequently used by EBS components that must guarantee isolation of state between concurrent users, reset cached package state after configuration changes, or establish the security context on which row-level and function-based security depend.

The ETRM metadata classifies the object as owner SYS, status VALID, with an API classification of OTHER, confirming that it is a generic database utility rather than an EBS business API. It is referenced by 23 other packages, including APPS-owned OKC_ASYNC_PVT and OKC_REPORT_PVT, which depend on it for session context and trace control during asynchronous contract processing and reporting.

Key Procedures and Functions

The documented interface comprises twenty-four procedures and functions. The most significant are grouped below by purpose.

• Identity and session state: UNIQUE_SESSION_ID returns a unique identifier for the current session; IS_SESSION_ALIVE reports whether a given session remains active; SET_IDENTIFIER and CLEAR_IDENTIFIER manage the client identifier associated with the session.
• Application context: SET_CONTEXT, CLEAR_CONTEXT, CLEAR_ALL_CONTEXT, and LIST_CONTEXT create, remove, and enumerate application context namespaces and attribute values. These are central to EBS function-based security, where context values determine which rows a user may access.
• Role management: SET_ROLE enables or disables a database role for the current session; IS_ROLE_ENABLED reports whether a role is currently enabled.
• Trace and diagnostics: SET_SQL_TRACE, SESSION_TRACE_ENABLE, and SESSION_TRACE_DISABLE control SQL trace generation for the session.
• Package and cursor management: RESET_PACKAGE clears all package state in the session; MODIFY_PACKAGE_STATE changes the state of packages; SET_CLOSE_CACHED_OPEN_CURSORS and CLOSE_DATABASE_LINK manage cursor and link resources; FREE_UNUSED_USER_MEMORY releases unused session memory.
• Environment settings: SET_NLS adjusts national language and territory parameters for the session; SWITCH_CURRENT_CONSUMER_GROUP changes the resource consumer group under which the session runs.

Tables Accessed

The ETRM metadata documents no base tables referenced directly by SYS.DBMS_SESSION through APPS synonyms, which is consistent with its role as a session-control interface. Its operations act on session memory structures, the data dictionary, and dynamic performance views rather than on application tables. Where persistent effects occur, they are indirect — for example, trace files written to the database diagnostic destination, or context values stored in the session's context area. Application-level tables are touched by the calling packages, not by DBMS_SESSION itself.

Usage Notes

DBMS_SESSION is typically invoked from custom PL/SQL, EBS concurrent program logic, database triggers, and forms-based code that must manipulate session state. Common patterns include setting application context before executing queries subject to function-based security, resetting package state after a configuration or setup change so cached values are refreshed, and enabling SQL trace for targeted diagnostics. Because the package is owned by SYS and granted to PUBLIC, it is callable from APPS schema code without additional grants. When troubleshooting, note that SET_CONTEXT requires that the namespace be created with an appropriate trusted package; improper use can undermine EBS security. RESET_PACKAGE is a heavyweight operation and should be used sparingly, as it clears global state across all packages in the session.