1 2 3 Previous Next 44 Replies Latest reply: Mar 31, 2011 2:09 PM by mdrake RSS

    ORA-31167: 64k size limit for XML node

    464105
      Hi,

      I have an application that archives XML documents provided by another organization. The other organization, a government regulatory body, controls the format and content of the documents. Many of the documents have large text nodes consisting of hundreds of kilobytes of CSV data.

      This application was developed against Oracle 9i and runs fine on that product. Testing against Oracle 10g, I get "ORA-31167: XML nodes over 64K in size cannot be inserted".

      As I read it, 64k is a fixed limit in Oracle10g. This limit did not seem to appear in Oracle9i however?

      What are my options for upgrading this application to Oracle10g? I can think of:

      1) Stay on 9i.
      2) Mess around with the XML content. This kind of compromises the purpose of the application, which is to archive the documents unchanged.
      3) Store the documents as CLOBs and forego the XML DB features.
      4) Abandon Oracle and use another XML database???!

      I've been really impressed with Oracle XML DB to date. I find this limitation quite hard to believe. I'm really hoping there is a simple alternative that I'm missing.

      Any ideas?

      John Hurst
      Wellington, New Zealand
        • 1. Re: ORA-31167: 64k size limit for XML node
          mdrake
          John

          The 64K limitation has has been present in every release of XML DB, including 9i. We intend lifting it a future release of the database but at the moment I cannot state when that might be in a public forum.

          If all you ever do is insert the entire document into a CLOB based XMLType column and then retrieve the entire document as a CLOB you can sometime get away with storing XML with nodes that exceeds this limitation.

          In 9i operations on an XMLType that contained nodes > 64K would simply truncate the text() nodes at 64K. This meant that if none of the operations access the node or the contents of the node were not carefully examined the problem never manifested itself. However it could have very nasty side-effects causing data to be lost during updates on unrelated nodes.

          In 10.x if we detect a node > 64K while parsing the document we throw an error.

          One technqiue which has been used quite successfully is to stip these large nodes and store them as a seperate non-xml document in the XDB repository. The content of the large node is replaced with a URL which points to the document containing the large node. Obviously this has to be done by the process that is loading the documents into the database. As you state this may or may not be a viable soln for you.

          Depending on the kind of operations you need to do CLOB storage, or XMLType with CLOB storage may work for you. In this case you will need to insert the content using the new (10.1.x) xmlparse

          If you have the following
          --
          -- Create a CLOB based XMLType
          --
          create table myXML of XMLType 
          /
          --
          -- insert a CLOB containing XML into the table without parsing it
          --
          insert into myXML values (xmlparse(DOCUMENT someClob WELLFORMED))
          /
          --
          -- Retrieve the XML as a CLOB without parsing it
          --
          select value(x).getClobVal() from myXML 
          /
          You can use XMLType to store documents with nodes that exceed the 64K node limit. However any operation that requires us to parse the XML, such as extract(), extractValue(), existsNode() etc will fail.


          Be very careful. There is a significant likely hood that XML DB will slighently truncate the nodes in 9i. In 10.x it complains about nodes it cannot handle.
          • 2. Re: ORA-31167: 64k size limit for XML node
            464105
            Hello Mark,

            Thank you very much for your quick and detailed reply. The Oracle forums have given superb feedback on the few occasions I've used them. I may be tempted to use them more -- :-) but I should do my homework first.

            You really gave me a fright with that truncation info! I thought briefly perhaps the DB was truncating my data and I might have lost (a lot of) data in production (without even realising it!). But no, I have stored, and can retrieve, intact XML documents with 500kB nodes. No data appear to have been lost.

            My table is defined:

            CREATE TABLE xml_document (
            id INTEGER NOT NULL,
            file_type VARCHAR2(200) NOT NULL,
            file_name VARCHAR2(4000) NOT NULL,
            file_size INTEGER NOT NULL,
            file_date TIMESTAMP NOT NULL,
            upload_date TIMESTAMP NOT NULL,
            xml_data XMLTYPE NOT NULL
            );

            I'm storing the data via JDBC, basically with:
            XMLType xt = XMLType.createXML(oracleConnection, document);
            ps.setObject(col, xt);

            I retrieve with (basically):
            XMLType xt = (XMLType) rs.getObject(col);
            if (rs.wasNull()) {
            return null;
            }
            else {
            return xt.getDOM();
            }

            I take it from your note I've been lucky so far because none of these result in certain XML operations such as extract(), extractValue() etc.

            On the SQL side I currently don't use XML-specific queries, rather I'm using a CONTAINS with a CONTEXT. Thinking about it now, I believe I did try to create an XML-based index using extract() or exatrctValue() on this table, and it failed with an error message I didn't understand. Sounds like the failure may have been caused by my oversized nodes.
            However it could have very nasty side-effects causing data to be lost during updates on unrelated nodes.
            You are talking about SQL UPDATE, right?

            Am I safe from these side effects if I use only INSERT and DELETE, and never UPDATE (or UPDATE only on non-XML columns)?

            It sounds like I can get away with large nodes if I am careful in the feature set I use, and I work around the size limit on INSERT using the xmlparse() technique you give. That's probably the best way forward for this application.

            Thanks again for the info.

            John Hurst
            Wellington, New Zealand
            • 3. Re: ORA-31167: 64k size limit for XML node
              mdrake
              You're OK because you haven't done any updates.. It is update operations via updateXML() which can cause silent truncation can take place under certain circumstances and early versions of 9.2.x, and that's the reason we decided to throw the error in 10.1.x...

              The trick here is to make sure that XDB never actually parses the XML data. We will throw the error the second we detect a large text() node. Basically in 10.x if you ensure that the XML is never parsed everthing is fine. XML Parse can do this if the following is true (1) The target XML column is non-schema based (true in your case) and (2) you provide the WELLFORMED keyword in the XMLParse() call. This is an Oracle extension that basically tells Oracle you are will guarantee that the XML is well formed. At this point if the source is CLOB and the target is XMLType stored as CLOB we simply copy bytes from source to target without doing any XML specific processing.

              I would probably recommend avoiding the use of getDOM(). You need to get from the CLOB version to an XDK DOM without parsing. I'm guessing you use a thin JDBC connection and that's why its working for you. With the thin connection we serialize into a CLOB, pass the CLOB over the wire and create an instance of oracle.xml.parser.v2.xmldocument.

              The way to be sure of this would be to call XMLType.getClobVal() and then parse the CLOB.

              The other advise will be to upgrade to which ever patchset / release contains the large node handling implementation as soon as it's released
              • 4. Re: ORA-31167: 64k size limit for XML node
                464105
                Hi Mark,

                Thanks again for your additional information. I will apply your advice in my application, and it should get us to where the current functionality works on both 9i and 10g. I'm relieved to hear we're not at risk for losing data!

                Indeed we are using the thin JDBC driver. It's been nice to store/retrieve the XML data as a DOM Document ... the store/retrieve service layer methods are exposed as web services and this makes for very concise straightforward code. But if you recommend sticking to CLOB to avoid (the possibility of) truncation problems ... I'll do that I guess.

                Currently we don't use XML-specific features in the database, but it was always my hope to do so, should the customer come up with requirements beyond simple text search. This is very much a possibility in the future, so add me to the list of people looking forward to nodes over 64kB.

                Thanks again for your comprehensive response.

                John Hurst
                Wellington, New Zealand
                • 5. Re: ORA-31167: 64k size limit for XML node
                  mdrake
                  Here's the safe way of using getting the DOM you are currently working with...
                    public XMLDocument getXMLDocument(XMLType xmlType, boolean includeContent)
                    throws SQLException, IOException, SAXException
                    {
                       XMLDocument xml;
                       InputStream is = xmlType.getClobVal().getStream();
                       xml = printAndParse(is);
                       return xml;
                    }
                  
                    public static XMLDocument printAndParse(InputStream is)
                    throws SQLException, IOException, SAXException
                    {
                      DOMParser parser = new DOMParser();
                      parser.setErrorStream(System.out);
                      parser.showWarnings(true);
                      parser.setPreserveWhitespace(false);
                      parser.parse(is);
                      XMLDocument xmlDocument = parser.getDocument();
                      return xmlDocument;
                    }
                  • 6. Re: ORA-31167: 64k size limit for XML node
                    464105
                    Hi,

                    OK, thanks, I now have code that works in Oracle 10g with my large nodes.

                    Apparently Oracle 9i doesn't support the DOCUMENT ? WELLFORMED syntax you gave for the INSERT. So, I've ended up with two alternative schemes in my DAO: one just as I had it before for 9i, and a new one for 10g, using WELLFORMED as you suggested for the INSERT, and using code adapted from yours for getting the DOM from the CLOB in a ResultSet. I guess the main point is the 10g code never uses XMLType.

                    The client code (of the DAO) is unchanged -- still uses W3C Document, and doesn't see any CLOBs.

                    Thanks again.

                    John Hurst
                    Wellington, New Zealand
                    • 7. Re: ORA-31167: 64k size limit for XML node
                      357517
                      The limitation also applies to document root nodes.
                      Will there be any patch for Oracle 10g, I read about patch 10.2.04, that will fix the 64k limitation for nodes?
                      • 8. Re: ORA-31167: 64k size limit for XML node
                        mdrake
                        This limit will not be resolved until 11.1.x.
                        • 9. Re: ORA-31167: 64k size limit for XML node
                          357517
                          Thank you very much for your reply!
                          Is there any chance to get an backward patch?
                          • 10. Re: ORA-31167: 64k size limit for XML node
                            mdrake
                            No.. :(
                            • 11. Re: ORA-31167: 64k size limit for XML node
                              452507
                              Can we confirm this is already fixed in the currently released version of Oracle 11g ? I have in front of me a box that says 11.1.0.6.0 for Linux x86 - does that contain the 64k fix, or do we need to wait for a further patch set ? I'll need this on 64bit linux as well. I see this in the 11g docs:

                              "Support for Large XML Nodes
                              The previous 64K limit on text nodes and attribute values has been lifted. Text nodes and attribute values are no longer limited in size to 64K bytes each."
                              http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28369.pdf
                              • 12. Re: ORA-31167: 64k size limit for XML node
                                Marco Gralike
                                Never say never, but as the official manual states, this limitation is not applicable in 11.1.0.6 (aka 11gR1) for all OS versions released.
                                • 13. Re: ORA-31167: 64k size limit for XML node
                                  mdrake
                                  Any issues with nodes >64K in 11.1.0.6.0 or later are bugs and will be treated as such. I am aware of one issue to date, bug 6440737 which has been patched.
                                  • 14. Re: ORA-31167: 64k size limit for XML node
                                    639321
                                    I inherited an application that was developed against Oracle 9.2.0.8, and which was recently upgraded to Oracle 10.2.0.3. I get "ORA-31167: XML nodes over 64K in size cannot be inserted" on about 5% of the documents, unfortunately they are the most important documents.

                                    Since the only data retrieval is with getCLOB(), I have attempted the xmlparse workaround xmlparse(DOCUMENT someCLOB WELLFORMED) yet still receive the ORA-31167 error.

                                    @ After more deugging, the getXMLFromFile() causes the error not the insert, while a getFileContent()successfully completes.

                                    Can you suggest another way to extract the values from the XML?
                                    Any chance of a 10g patch for this limit?

                                    Thanks,


                                    Procedure SQL:

                                    BEGIN
                                    freeCLOBOnExit := tempCLOB IS NULL;
                                    DBMS_LOB.createTemporary(tempCLOB, TRUE, DBMS_LOB.SESSION);


                                    xmlFileContent := xdb_utilities.getXMLFromFile(fileName, directoryName, tempCLOB=>tempCLOB);

                                    x_assetid := xmlFileContent.extract('/cds:document/cds:document-spec/cds:asset-id/text()', 'xmlns:cds="http://cds.central"').getStringVal();
                                    x_sourceid := xmlFileContent.extract('/cds:document/cds:document-spec/cds:source-id/text()', 'xmlns:cds="http://cds.central"').getStringVal();
                                    x_synopsis := xmlFileContent.extract('/cds:document/cds:document-spec/cds:synopsis/text()', 'xmlns:cds="http://cds.central"').getStringVal();
                                    x_date := xmlFileContent.extract('/cds:document/cds:document-spec/cds:update-date/text()', 'xmlns:cds="http://cds.central"').getStringVal();
                                    o_date := get_xml_date(x_date);
                                    x_audience := xmlFileContent.extract('/cds:document/cds:document-spec/cds:audience/text()', 'xmlns:cds="http://cds.central"').getStringVal();
                                    x_collection := xmlFileContent.extract('/cds:document/cds:document-spec/cds:collection-name/text()', 'xmlns:cds="http://cds.central"').getStringVal();


                                    DELETE from INFODOC where asset_id = x_assetid;
                                    INSERT INTO INFODOC VALUES( x_assetid, x_sourceid, x_synopsis, o_date, x_audience, x_collection, xmlparse(DOCUMENT tempCLOB WELLFORMED));
                                    COMMIT;

                                    IF (freeCLOBOnExit) THEN
                                    dbms_lob.freeTemporary(tempCLOB);
                                    ELSE
                                    dbms_lob.trim(tempCLOB, 0);
                                    END IF;
                                    END;


                                    Table DDL:
                                    CREATE TABLE "SYSTEM"."INFODOC"
                                    (     "ASSET_ID" VARCHAR2(35 BYTE),
                                         "SOURCE_ID" VARCHAR2(25 BYTE) NOT NULL ENABLE,
                                         "SYNOPSIS" VARCHAR2(256 BYTE) NOT NULL ENABLE,
                                         "UPDATE_DATE" DATE NOT NULL ENABLE,
                                         "AUDIENCE" VARCHAR2(15 BYTE) NOT NULL ENABLE,
                                         "COLLECTION_NAME" VARCHAR2(20 BYTE) NOT NULL ENABLE,
                                         "XMLDOC" "SYS"."XMLTYPE" NOT NULL ENABLE
                                    ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
                                    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
                                    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
                                    TABLESPACE "INFODOC"
                                    XMLTYPE COLUMN "XMLDOC" STORE AS CLOB (
                                    TABLESPACE "INFODOC" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10
                                    NOCACHE LOGGING
                                    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
                                    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)) ;

                                    Message was edited by:
                                    dauza

                                    Message was edited by:
                                    dauza
                                    1 2 3 Previous Next