Home

Description

This rule flags any reference to the PL/SQL package DBMS_XMLQUERY or its sub‑programs (getXML, newContext, closeContext) in application code.

DBMS_XMLQUERY was officially deprecated starting with Oracle 18c and is completely desupported (removed) in Oracle 21c and later. Continuing to compile or deploy code that calls this package will cause compilation failures or runtime errors after a database upgrade.

The recommended replacement for querying XML is to use SQL/XML functions such as XMLSERIALIZE, XMLELEMENT, XMLAGG, and XMLTABLE. These provide native, set-based XML generation with full SQL integration, better performance, and long-term support.

Key Benefits

• Upgrade Safety: Ensures compatibility with Oracle 21c and future versions without relying on removed packages.
• Modern Standards: Leverages SQL/XML functions compliant with ANSI standards and best practices.
• Performance: Set-based processing eliminates procedural bottlenecks and improves throughput.
• Maintainability: Produces clean, declarative SQL for XML output with fewer dependencies.
• Supportability: Aligns with Oracle’s long-term support model and recommended development patterns.

Non-compliant Code Example

DECLARE
   ctx  DBMS_XMLQUERY.ctxType;       --Non compliant code
   xml  CLOB;
BEGIN
   ctx := DBMS_XMLQUERY.newContext('SELECT * FROM employees WHERE department_id = 10');       --Non compliant code
   xml := DBMS_XMLQUERY.getXML(ctx);       --Non compliant code
   DBMS_OUTPUT.put_line(xml);
   DBMS_XMLQUERY.closeContext(ctx);       --Non compliant code
END;
/

Compliant Code Example

SELECT XMLSERIALIZE(
         CONTENT 
         XMLELEMENT("Employees",
           XMLAGG(
             XMLELEMENT("Employee",
               XMLFOREST(
                 employee_id AS "EmployeeID",
                 first_name AS "FirstName",
                 last_name AS "LastName"
               )
             )
           )
         )
         AS CLOB
       ) AS xml_output
FROM employees
WHERE department_id = 10;     --Compliant code