1 2 3 Previous Next 44 Replies Latest reply on Mar 31, 2011 7:09 PM by mdrake-Oracle Go to original post
      • 15. Re: ORA-31167: 64k size limit for XML node
        mdrake-Oracle
        Unfortunately you are out of luck. You should be able to insert the document with

        INSERT INTO INFODOC VALUES( x_assetid, x_sourceid, x_synopsis, o_date, x_audience, x_collection, xmltype(tempCLOB,null,1,1));

        However you cannot use any of the XML operators, including extract(), extractValue(), existsNode(), or the new XMLTable, XMLQuery operators on that document.

        11g contains the fix for this issue. Can you upgrade the database. It has already been determined that the fix is not backportable.
        • 16. Re: ORA-31167: 64k size limit for XML node
          639321
          Thanks for rapid response.

          Update is possible, but not desirable.

          Can you point me to any material on update issues I'm likely to encounter?
          • 17. Re: ORA-31167: 64k size limit for XML node
            mdrake-Oracle
            From an XML DB perspective 11g has been pretty stable, others may want to add thier 2 cents. I don't think you'd encounter many problems in 11g that wouldn't manifest in earlier releases. However I'm not sure what else your instance is used for, so can't really comment on other areas.
            • 18. Re: ORA-31167: 64k size limit for XML node
              643156
              Hello!

              I am using Oracle 11g (11.1.0.6.0) to store large XML documents with large node of binary encoded data (Base64). According to the error messages these in essence text nodes are to big.

              After reading all the before messages on 11 I wonder if the 64K limitations is now solved or not - or only in a certain version of the 11 DB?

              Actually my error message is different: ORA-30951: Element or attribute at Xpath %s exceeds maximum length.
              I came to this Post, because I also have a too large text node - at least thats what I think, so I think its a match.
              I would be very thankful for any hint how to solve it,

              Thank you all!
              • 19. Re: ORA-31167: 64k size limit for XML node
                mdrake-Oracle
                At a guess the Base64 data has been mapped to the default of RAW(4000). You need to annotated the XML Schema to tell Oracle that you want it to allocate a BLOB for managing this data. You will need to add an xdb:SQLType="BLOB" to the element in question, and then re-register the XMLSchema. At that point you should be OK
                • 20. Re: ORA-31167: 64k size limit for XML node
                  643156
                  Great - that's the solution. I was so focused that I could not see anything else - even though I read about that in the docu!

                  Thank you!
                  • 21. Re: ORA-31167: 64k size limit for XML node
                    677026
                    Hi,

                    We are facing the similar issue when we try to parse more than 96k size of XML using extract. pls. let me know if there is any workaround to parse XML of large size more than 64k

                    Thanks
                    • 22. Re: ORA-31167: 64k size limit for XML node
                      706061
                      Hi,

                      Currently we are using oracle 10.2.X with XMLTYPE as column in a table. I have a java program which prepares the clob and inserts the clob into xmltype column. It is throwing following exception:

                      java.sql.SQLException: ORA-31167: XML nodes over 64K in size cannot be inserted
                      ORA-06512: at "SYS.XMLTYPE", line 254
                      ORA-06512: at line 1

                      Clob object is created as given below:
                      CLOB tempClob = CLOB.createTemporary(conn, true, 10);
                      tempClob.open(1);
                      Writer tempClobWriter = tempClob.setCharacterStream(0L);
                      tempClobWriter.write(xml);
                      tempClobWriter.flush();
                      tempClobWriter.close();
                      tempClob.close();

                      I read in another thread that there is a fix in 11g. But we can't upgrade to 11g at this point of time.
                      Please suggest me any workaround for this.

                      Thanks,
                      Ashok.
                      • 23. Re: ORA-31167: 64k size limit for XML node
                        mdrake-Oracle
                        Is the XMLType Schema Based or Non Schema Based. If it's schema based then there is NO workaround. You will HAVE to upgrade to 11.1.x. If it's non schema based and you do not want to use any XML operators on it then you can construct the XMLType with the Well formed flag and retrieve it as a CLOB.
                        • 24. Re: ORA-31167: 64k size limit for XML node
                          706061
                          Thank You very Much mdrake
                          We are not referring any schema in the XML document. You are mentioning that I need to construct the XML type with the well formed. Please provide any references or code snippets in this regard. Will the actual error ie. "ORA-31167 : 64k size limit for XML node" will be resloved with your solution? Awaiting for your valuble answers.
                          • 25. Re: ORA-31167: 64k size limit for XML node
                            Marco Gralike
                            Mark refers to this...

                            http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/t_xml.htm#i1009842
                            constructor function XMLType(
                               xmlData IN clob,
                               schema IN varchar2 := NULL,
                               validated IN number := 0,
                               wellformed IN Number := 0)
                            return self as result deterministic;
                            eg. REF CURSOR example
                            SQL> select xmltype(cursor(select * from dual)) from dual;
                            
                            XMLTYPE(CURSOR(SELECT*FROMDUAL))
                            --------------------------------------------------------------------------------
                            <?xml version="1.0"?>
                            <ROWSET>
                             <ROW>
                              <DUMMY>X</DUMMY>
                             </ROW>
                            </ROWSET>
                            
                            
                            SQL> select xmltype(cursor(select * from dual),NULL, 0, 1) from dual;
                            
                            XMLTYPE(CURSOR(SELECT*FROMDUAL),NULL,0,1)
                            --------------------------------------------------------------------------------
                            <?xml version="1.0"?>
                            <ROWSET>
                             <ROW>
                              <DUMMY>X</DUMMY>
                             </ROW>
                            </ROWSET>
                            
                            
                            SQL> select xmltype(cursor(select * from dual),NULL, 0, 1).getClobVal() from dual;
                            
                            XMLTYPE(CURSOR(SELECT*FROMDUAL),NULL,0,1).GETCLOBVAL()
                            --------------------------------------------------------------------------------
                            <?xml version="1.0"?>
                            <ROWSET>
                             <ROW>
                              <DUMMY>X</DUMMY>
                             </ROW>
                            </ROWSET>
                            • 26. Re: ORA-31167: 64k size limit for XML node
                              706061
                              Hi,

                              As my XML is non schema based I have tried with below code:

                              XMLType xmlData = XMLType.createXML(conn, xmlString);
                              opstmt.setObject(1, xmlData)

                              I am getting the below error:
                              Exception in thread "main" java.lang.NoClassDefFoundError: oracle/jdbc/oci8/OCIDBAccess

                              I have verified ojdbc14.zip(which supports 10g) and found that above package is missing.

                              Then I have replaced it with another ojdbc14.jar which supports 9i (my database is 10.2.x). Which is having the above package.
                              I am getting the below exception:
                              Exception in thread "main" java.lang.NoSuchFieldError: conversion
                                   at oracle.xdb.XMLType.<init>(XMLType.java:529)
                                   at oracle.xdb.XMLType.createXML(XMLType.java:361)

                              Please suggest me how to proceed on this.
                              How to resolve this issue by using ojdbc14.zip(which supports 10g) as my db version is 10.2.x.

                              Your reply will be much appriciated.

                              Thanks.
                              • 27. Re: ORA-31167: 64k size limit for XML node
                                mdrake-Oracle
                                Go back to the original code that uses the temp clob

                                Somewhere you must be preparing a statement that passing the tempCLob to the XMLType constructor

                                eg
                                "insert into my_table (XMLCOL) values (XMLType(:1))"
                                and change it to
                                "insert into my_table (XMLCOL) values (XMLType(:1,null,0,1))"
                                And this should bypass the check.. The Insert should be faster too.

                                However note

                                You CANNOT use any of our XML operators on that column. This means no INDEXING, no EXTRACTVALUE, no EXTRACT, no EXISTSNODE, no XMLQUERY, no XMLTABLE, no XMLTRANSFORM no TRANSFORM, no XMLSERIALIZE etc. You cannot retrieve the content as XML, only as a CLOB, eg you MUST use the _.getClobVal()_ method anytime you access the content of this column, absolutely no exception. Performing any operation that requires us to parse the XML will result in the error being thrown, and this is not a bug, it is by design.

                                Edited by: mdrake on Jun 11, 2009 6:37 AM

                                Edited by: mdrake on Jun 11, 2009 6:39 AM
                                • 28. Re: ORA-31167: 64k size limit for XML node
                                  706061
                                  Thanks a lot Mark :).

                                  Looks like it is working. Still need to check while retrieving are we using any xml operations or not.
                                  Anyhow thanks for the timely response and help.
                                  • 29. Re: ORA-31167: 64k size limit for XML node
                                    mdrake-Oracle
                                    Please note I would still strongly advise migrating to 11.1.x at the earliest possible opportunity.