Search code examples
oracle-databaseplsql

Adding CLOB data to XML usng `Dbms_XmlDom`

The following code executes perfectly well in my program. In this context I end up with an XML file that contains a tag with the contents "a very very very long string".

DECLARE
   doc_           dbms_xmlDom.DomDocument := := Dbms_XmlDom.newDomDocument;
   nd_txt_        dbms_xmldom.DOMText;
   text_content_  CLOB := 'a very very very long string';
BEGIN
   --- blah blah blah, lots of code goes here...

   nd_txt_ := Dbms_XmlDom.createTextNode (doc_, text_content_);

END;

But you will have noticed that I've defined my text_content_ variable as a CLOB, not VARCHAR2. What I'm doing in my real program is Base-64 encoding a PDF file and assigning that to text_content_. The resulting base-64 string fits fine into the CLOB, but breaks the 32k limit imposed by PL/SQL's VARCHAR2 datatype. Since the function Dbms_XmlDom.createTextNode() is defined with a VARCHAR2 datatype, at runtime I get an error saying that the character string buffer is too small.

Does Dbms_XmlDom support tag-content >32k characters? And if so, how do I implement it?

Solution

  • You can use a character stream:

    DECLARE
   doc_           dbms_xmlDom.DomDocument := Dbms_XmlDom.newDomDocument;
   nd_txt_        dbms_xmlDom.DomText;
   text_content_  CLOB := 'a very very very long string';

   nd_            dbms_xmlDom.DomNode;
   stream_        sys.utl_characteroutputstream;
   pos_           pls_integer := 1;
   amt_           pls_integer := 4000;
   buf_           varchar2(4000);
BEGIN
   nd_txt_ := dbms_xmlDom.createTextNode (doc_, '');

   nd_ := dbms_xmlDom.makeNode(nd_txt_);
   stream_ := dbms_xmlDom.setNodeValueAsCharacterStream(nd_);
   while pos_ <= dbms_lob.getlength(text_content_) loop
      dbms_lob.read(text_content_, amt_, pos_, buf_);
      pos_ := pos_ + amt_;
      stream_.write(buf_, 0, amt_);
   end loop;
   stream_.close;
END;
/

    That starts with an empty DomText, casts it a DomNode, and calls setNodeValueAsCharacterStream to get an output stream:

    This function returns an instance of the PL/SQL XMLCHARACTEROUTPUTSTREAM type into which the caller can write the node value. The datatype of the node can be any valid XDB datatype. If the type is not character or CLOB, the character data written to the stream is converted to the node datatype. If the datatype of the node is character or CLOB, then the character data written to the stream is converted from PL/SQL session character set to the character set of the node.

    It then loops over the CLOB and writes it to that stream in chunks that fit into a varchar2 buffer.

    fiddle showing your original code and the length of the DomText (via a DomNode), then this approach with the same string, and then again with a much larger CLOB value (with a second stream to read the node value back to check its length).