Search Results export_error

Overview

The APPS.DDR_ETL_UTIL_PKG package is a utility package within the Oracle E-Business Suite environment that supports extract, transform, and load (ETL) operations and materialized view maintenance. In the context of EBS 12.1.1 and 12.2.2, this package provides a centralized set of procedures and functions for managing materialized view refreshes, log truncation, error handling, and bulk data movement between staging and target tables. The package header carries the identifier ddruetls.pls 120.1.12010000.2, indicating it belongs to the DDR (Data Repository/Reporting) family of EBS objects commonly associated with data warehousing and reporting infrastructure.

The package is classified as an OTHER API within the ETRM metadata, meaning it is not a standard public business API but rather an internal utility package invoked by other EBS components, concurrent programs, or custom code. It does not form part of the documented public interface exposed to end users.

Key Procedures and Functions

The package exposes eleven documented procedures and functions. The following summarizes their intended purposes based on the metadata:

• GET_MV_REFRESH_JOB_ID — Returns a VARCHAR2 identifier used to track or correlate a materialized view refresh job, likely generating or retrieving a sequence-based job identifier.
• REFRESH_MV — Refreshes one or more materialized views using parameters that control refresh method, rollback segment, deferred RPC push, error handling behavior, purge options, parallelism, heap size, atomic refresh, job ID, and refresher identity. Returns output status and message.
• TRUNCATE_MV_LOG — Truncates a specified materialized view log, optionally scoped by job ID and refresher, returning status and message outputs.
• EXPORT_ERROR — The procedure most directly matching the search term "export_error." It writes error records associated with a given table, load ID, and file name to an external file, supporting the export phase of ETL error handling.
• IMPORT_ERROR — Provides two overloaded variants that read error records into a target table, optionally specifying an error table name, load ID, and target table type (defaulting to 'I' for interface).
• TRANSFER_DATA — Moves data between a source and target table, supporting load ID scoping and target table type designation, typical of staging-to-interface transfers.
• EXPORT_DATA — Exports table rows to a file, optionally filtered by a WHERE clause and directed to a specified file name.
• IMPORT_DATA — Imports table rows from a file into the designated table.
• REFRESH_MV_ODI — A variant of the materialized view refresh procedure tailored for ODI (Oracle Data Integrator) integration scenarios.
• TRUNCATE_MV_LOG_WRP — A wrapper procedure for the TRUNCATE_MV_LOG functionality, likely providing a simplified or overloaded interface.

Tables Accessed

The package reads and writes several objects, referenced through APPS synonyms:

• DDR_U_MV_RFRSH_LOG — Stores materialized view refresh log entries, recording refresh history and status.
• DDR_U_MV_RFRSH_SEQ — Supplies sequence values used by GET_MV_REFRESH_JOB_ID for job identification.
• ALL_TABLES and ALL_TAB_COLUMNS — Data dictionary views queried dynamically to validate table names and column structures during data movement.
• DBA_DEPENDENCIES — Used to inspect dependency relationships, likely for determining materialized view dependencies.
• DBMS_MVIEW, DBMS_SQL, DUAL, PLITBLM, UTL_FILE — Not physical tables but supplied packages and pseudotables leveraged for dynamic SQL, materialized view operations, and file I/O.

Usage Notes

DDR_ETL_UTIL_PKG is not referenced by any other documented packages, indicating it is called directly rather than through package chaining. It is typically invoked from ETL concurrent programs, custom PL/SQL scripts, or ODI workflows performing bulk data staging and error logging. The EXPORT_ERROR procedure in particular is invoked when ETL load processes encounter rejected or invalid records and must persist them to a flat file for downstream inspection or reload. Because the package relies on UTL_FILE and DBMS_SQL, the invoking session requires appropriate directory object privileges and dynamic SQL authority. Implementers should treat this package as an internal utility rather than a supported public API, and validate any custom usage against the target EBS release, as signatures such as the overloaded IMPORT_ERROR are subject to change between 12.1.1 and 12.2.2.