2 Replies Latest reply: Jan 11, 2013 4:01 PM by 984393 RSS

    XMLAGG giving ORA-19011 when creating CDATA with large embedded XML

    984393
      What I'm trying to achieve is to embed XML (XMLTYPE return type) inside a CDATA block. However, I'm receiving "ORA-19011: Character string buffer too small" when generating large amounts of information within the CDATA block using XMLCDATA within an XMLAGG function.

      Allow me to give a step by step explanation through the thought process.

      h4. Creating the inner XML element
      For example, suppose I have the subquery below
      select
          XMLELEMENT("InnerElement",DUMMY) as RESULT
      from dual 
      ;
      I would get the following.
      RESULT                            
      ----------------------------------
      <InnerElement>X</InnerElement>
      h4. Creating outer XML element, embedding inner XML element in CDATA
      Now, if I my desire were to embed XML inside a CDATA block, that's within another XML element, I can achieve it by doing so
      select 
          XMLELEMENT("OuterElement",
              XMLCDATA(XML_RESULT)
          ) XML_IN_CDATA_RESULT
      FROM 
      (select
          XMLELEMENT("InnerElement",DUMMY) as XML_RESULT
      from dual)
      ;
      This gets exactly what I want, embedding XML into CDATA block, and CDATA block is in an XML element.
      XML_IN_CDATA_RESULT                                                        
      ---------------------------------------------------------------------------
      <OuterElement><![CDATA[<InnerElement>X</InnerElement>]]></OuterElement>    
      So far so good. But the real-world dataset naturally isn't that tiny. We'd have more than one record. For reporting, I'd like to put all the <OuterElement> under a XML root.

      h4. Now, I want to put that data in XML root element called <Root>, and aggregate all the <OuterElement> under it.
      select
          XMLELEMENT("Root", 
              XMLAGG(
                  XMLELEMENT("OuterElement",
                      XMLCDATA(INNER_XML_RESULT)
                  )
              )
          )
      FROM 
          (select 
               XMLELEMENT("InnerElement",DUMMY) as INNER_XML_RESULT
           from dual)
      ;
      And to my excitement, I get what I want..
      <Root>
          <OuterElement><![CDATA[<InnerElement>X</InnerElement>]]></OuterElement>
          <OuterElement><![CDATA[<InnerElement>Y</InnerElement>]]></OuterElement>
          <OuterElement><![CDATA[<InnerElement>Z</InnerElement>]]></OuterElement>
      </Root>  
      But... like the real world again... the content of <InnerElement> isn't always so small and simple.

      h4. The problem comes when <InnerElement> contains lots and lots of data.
      When attempting to generate large XML, XMLAGG complains the following:
      ORA-19011: Character string buffer too small
      The challenge is to keep the XML formatting of <InnerElement> within CDATA. A particular testing tool I'm using parses XML out of a CDATA block. I'm hoping to use [Oracle] SQL to generate a test suite to be imported to the testing tool.

      I would appreciate any help and insight I could receive, and hopefully overcome this roadblock.

      Edited by: user6068303 on Jan 11, 2013 12:33 PM

      Edited by: user6068303 on Jan 11, 2013 12:34 PM
        • 1. Re: XMLAGG giving ORA-19011 when creating CDATA with large embedded XML
          odie_63
          That's an expected error.
          XMLCDATA takes a string as input, but you're passing it an XMLType instance, therefore an implicit conversion occurs from XMLType to VARCHAR2 which is, as you know, limited to 4000 bytes.

          This indeed gives an error :
          SQL> select xmlelement("OuterElement", xmlcdata(inner_xml))
            2  from (
            3    select xmlelement("InnerElement", rpad(to_clob('X'),8000,'X')) as inner_xml
            4    from dual
            5  ) ;
          ERROR:
          ORA-19011: Character string buffer too small
          
          
          
          no rows selected
          The solution is to serialize the XMLType to CLOB before passing it to XMLCDATA :
          SQL> select xmlelement("OuterElement",
            2           xmlcdata(xmlserialize(document inner_xml))
            3         )
            4  from (
            5    select xmlelement("InnerElement", rpad(to_clob('X'),8000,'X')) as inner_xml
            6    from dual
            7  ) ;
          
          XMLELEMENT("OUTERELEMENT",XMLCDATA(XMLSERIALIZE(DOCUMENTINNER_XML)))
          --------------------------------------------------------------------------------
          <OuterElement><![CDATA[<InnerElement>XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
          (use getClobVal method if your version doesn't support XMLSerialize)
          • 2. Re: XMLAGG giving ORA-19011 when creating CDATA with large embedded XML
            984393
            Yes, I am aware of the 4000 byte limit for VARCHAR2. I had assumed that gave the classic ORA-19011.

            Thank you for shedding the light on XMLSERIALIZE(). That resolved the roadblock I was facing. I extremely appreciate the help.