Create integration XML with Oracle PL/SQL

Explore this learning path

Documentation-based guide draft · Reviewed against sources on 2026-09-27 · Not executed in an IFS environment. IFS references use 26R1 unless stated otherwise. Confirm available APIs, components, and permissions in your installed release.

Generate a small XML message with Oracle’s DOM API instead of joining strings. The exercise demonstrates element creation, text escaping, CLOB output, and cleanup. Run it in an authorized Oracle development schema; it does not write to IFS tables.

Before you start

Use SQL Developer or SQLcl with permission to execute DBMS_XMLDOM. Enable DBMS Output for the connection. The example schema is fictional and must be replaced with a receiving interface’s documented XML structure before integration use.

1. Define the expected output

The intended message has one equipment reference and one description. The description deliberately contains an ampersand. After parsing the output, its text must still be Inspection & calibration, rather than double-escaped text or malformed XML.

2. Create and serialize the document

Execute this anonymous block:

SET SERVEROUTPUT ON;
DECLARE
  doc_       DBMS_XMLDOM.DOMDocument;
  root_      DBMS_XMLDOM.DOMElement;
  field_     DBMS_XMLDOM.DOMElement;
  ignored_   DBMS_XMLDOM.DOMNode;
  output_    CLOB;
  doc_ready_ BOOLEAN := FALSE;
BEGIN
  doc_ := DBMS_XMLDOM.NewDOMDocument;
  doc_ready_ := TRUE;
  root_ := DBMS_XMLDOM.CreateElement(doc_, 'equipment-note');
  ignored_ := DBMS_XMLDOM.AppendChild(
    DBMS_XMLDOM.MakeNode(doc_), DBMS_XMLDOM.MakeNode(root_));

  field_ := DBMS_XMLDOM.CreateElement(doc_, 'reference');
  ignored_ := DBMS_XMLDOM.AppendChild(
    DBMS_XMLDOM.MakeNode(root_), DBMS_XMLDOM.MakeNode(field_));
  ignored_ := DBMS_XMLDOM.AppendChild(
    DBMS_XMLDOM.MakeNode(field_),
    DBMS_XMLDOM.MakeNode(DBMS_XMLDOM.CreateTextNode(doc_, 'LAB-PUMP-04')));

  field_ := DBMS_XMLDOM.CreateElement(doc_, 'description');
  ignored_ := DBMS_XMLDOM.AppendChild(
    DBMS_XMLDOM.MakeNode(root_), DBMS_XMLDOM.MakeNode(field_));
  ignored_ := DBMS_XMLDOM.AppendChild(
    DBMS_XMLDOM.MakeNode(field_),
    DBMS_XMLDOM.MakeNode(DBMS_XMLDOM.CreateTextNode(doc_, 'Inspection & calibration')));

  DBMS_LOB.CreateTemporary(output_, TRUE);
  DBMS_XMLDOM.WriteToClob(doc_, output_);
  DBMS_OUTPUT.Put_Line(DBMS_LOB.Substr(output_, 32000, 1));
  DBMS_XMLDOM.FreeDocument(doc_);
  doc_ready_ := FALSE;
  DBMS_LOB.FreeTemporary(output_);
EXCEPTION
  WHEN OTHERS THEN
    IF doc_ready_ THEN DBMS_XMLDOM.FreeDocument(doc_); END IF;
    IF DBMS_LOB.IsTemporary(output_) = 1 THEN
      DBMS_LOB.FreeTemporary(output_);
    END IF;
    RAISE;
END;
/

SQL Developer’s substitution-variable feature may prompt at &. For this fixed lab script, run SET DEFINE OFF before the block if substitution is enabled; restore your preferred setting afterwards. SET commands are client commands, not PL/SQL statements.

3. Inspect the serialized output

The serialized XML should represent:

<equipment-note>
  <reference>LAB-PUMP-04</reference>
  <description>Inspection &amp; calibration</description>
</equipment-note>

Whitespace and declaration formatting may differ. Compare parsed node values, not the pretty-printing. The serializer escapes the ampersand because it was supplied as a text node; do not pre-escape it to & in the PL/SQL input.

4. Adapt values and validate the contract

Replace the two literals with parameters in your own package. Reject a missing required reference before constructing the document. If the receiver requires namespaces or attributes, add them using the appropriate DOM API and validate the output against its XSD.

For dates and numbers, specify the receiver’s format explicitly; do not let session NLS settings decide the output. Keep a fixture with a decimal value and non-ASCII text to catch formatting/encoding problems.

5. Pass the CLOB through a supported interface

The demo prints only the first 32,000 characters for inspection. A real integration should pass the complete CLOB to the approved interface while it is still allocated, then release it at the correct ownership boundary. Do not put large payloads or confidential data into DBMS Output logs.

Use SOAP Gateway testing to verify the receiver separately. Generating XML successfully does not enqueue or deliver it to IFS.

6. Diagnose and clean up

An undeclared-package error means the schema lacks the required package/grant or the connection is wrong. A malformed message usually indicates string concatenation was introduced during adaptation. Truncated output can come from the inspection method rather than the CLOB itself.

Repeat with empty text, an ampersand, and Unicode. Confirm errors leave no intended business changes; this block makes none and needs no commit. Close the lab connection after testing. The exception handler retains the original failure after releasing document and temporary-LOB resources.

Sources and scope

• Oracle DBMS_XMLDOM reference

Older IFS Applications examples are historical context, not evidence of Cloud compatibility. No third-party code, screenshots, or downloadable workflows are reproduced.