Search Results ecx_dtds_u2

Overview

ECX.ECX_DTDS is a seed data table in the XML Gateway (ECX) schema of Oracle E-Business Suite, present in both release 12.1.1 and 12.2.2. It stores the Document Type Definitions (DTDs) that XML Gateway uses to validate, parse, and generate the XML messages exchanged between EBS and external trading partners, business-to-business gateways, and integration middleware. Each row represents one registered DTD identified by its file name, root element, and user-defined version, with the complete DTD text held in a CLOB column.

The table resides in the APPS_TS_SEED tablespace, reflecting its role as seeded reference data rather than transactional data. Under the heuristic Data Vault classification derived from its foreign key structure, ECX_DTDS is modeled as a standalone object; there are no inbound or outbound foreign keys, so it is best treated as a reference or lookup table rather than a hub, link, or satellite. This classification is a modeling suggestion, not a physical constraint.

Key Information Stored

The documented physical schema contains seven columns. The most important are:

• DTD_ID — Numeric surrogate primary key, enforced by the ECX_DTDS_PK constraint. It also participates in the unique index ECX_DTDS_U2 alongside ZD_EDITION_NAME.
• FILENAME — The DTD file name, used to locate the definition on disk or in the database.
• ROOT_ELEMENT — The XML root element that the DTD governs; together with FILENAME and VERSION this forms the business key.
• VERSION — User-defined DTD version, allowing multiple revisions of the same document type to coexist.
• PAYLOAD — A CLOB (declared length 4000) holding the actual DTD text used for validation and generation.
• DESCRIPTION — Free-text description of the DTD's purpose.
• ZD_EDITION_NAME — Editioning column supporting the EBS online patching (adop) model in 12.2.x, enabling edition-based redefinition of seeded rows.

The unique indexes ECX_DTDS_U1 (FILENAME, ROOT_ELEMENT, VERSION, ZD_EDITION_NAME) and ECX_DTDS_U2 (DTD_ID, ZD_EDITION_NAME) are the documented business-key candidates. A LOB index, SYS_IL0000165421C00006$$, supports the PAYLOAD column.

Common Use Cases and Queries

Typical usage includes verifying that the DTD required by a trading partner's XML message is registered, reviewing which root elements and versions are supported, and auditing seeded DTDs after patching. A standard lookup resolves a DTD by file name and root element:

• SELECT DTD_ID, DESCRIPTION, FILENAME, ROOT_ELEMENT, VERSION FROM ECX.ECX_DTDS WHERE FILENAME = :p_filename AND ROOT_ELEMENT = :p_root;
• Extracting the DTD body for a specific document type: SELECT PAYLOAD FROM ECX.ECX_DTDS WHERE DTD_ID = :p_id;
• Listing all versions of a given root element for upgrade impact analysis: SELECT ROOT_ELEMENT, VERSION, FILENAME FROM ECX.ECX_DTDS ORDER BY ROOT_ELEMENT, VERSION;

Because PAYLOAD is a CLOB, reporting queries should avoid selecting it unnecessarily. The documented query text in ETRM selects DTD_ID, DESCRIPTION, FILENAME, ROOT_ELEMENT, VERSION, PAYLOAD, and ZD_EDITION_NAME.

Related Objects

Per the documented dependency information, ECX.ECX_DTDS does not reference any database object, and it is referenced only by the ECX.ECX_DTDS# internal object. No foreign-key relationships to other application tables are documented. Related XML Gateway objects that logically consume DTD definitions include ECX.ECX_XML_DOCUMENTS, ECX.ECX_TRANSACTIONS, and the XML Gateway runtime APIs (ECX_XMLPROCESSOR and ECX_XMLGATEWAY), though these are not FK-documented dependencies. The absence of documented referential dependencies reinforces the view of ECX_DTDS as a standalone reference table whose join behavior must be derived from FILENAME and ROOT_ELEMENT conventions rather than declared constraints.