Orcale dbms_xmldom.writetobuffer() help

Dear Experts,
After successfully loading an XML file (6MB) into a dbms_xmldom.DOMDocument var (and dbms_xmldom.DOMNode var) I attempt to convert the Node into an XMLType using a two step process.
Step 1 - Call dbms_xmldom.writetobuffer(p_node, l_clob)
Step 2 - Call sys.xmltype.createXml(l_clob)

For small files both steps execute without error.  However, for larger files I get an error on Step 1 - Call dbms_xmldom.writetobuffer(p_node, l_clob).  Here's the error, "ORA-30185: output too large to fit in the buffer".  

The purpose for converting the NODE to an XMLTYPE is to save the data into a XMLTYPE table column.  Can anyone offer a suggestion on how to work around this?

Thanks.
function ToXmlType_
(
    p_node          in      dbms_xmldom.DOMNode
)
return sys.xmltype as
    l_xml           sys.xmltype;
    l_clob          clob;
begin
    dbms_xmldom.writetobuffer(p_node, l_clob);
    return sys.xmltype.createXml(l_clob);
end ToXmlType_;

Open in new window

bilparAsked:
Who is Participating?
 
bilparAuthor Commented:
I found an answer.  Is it cheating if I answer my own questions?  Anyway, thought I would share the solution.

For xml larger than 4K use the dbms_xmldom.writetoclob() function.  Dont forget to open (and close) a handle to the clob.
/*
************************************************************************
ToXmlType_() - function
************************************************************************
***********************************************************************/
function ToXmlType_
(
    p_node          in      dbms_xmldom.DOMNode
)
return sys.xmltype as
    l_xml           sys.xmltype;
    l_clob          clob;
begin
    dbms_lob.createtemporary (l_clob, TRUE);
    dbms_xmldom.writetoclob(p_node, l_clob);
    l_xml := sys.xmltype.createXml(l_clob);
    dbms_lob.freetemporary (l_clob);
    return l_xml;
end ToXmlType_;

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.