[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1954
  • Last Modified:

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

0
bilpar
Asked:
bilpar
1 Solution
 
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now