Search Results register_table

Overview

AD_LONGTOLOB_PKG is an internal Oracle E-Business Suite database package owned by the APPS schema and classified under the ETRM as "OTHER" rather than a public or published API. Its business function is to support the migration of legacy LONG and LONG RAW columns to LOB data types (CLOB and BLOB) within an EBS database. Oracle EBS 12.1.1 and 12.2.2 schemas contain numerous legacy tables that still carry LONG columns, and the AutoConfig and AD utilities must occasionally convert these columns to LOBs to satisfy newer application requirements, interoperability, or platform restrictions. This package provides the low-level orchestration that EBS database administrators use to perform that conversion in controlled, resumable stages.

The package body declares private variables used to scope a conversion to a specific table (g_Specific_Table), product (g_Specific_Product), and schema (g_Specific_Schema), along with a collection type for table names and target data types. These were retained from an earlier, more general design. The header comment block and the $Header string (adl2lpkgb.pls 120.1 2005/09/30) indicate the body has been stable since the 11i era and remains in place through 12.2.2 for compatibility.

Key Procedures and Functions

The ETRM records eight documented program units, seven of which are procedures along with one named INITIALIZE_PROCESS:

• INITIALIZE_PROCESS — Establishes the conversion run context, registering the target table, schema, and product so that subsequent steps operate against the correct object.
• ADD_NEW_COLUMN — Adds the new LOB column alongside the existing LONG column, defining the target data type for the conversion.
• WRITE_LONG_REP — Reads the LONG value from the source column and writes a representation of it, supporting the migration of large values that cannot be handled directly in a single SQL statement.
• CREATE_TRANSFORM_TRIGGERS — Creates the database triggers needed to keep the old and new columns synchronized during the transition period.
• UPDATE_NEW_DATA — Populates the new LOB column from the old LONG column and reconciles data written after the trigger was created.
• DEFER_TABLE — Defers or suspends the table (typically by disabling triggers or deferring constraints) so that the conversion can proceed safely on a live system.
• RE_ENABLE_TABLE — Re-enables the table and its triggers after conversion, restoring normal application access.

Two private helper routines, get_table_status and update_table_status, drive the state machine for each table by reading and writing the AD_LONG_COLUMN_CONVERSIONS row, defaulting to G_INITIALIZED_STATUS when no row exists and rejecting invalid status transitions.

Tables Accessed

The package reads and writes AD_LONG_COLUMN_CONVERSIONS, the EBS repository that records each schema, table, and old column name together with its current conversion status. This is the central control table for the process. It queries the data dictionary views DBA_SEGMENTS (to size the source and target segments) and DBA_TAB_COLUMNS (to confirm column presence and data types). It uses DUAL for scalar evaluation and PLITBLM, the PL/SQL table of LONG metadata, and DBMS_SQL for dynamic SQL required by the LONG-to-LOB transform. DBMS_OUTPUT and UTL_FILE provide diagnostics and logging. All objects are resolved through APPS synonyms.

Usage Notes

AD_LONGTOLOB_PKG is an internal implementation package, not a supported public API, and is not documented for direct customer invocation from Forms or concurrent programs. ETRM records exactly one package referencing it, confirming it is called from Oracle-supplied AD and migration code rather than from user code. In practice it is invoked by the EBS AutoConfig/AD conversion utilities and by the upgrade driver that performs LONG-to-LOB migrations during patching or platform migration. Because it manipulates physical table structure, triggers, and constraints, it must run only while the affected tables are quiesced, and operations are wrapped in the DEFER_TABLE / RE_ENABLE_TABLE cycle to minimize disruption. The register_table search term users commonly associate with this area relates to the table registration step performed through the INITIALIZE_PROCESS and AD_LONG_COLUMN_CONVERSIONS mechanism rather than a separately named public procedure.