Search Results commit_rows

Overview

SYS.DBMS_RECTIFIER_DIFF is an Oracle-supplied, undocumented-but-public PL/SQL package used by the Oracle Server database itself, and surfaced within Oracle E-Business Suite environments as a utility for reconciling divergent copies of the same table across two database sites. Its business purpose is to detect and repair differences between a "truth" (reference) fragment of a table and a "comparison" fragment of a corresponding table at another site, which is the classic pattern for symmetric replication or multi-site data synchronisation. In EBS terms, this makes the package relevant whenever replicated data must be validated for consistency — for example, when troubleshooting advanced replication, materialised view, or multi-master configuration issues between a primary and a secondary database node.

The package is declared AUTHID CURRENT_USER, so its runtime privileges are those of the calling schema rather than SYS. That design allows trusted EBS schemas (typically APPS or a DBA account) to invoke it against their own tables while the package body resides in SYS.

Key Procedures and Functions

The ETRM metadata documents four public routines out of the package specification, although the internal type and exception declarations indicate a fuller implementation.

• DIFFERENCES — Compares a fragment of the "truth" table at a reference site against the corresponding fragment of a "comparison" table at a second site. It uses a caller-supplied column list and a where-clause to define the fragment, and writes the detected discrepancies into the pair of staging tables named missing_rows_oname1 and missing_rows_oname2. The caller must pre-create these staging tables with the correct shape in the schema/site nominated by missing_rows_sname / missing_rows_site, and must avoid white space around commas in the column list.
• RECTIFY — Applies the corrections identified by DIFFERENCES, rectifying the divergent rows so that the comparison site matches the reference site. It operates against the same staging tables that were populated by the diff step.
• TURN_REPLICATION_OFF — Disables replication activity for the objects being reconciled, preventing new changes from arriving while the diff and rectify cycle is in progress.
• TURN_REPLICATION_ON — Re-enables replication once reconciliation is complete, restoring normal propagation.

The exception declarations (nosuchsite, badnumber, missingprimarykey, badname, cannotbenull, notshapeequivalent, unknowncolumn, unsupportedtype, badmrname) map to Oracle error numbers -23365 through -23377 and signal typical validation failures: mismatched table shapes, missing primary keys, unsupported column types, and invalid site or table names.

Tables Accessed

The metadata documents no fixed EBS tables through APPS synonyms; the package is generic and operates on caller-named tables. Its externally visible data footprint is the two staging tables, missing_rows_oname1 and missing_rows_oname2, created by the caller in missing_rows_sname at missing_rows_site. The package reads the reference and comparison fragments and writes discrepancy records into those staging tables, which RECTIFY then consumes. The package is referenced by two other database packages, confirming its role as a shared infrastructure utility rather than an EBS application API.

Usage Notes

Because the metadata exposes no parameter lists, the parameter names quoted above (column_list, where_clause, reference_site, comparison_site, missing_rows_sname, missing_rows_site, sname1.oname1, sname2.oname2) should be treated as documented identifiers rather than literal declarations. In practice the package is invoked from SQL*Plus or a DBA script — not from an EBS form or concurrent program — by DBAs performing manual conflict resolution in replicated environments. The typical sequence is: create the staging tables, call TURN_REPLICATION_OFF, call DIFFERENCES, call RECTIFY, then call TURN_REPLICATION_ON. In EBS 12.1.1 and 12.2.2 this is rarely a day-to-day application concern, but it is a valuable diagnostic when replication metadata or multi-site copies of an EBS schema object have drifted out of sync.