Search Results dbms_xmlgen




Overview
Explore in AI Chat

Overview

The SYS.DBMS_XMLGEN package is a core Oracle Database PL/SQL built-in supplied under the SYS schema and exposed to the database through a public synonym. In the Oracle E-Business Suite 12.1.1 and 12.2.2 environments, it provides a server-side engine for generating XML documents directly from SQL queries. Rather than requiring application code to loop over a cursor and concatenate tag strings, the package accepts a query string and internally constructs a well-formed XML document from the result set, applying configurable row tags, rowset tags, and styling transformations. Within EBS, this capability underpins integration points where relational data must be serialized into XML for transport, interface staging, or reporting. The ETRM metadata confirms the package is VALID in both releases and that it is referenced by an APPS-owned object, EDR_PSIG, demonstrating that EBS application logic directly depends on this database utility.

Key Procedures and Functions

The documented interface contains thirty-two procedures and functions. These fall into distinct functional groups:

  • Context lifecycle: NEWCONTEXT creates a query context bound to a SQL statement, while NEWCONTEXTFROMHIERARCHY creates a context from a hierarchical query. CLOSECONTEXT releases the context, and RESTARTQUERY resets the current context so the query can be re-executed.
  • Output generation: GETXML returns the generated document as a CLOB, and GETXMLTYPE returns it as an XMLTYPE value. GETNUMROWSPROCESSED reports how many rows were converted, supporting diagnostics and pagination logic.
  • Tagging control: SETROWTAG and SETROWSETTAG define the element names wrapping each row and the collection of rows. USEITEMTAGSFORCOLL directs the generator to use item tags for collection elements.
  • Window and row control: SETMAXROWS limits the number of rows emitted, and SETSKIPROWS skips a leading set of rows, enabling chunked extraction.
  • Character and null handling: SETCONVERTSPECIALCHARS controls entity escaping, SETCHECKINVALIDCHARS governs validation of characters, SETNULLHANDLING and USENULLATTRIBUTEINDICATOR determine how NULL columns are represented, and CONVERT applies the character conversion routine.
  • Stylesheet support: SETXSLT associates an XSL transformation with the context, while SETXSLTPARAM and REMOVEXSLTPARAM manage parameters passed into that transformation.

Tables Accessed

The ETRM metadata records no fixed base tables referenced directly by SYS.DBMS_XMLGEN through APPS synonyms; the package is a generic utility whose row source is supplied at runtime as the SQL statement passed to NEWCONTEXT. Consequently, the tables read by any given invocation are determined entirely by the caller. The dependency listing shows the package relies on SYS.STANDARD and XMLTYPE, and that it is referenced by DBMS_CUBE, DBMS_XMLGEN itself, WRI$_ADV_OBJSPACE_TREND_T, and the APPS object EDR_PSIG. No write operations are attributed to the package itself in the metadata.

Usage Notes

In EBS 12.1.1 and 12.2.2, DBMS_XMLGEN is typically invoked from custom PL/SQL, concurrent program logic, or integration wrappers rather than from standard Oracle Forms, which generally rely on the EBS XML Publisher and OA Framework XML facilities for document generation. The canonical pattern is to open a context, optionally set row tags, maximum rows, XSLT, and null handling, call GETXML or GETXMLTYPE, then close the context. Because it executes arbitrary SQL passed as a string, callers must construct query text carefully to avoid injection and must ensure the generating schema holds privileges on the underlying objects. Where large result sets are involved, SETMAXROWS and SETSKIPROWS support batched retrieval to control PGA and temporary space consumption. The documented dependence of the APPS object EDR_PSIG on this package indicates that at least one delivered EBS component already leverages its XML generation behavior, and customizations should preserve the same context-management discipline to avoid leaking open contexts.