Search Results do_mv_ddl2

Overview

AD_MV is an Oracle E-Business Suite database package owned by the APPS schema that provides a controlled, centralized interface for manipulating Oracle materialized views (MVs, historically termed snapshots) used to support Oracle Applications reporting and summary data structures. It is declared with AUTHID CURRENT_USER, meaning its unqualified database object references resolve against the invoking schema's privileges rather than the definer's, which is consistent with a utility package designed to be called from a variety of application contexts. The package encapsulates the raw DDL required to create, alter, and drop materialized views and their associated indexes, applying standard EBS tablespace conventions through its global variables g_mv_data_tablespace and g_mv_index_tablespace. Three package constants—mv_create, mv_alter, and mv_drop—encode the operation type passed to the internal DDL drivers. The package dates from the 11i era (the source header shows version 115.6) and remains present in both 12.1.1 and 12.2.2.

Key Procedures and Functions

Six documented procedures constitute the public API. Users searching for "drop_mv" are directed to the DROP_MV procedure, which removes an existing materialized view by name using a supplied statement.

• CREATE_MV — Creates a materialized view from a supplied statement, with a boolean flag indicating whether the statement is a long-running operation.
• CREATE_MV2 — An overload variant of CREATE_MV that accepts an integer (rather than boolean) long-statement indicator, provided for callers binding numeric flags.
• ALTER_MV — Applies an alteration statement to an existing materialized view.
• DROP_MV — Drops the named materialized view using the supplied statement text.
• DO_MV_DDL — The generic DDL driver taking an operation code (mv_create, mv_alter, or mv_drop), the MV name, the statement text, and a boolean execute flag.
• DO_MV_DDL2 — The numerically flagged counterpart to DO_MV_DDL, accepting an integer execute indicator.

The parameter lists are not restated here to avoid speculation; the documented signature pattern is (operation, mview name, statement, execute flag).

Tables Accessed

The package references several data dictionary and EBS administrative views through APPS synonyms. FND_ORACLE_USERID and DBA_USERS are consulted to resolve the Oracle schema/user identity that owns or executes the MV operations, a common requirement in a multi-schema EBS environment. USER_MVIEWS and USER_OBJECTS are queried to determine whether a materialized view already exists and to inspect its state before create, alter, or drop actions are executed. USER_TABLES is used to verify the underlying container table. PLITBLM, the PL/SQL table-to-index-by conversion utility, supports internal collection handling. DBMS_SQL and DBMS_UTILITY provide dynamic SQL execution and identifier parsing for the DDL strings being processed.

Usage Notes

AD_MV is not an end-user-facing API and is not typically invoked from an Oracle Forms screen. It is called by other PL/SQL packages—documentation records it as referenced by two other packages—which assemble the appropriate MV DDL and delegate execution to AD_MV so that tablespace assignment, existence checks, and dynamic SQL execution follow a single consistent path. Custom extensions that need to build materialized views within the EBS data model should call AD_MV rather than issuing DDL directly, so that the same tablespace globals and dictionary validations apply. Callers must ensure the statement text is syntactically complete and that the invoking user holds the privileges required, since AUTHID CURRENT_USER executes the DDL under the caller's authority.