Search Results ecx_dtds




Overview
Explore in AI Chat

Overview

ECX_DTDS is a table owned by the ECX schema within the Oracle E-Business Suite XML Gateway product. Its documented purpose is to store the Document Type Definitions (DTDs) used by XML Gateway, the EBS component responsible for generating, transmitting, receiving, and processing XML business documents to and from external trading partners. A DTD defines the permitted structure, elements, and attributes of an XML message; ECX_DTDS therefore acts as the repository of structural definitions that XML Gateway references when validating or rendering outbound and inbound XML payloads. The object carries a status of VALID and is present in both the 12.1.1 and 12.2.2 releases, with the documented 12.2.2 physical schema exposing seven columns in the ECX schema.

From a Data Vault modeling perspective, the heuristic classification mined from the foreign-key structure is standalone; ECX_DTDS participates in no documented parent-child relationships and is best treated as an independent reference or definition table rather than a hub, link, or satellite. Practitioners modeling this data for an enterprise warehouse should consider replicating it as a standalone reference set, keyed on the DTD identity, without expecting natural integration points to other ECX operational tables.

Key Information Stored

The documented columns for ECX_DTDS are DTD_ID, DESCRIPTION, FILENAME, ROOT_ELEMENT, VERSION, PAYLOAD, and ZD_EDITION_NAME.

  • DTD_ID — the surrogate primary key, enforced by the ECX_DTDS_PK constraint. It uniquely identifies each stored DTD record and is the value most commonly referenced by dependent XML Gateway processing logic.
  • FILENAME — the physical or logical file name associated with the DTD definition, used to locate and reference the definition.
  • ROOT_ELEMENT — the XML root element that the DTD governs; this determines which document types the definition applies to during parsing and validation.
  • VERSION — the version identifier for the DTD, allowing multiple revisions of the same logical definition to coexist.
  • PAYLOAD — the actual DTD content stored as a large object or text column; this is the operative definition consumed by XML processing.
  • DESCRIPTION — a human-readable description of the DTD's purpose or scope.
  • ZD_EDITION_NAME — the editioning column introduced under the EBS Edition-Based Redefinition (EBR) framework in 12.2, enabling online patching by isolating rows by edition.

Two unique indexes serve as business-key candidates: ECX_DTDS_U1 on (FILENAME, ROOT_ELEMENT, VERSION, ZD_EDITION_NAME), meaning a given file, root element, and version combination is unique within an edition; and ECX_DTDS_U2 on (DTD_ID, ZD_EDITION_NAME), reinforcing the surrogate key within the edition namespace.

Common Use Cases and Queries

Typical usage centers on interrogating available definitions, resolving a definition by document type, and auditing version coverage across releases.

  • Listing all available DTDs with their root elements and versions: SELECT DTD_ID, FILENAME, ROOT_ELEMENT, VERSION FROM ECX.ECX_DTDS ORDER BY ROOT_ELEMENT, VERSION;
  • Resolving the definition for a specific root element: SELECT DTD_ID, PAYLOAD FROM ECX.ECX_DTDS WHERE ROOT_ELEMENT = :root AND VERSION = :version;
  • Identifying duplicate or overlapping definitions: SELECT FILENAME, ROOT_ELEMENT, VERSION, COUNT(*) FROM ECX.ECX_DTDS GROUP BY FILENAME, ROOT_ELEMENT, VERSION HAVING COUNT(*) > 1;
  • Reporting on edition isolation under EBR: SELECT ZD_EDITION_NAME, COUNT(*) FROM ECX.ECX_DTDS GROUP BY ZD_EDITION_NAME;

Because the table is standalone, reporting queries generally execute against ECX_DTDS alone rather than through joins. The PAYLOAD column, being a large object, should be excluded from list-style reports to avoid unnecessary I/O.

Related Objects

No foreign-key relationships are documented for ECX_DTDS, so related objects are referenced logically rather than through declarative constraints. The most significant include:

  • ECX_DTDS_PK — the primary-key constraint on DTD_ID, the principal access path.
  • ECX_DTDS_U1 / ECX_DTDS_U2 — unique indexes providing business-key and surrogate-key enforcement.
  • ECX transaction and message tables — used during XML Gateway processing, where the DTD_ID resolved from ECX_DTDS is carried alongside a message definition.
  • ECX document / message definition tables — which associate XML Gateway transaction types with the definitions held here.
  • ECX_* XML Gateway runtime tables — consumed during validation and transformation, referencing the PAYLOAD content of this table.

Any integration should be validated against the actual installed schema, as the EBR editioning column alters effective uniqueness across editions.