DevOps, CI/CD and Automation

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