Search Results dbms_xmlquery




Overview
Explore in AI Chat

Overview

DBMS_XMLQUERY is a SYS-owned PL/SQL package that forms part of Oracle's server-side XML generation infrastructure. Unlike the related DBMS_XMLGEN package, DBMS_XMLQUERY is a stateful, context-based API: the caller first creates a query context with NEWCONTEXT, applies formatting and behavioural settings through a series of setter procedures, and then executes the query to produce an XML document (typically as a CLOB) that is returned to the caller. In the Oracle E-Business Suite 12.1.1 and 12.2.2 environments, the package is present in every database because it is installed with the core RDBMS, and it is exposed to the APPLSYSPUB and APPS schemas through public synonyms.

The ETRM metadata records the package as VALID in SYS, with dependencies only on the SYS schema and the STANDARD package, and with no dependencies on any application tables. Its classification is listed as OTHER, reflecting that it is a database-supplied utility rather than an E-Business Suite application API. The package body is documented with 55 procedures and functions, of which the following are the most commonly referenced.

Key Procedures and Functions

  • NEWCONTEXT — Allocates and initialises a query context, optionally bound to a SQL query and to a set of binds, and returns a handle used by all subsequent calls.
  • CLOSECONTEXT — Releases a previously created context, together with any associated XSLT parameters and cached metadata.
  • GETVERSION — Returns the version identifier of the installed DBMS_XMLQUERY package.
  • SETROWSETTAG / SETROWTAG / SETERRORTAG — Define the XML element names used to wrap the complete result set, each individual row, and error output respectively.
  • SETROWIDATTRNAME / SETROWIDATTRVALUE / SETCOLLIDATTRNAME — Control the optional ROWID and collection-ID attributes emitted on row and collection elements.
  • USETYPEFORCOLLELEMTAG / USENULLATTRIBUTEINDICATOR — Govern how collection elements are tagged and whether NULL columns are represented as explicit attributes.
  • SETSQLTOXMLNAMEESCAPING / SETTAGCASE — Determine how SQL identifiers are translated into valid XML names, and whether generated tag names use upper, lower, or mixed case.
  • SETDATEFORMAT — Specifies the date mask applied to DATE and TIMESTAMP columns during serialisation.
  • SETMAXROWS / SETSKIPROWS — Bound the number of rows returned and the number of leading rows skipped, supporting simple result-set paging.
  • SETSTYLESHEETHEADER — Emits an XML processing instruction referencing an external stylesheet.
  • SETXSLT, SETXSLTPARAM, REMOVEXSLTPARAM — Associate an XSL transformation with the context, set or remove named XSLT parameters, and thereby cause the generated XML to be transformed server-side before it is returned.

Tables Accessed

DBMS_XMLQUERY does not read or write any application tables. The ETRM metadata lists no referenced tables via APPS synonyms, and the only recorded dependencies are SYS and STANDARD. Any tables touched by the package are those named in the caller's own SQL statement when NEWCONTEXT is invoked; the package itself simply executes that statement and serialises the resulting rows.

Usage Notes

The package is typically invoked from custom PL/SQL, from Java or OAF code running against the EBS database, or from concurrent programs and database jobs that must publish query results as XML. It is not called directly by standard Oracle Forms in EBS 12.1.1 or 12.2.2; Forms-based integrations normally use the XML Publisher or the FND APIs, which in turn may use DBMS_XMLGEN or DBMS_XMLQUERY internally.

Because DBMS_XMLQUERY is a SYS-owned component of the RDBMS kernel, its procedure signatures are fixed by the database release and should not be altered. The ETRM dependency report confirms that the package is referenced by PUBLIC and by SYS itself, and that it is referenced by zero application packages, indicating that no EBS product team owns or extends it. Customisations that rely on DBMS_XMLQUERY should be validated after each database upgrade, since Oracle has progressively deprecated the older XML SQL Utility interfaces in favour of DBMS_XMLGEN and SQL/XML functions such as XMLQuery and XMLTable.

Every context created with NEWCONTEXT should be matched with a CLOSECONTEXT call in the same session to avoid leaking memory in long-running concurrent requests.