Search Results update_new_data




Overview
Explore in AI Chat

Overview

AD_LONGTOLOB_PKG is an Oracle E-Business Suite Applications DBA (AD) utility package that supports the migration of legacy LONG columns to LOB (CLOB/BLOB) data types within the EBS database schema. In releases 12.1.1 and 12.2.2, several repository and interface tables historically use the LONG data type, which has significant functional limitations compared to LOB types — LONG columns cannot be used in most SQL functions, cannot be replicated or compared easily, and are deprecated in modern Oracle Database releases. This package provides the orchestration logic to convert those columns to LOBs while preserving data and minimizing application downtime.

The package models the conversion as a state machine. Each step of the migration is tracked through status constants such as INITIALIZED, NEW_COL_ADDED, TRIGGER_ADDED, TABLE_UPDATED, LONG_LOB_RENAMED, TRIGGER_DROPPED, COMPLETED, and DEFERRED, with a separate DEFERRED state indicating a table that was deliberately skipped or postponed. Actions such as NO_ACTION, WITH_DATA, WITHOUT_DATA, and DROP_COLUMN further qualify the intended behavior for a given table. This design allows the migration to be executed incrementally, paused, and resumed, and allows administrators to defer problematic tables without aborting the entire upgrade.

Key Procedures and Functions

The package exposes the following documented procedures, all of which operate on the conversion work list:

  • INITIALIZE_PROCESS — Prepares the migration by populating or resetting the tracking records in the conversion repository. It accepts optional filters for a specific table, product, or schema, enabling targeted conversions rather than a blanket database-wide operation.
  • ADD_NEW_COLUMN — Adds the new LOB column (for example, a CLOB counterpart of an existing LONG) to the target table. The caller supplies the schema, table name, old and new column names, the target data type, the current status, and the desired action.
  • WRITE_LONG_REP — Writes a report describing the state of the LONG-to-LOB migration. It is the object of interest when searching for write_long_rep. Two overloads exist: one writes to the default output destination, and one accepts a path argument for file-based output. This report is the primary diagnostic artifact for verifying progress before and after upgrades.
  • CREATE_TRANSFORM_TRIGGERS — Creates triggers that keep the old LONG column and the new LOB column synchronized during the transition period, ensuring that concurrent application writes are not lost between the column addition and the final rename.
  • UPDATE_NEW_DATA — Copies existing LONG data into the newly created LOB column for the tables marked WITH_DATA.
  • DEFER_TABLE — Marks a specific table as deferred so that it is excluded from the current migration pass and can be revisited later.
  • RE_ENABLE_TABLE — Reverses a deferral, returning a previously skipped table to the active conversion work list.

Tables Accessed

The package reads and writes the dictionary table AD_LONG_COLUMN_CONVERSIONS, which stores the per-table conversion state, column names, and status values that drive the state machine. It queries DBA_TAB_COLUMNS to discover existing LONG columns and their definitions, and DBA_SEGMENTS to assess table and segment sizes when planning migrations. DUAL supports simple singleton queries. On the operational side it relies on DBMS_SQL for dynamic DDL and DML, DBMS_OUTPUT for diagnostics, UTL_FILE for writing migration reports to the file system, and PLITBLM for internal LONG-to-LOB data handling. All of these are accessed through APPS synonyms.

Usage Notes

AD_LONGTOLOB_PKG is not an end-user API. It is invoked by AD utilities and by the AutoPatch/ADX upgrade driver during EBS patching and upgrade operations, most notably when applying patches that convert LONG columns to LOBs. DBAs also call the procedures manually from SQL*Plus during controlled maintenance windows to run the conversion in stages, using DEFER_TABLE and RE_ENABLE_TABLE to manage exceptions. WRITE_LONG_REP is commonly executed before and after a conversion cycle to generate a status report for the DBA team. One other package references this package, indicating it forms part of a larger AD migration framework rather than being called directly by forms or concurrent programs.