The Oracle EBS Extraction Guide

EBS is honestly different from every SaaS ERP on this site: it runs on an Oracle database you can actually reach. Oracle’s own EBS Concepts guide describes the database tier as what “stores and manages all the data maintained by Oracle E-Business Suite.” That database is the extraction source, but the correct object layer depends on the access method. In R12.2, logical SQL reads must use the APPS synonyms that point to editioning views; Oracle requires that cover layer so a patch cannot expose obsolete physical columns. Log-based CDC necessarily captures physical product-schema tables from redo and therefore needs DBA and connector validation against the active logical model. MOAC and language views add session semantics, covered below.

12.2 introduced online patching: at any moment the database holds a run edition and, during a patch cycle, a patch edition side by side. Application code reaches data through editioning views via the APPS synonym, not the physical table directly. Oracle’s own Concepts guide warns that reading through the physical layer “may result in obsolete data been returned” [sic] during a patching cycle.

Seed and configuration tables carry an edition-name column (commonly seen as ZD_EDITION_NAME) with a row-level security policy so the run-edition and patch-edition copies coexist in the same table. For extraction that means seeded and configuration rows can appear duplicated per edition during a patch cycle — dedupe, or filter to the run edition. This catalog already excludes that column from published keys — the cost-type master’s unique key, for example, carries it in the source dictionary but not here.

Practical guidance: keep ad hoc and scheduled SQL on the APPS logical layer in R12.2. For physical log CDC, have the EBS DBA and connector owner map captured base-table columns to the active editioning view, test through a patch cycle, and coordinate extraction with the patching calendar.

Log-based CDC is the production path. Oracle GoldenGate captures change from Oracle Database redo logs. That physical boundary does not inherit EBS’s APPS synonym or editioning-view semantics automatically. Validate supported source objects, column mappings, crossedition behavior, and patch-cycle operations with Oracle’s product documentation and your EBS DBA. Apply the same checks to any third-party log-based tool.

The batch fallback is a scheduled JDBC pull filtered on LAST_UPDATE_DATE. Take Oracle’s own Developer’s Guide warning about the Record History columns seriously — it is the official version of the quirks guide’s #who-columns caveat: “Never use Record History columns to qualify rows for processing. Never depend on these columns containing correct information.” Pair watermark pulls with periodic full refreshes, and snapshot small reference and translation tables outright rather than watermarking them.

Hard deletes happen — EBS purge programs physically remove rows — and a watermark pull never sees them. Only log-based CDC or a reconciling full refresh catches a delete.

Four paths, ranked by how often they’re the right answer, not by product maturity. (a) GoldenGate’s official Databricks target — stage-and-merge through cloud object storage (Avro), then MERGE into Delta, with Unity Catalog supported. (b) Databricks Lakeflow Connect’s Oracle connector — in Beta as of September 2026, LogMiner-based CDC over JDBC, requiring archive log mode and supplemental logging on the source database. Databricks’ own FAQ names Oracle E-Business Suite explicitly as a supported source when you can reach the underlying database — its CDC path reads tables, so R12.2 physical/logical mapping still needs validation. (c) Lakeflow Connect query-based ingestion for scheduled SQL reads without CDC configuration, generally available since May 2026 with Oracle among its supported sources. It supports a full load on each run or single-cursor incrementals, not continuous capture; rows with a null cursor value are not ingested, and delete tracking has separate API-only constraints. (d) Lakehouse Federation for Oracle for governed federated reads, with Auto Loader for files already landed in object storage.

Whichever path you take, land into a bronze schema under Unity Catalog — the raw, source-aligned layer every silver/gold build on this site assumes. The <catalog>.<schema> placeholders in every boilerplate snippet on this site are meant to point at exactly that bronze layer, and every snippet keeps EBS’s native uppercase identifiers.

The hierarchy: CDC change records are authoritative — they carry an operation type, real ordering, and real deletes. The LAST_UPDATE_DATE watermark is fallback-only and delete-blind — it can tell you a row changed, never that one disappeared.

The safe load is two steps: dedupe each incoming CDC batch to one row per key (latest change record wins), then MERGE into bronze — translating a delete operation into a physical DELETE and everything else into an upsert. The skeleton below keys on TRANSACTION_ID against MTL_MATERIAL_TRANSACTIONS; the CDC metadata column names (_change_type, _commit_version) are illustrative — every tool names them differently.

-- Incremental upsert from a staged CDC batch into a bronze Delta table.
-- CDC change records are authoritative when you have them: dedupe on the key
-- keeping the latest change record, delete on delete-ops, else upsert.
-- Column names below are illustrative — CDC tool metadata columns vary.
MERGE INTO <catalog>.<schema>.MTL_MATERIAL_TRANSACTIONS AS tgt
USING (
  SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (
      PARTITION BY TRANSACTION_ID
      ORDER BY _commit_version DESC   -- tool-dependent ordering column
    ) AS rn
    FROM <catalog>.<staging_schema>.MTL_MATERIAL_TRANSACTIONS
  ) WHERE rn = 1
) AS src
ON tgt.TRANSACTION_ID = src.TRANSACTION_ID
WHEN MATCHED AND src._change_type = 'delete' THEN DELETE
WHEN MATCHED AND src._change_type != 'delete' THEN UPDATE SET *
WHEN NOT MATCHED AND src._change_type != 'delete' THEN INSERT *

A short exclusion list, all covered elsewhere in this reference in more depth. The _VL views when you need deterministic multilingual extraction — their translation row follows the database session language, so land the base and translation tables when all languages are required (#tl-tables). In R12.2, keep SQL reads on APPS synonyms and editioning views; reserve physical base tables for a validated log-CDC design (#editions). MOAC-filtered organization views — a landed extract has no session to filter through (#moac). Interface and staging tables — transient by design, and none are cataloged here; extract the persistent base tables they feed instead.

Also skip the convenience views this catalog marks as views — PO_VENDORS, BOM_BILL_OF_MATERIALS, and BOM_INVENTORY_COMPONENTS — and extract their base tables directly. The small org-mapping views (HR_OPERATING_UNITS, ORG_ORGANIZATION_DEFINITIONS) are fine to read as lookups, but land their base HR and inventory tables for a durable extract.