1 2 3 Previous Next 44 Replies Latest reply on Mar 31, 2011 7:09 PM by mdrake-Oracle Go to original post
      • 30. Re: ORA-31167: 64k size limit for XML node
        722112
        thank you!!!
        collection, xmltype(tempCLOB,null,1,1));
        good!!!
        • 31. Re: ORA-31167: 64k size limit for XML node
          Chris10263447
          I am now running into this issue in 10g as well. Assuming that migrating to 11g would eliminate the issue with the 64k limit, what would the new limit be?

          Thanks - Chris.
          • 32. Re: ORA-31167: 64k size limit for XML node
            mdrake-Oracle
            In 11gR1 the limit should be 2G/4G depending on DB character set.
            • 34. Re: ORA-31167: 64k size limit for XML node
              afret1
              I'm running into this limit using Jason Strub's flex_ws_api package to call a BIP web service from APEX. I'm having trouble trying to store the xml in a table a retrieve using the .getclob construct.
              First, I have created the table as "create table myXML (col1 XMLType)"
              Next, I trap the 31167 error in the exception handle of the function make_request an attempt to insert a value into a table and return a clob, but this errors out. The call "return xmltype(l_myclob)" fails with another 31167 error. Can anybody help with a work around?

              Alex.



              The exception handler code is (where l_myclob is defined as a clob):


              exception when others then
              if sqlcode = -31167 then
              insert into myXML values (xmlparse(DOCUMENT l_clob WELLFORMED));

              select x.col1.getClobVal() into l_myclob from hradmin.myXML x ;

              return xmltype(l_myclob);
              .....

              end if;
              • 35. Re: ORA-31167: 64k size limit for XML node
                mdrake-Oracle
                1. There is no safe guaranteed workaround for this other than upgrading to 11.1x

                The unsafe workarounds are..

                Using XMLType store as CLOB

                1. Get the content using XML.getClobVal()

                2. You need to insert / update using "insert into tab (xmlcol) values (XMLTYPE(CLOB,null,1,1))"
                • 36. Re: ORA-31167: 64k size limit for XML node
                  585262
                  We are on Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

                  We are still facing issue while retrieving XML as CLOB from a XMLTYPE Column having >64k XML node size.


                  The session hangs indefinitely when trying to
                  SELECT e.CSXML_DOC.extract('/').getClobVal() as xmlclobdoc
                  FROM CS_XML_DATA e where e.CSXML_GOID = 100 ;


                  Table Defination:


                  CREATE
                  TABLE ."CS_XML_DATA"
                  (
                  "CSXML_GOID" NUMBER(15,0) ,
                  "CSXML_DOC" "SYS"."XMLTYPE"

                  )
                  SEGMENT CREATION IMMEDIATE 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 FLASH_CACHE DEFAULT
                  CELL_FLASH_CACHE DEFAULT
                  )
                  TABLESPACE "USERS" XMLTYPE COLUMN "CSXML_DOC" STORE AS BASICFILE BINARY XML
                  (
                  TABLESPACE "USERS" 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
                  FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
                  )
                  ALLOW NONSCHEMA DISALLOW ANYSCHEMA ;


                  Appreciate any help.
                  • 37. Re: ORA-31167: 64k size limit for XML node
                    mdrake-Oracle
                    OK First I suspect that the performance is due to the size of the document, not the fact you have a large node (or two) in the document. What is the size of the documemt

                    Secondly a number of questions

                    1. Why on earth are you doing extract('/').getClobVal(). What do you expect this to achieve, other than forcing us to build a DOM, eat a significant amount of memory and then print out the entire document...

                    In earlier versions and extract('/') would force a pretty print. I doesn't do that any more, and there are supported ways of doing that if you need to do so.

                    2. If the document is stored as CLOB then do XMLType.getCLobVal() (note no extract('/'). We will simply give you the bytes in the document...

                    3. Why are you storing documents as CLOB in 11gR2. There is no good reason to do so, try using STORE AS SECUREFILE BINARY XML...

                    Hint.. We'll be deprecated CLOB storage soon, so do your self a favour switch now.
                    • 38. Re: ORA-31167: 64k size limit for XML node
                      585262
                      Size of the document is around 1.3MB.
                      An element in the file is a continuous chunk of approk 900K data and contains a lot of multi-byte characters.

                      1) The document is stored as XMLTYPE with STORE AS SECUREFILE BINARY XML

                      CREATE
                      TABLE "CS_CONTENT_XML_DATA"
                      (
                      "CSXML_GOID" NUMBER(15,0),
                      "CSXML_DOC" "SYS"."XMLTYPE"
                      )
                      SEGMENT CREATION IMMEDIATE 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 FLASH_CACHE DEFAULT
                      CELL_FLASH_CACHE DEFAULT
                      )
                      TABLESPACE "USERS" XMLTYPE COLUMN "CSXML_DOC" STORE AS SECUREFILE BINARY XML
                      (
                      TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192 NOCACHE LOGGING
                      NOCOMPRESS KEEP_DUPLICATES STORAGE(INITIAL 106496 NEXT 1048576 MINEXTENTS 1
                      MAXEXTENTS 2147483645 PCTINCREASE 0 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT
                      CELL_FLASH_CACHE DEFAULT)
                      )
                      ALLOW NONSCHEMA DISALLOW ANYSCHEMA ;

                      2) I have a business need to convert the XML stored in a XMLTYPE column to a CLOB using PL/SQL Function.

                      3) When I do try XMLTYPE.getCLOBVal() the session runs indefinitely, Consuming a significant amount of memory.

                      4) This error only occurs when a element/node has lots of data

                      I can send you the XML file,and the loader script,

                      let me know.

                      Appreciate your help.
                      • 39. Re: ORA-31167: 64k size limit for XML node
                        mdrake-Oracle
                        Ok, this is looking more interesting now :)

                        1. Can you try using XMLSERIALIZE instead of getClobVal()..
                        select XMLSERIALIZE(DOCUMENT CSXML_DOC as CLOB)
                          from  "CS_CONTENT_XML_DATA"
                        ...
                        Are you seeing Oracle use exceessive amounts of memory / and or CPU.

                        Can you open a tar and provide the document in question...

                        Thx.
                        M.
                        • 40. Re: ORA-31167: 64k size limit for XML node
                          585262
                          I did try with XMLSerialize, still did not work.

                          It was consuming 50% of the CPU, had to bounce the server.

                          Have opened a tar : Bug#9468270, oracle was able to reproduce it.

                          101.xml

                          Let me know, if I can email you the XML document.

                          Thanks,
                          • 41. Re: ORA-31167: 64k size limit for XML node
                            761701
                            Hi user582259,

                            I seem to be running into the same issue (allthough on a windows box) in an extract() operation. Did you get any sensible response so far ? I can see the bug you're referring to, but it's marked as a duplicate and I cannot see the base bug it's linked to. The issue is (as you probably know ;o) ) really annoying because we cannot anticipate its occurrence for a given document and therefore the whole process hangs for the rest of the batch.


                            Best regards,
                            Benjamin
                            • 42. Re: ORA-31167: 64k size limit for XML node
                              zameer-OC
                              I am facing the similar issue,
                              We are using 10.2.x version db.
                              I want to retrieve a value from a node , which is from a clob type column
                              but this document has binary data, in some other nodes.
                              when i am executing the select statement, am getting the error ORA-31167.
                              The node which i am accessing has only a 2 digit number value.

                              below is the sql, am executing

                              select
                              to_number(extractvalue(value(xml_path),'/objectiveeorow/objectiveid')) objectives
                              from
                              hr_api_transactions hat,per_objectives ppo,
                              table(xmlsequence(extract(xmlparse(document transaction_document wellformed),'/transaction/transcache/am/txn/eo/objectiveeorow'))) xml_path
                              where 1=1
                              and hat.assignment_id=3020
                              and to_number(extractvalue(value(xml_path),'/objectiveeorow/objectiveid')) = ppo.objective_id(+)
                              and hat.transaction_ref_table = 'per_personal_scorecards'
                              and hat.transaction_ref_id =:p_scorecard_id ;

                              thanks
                              zamora
                              Please help, if there any workaround.
                              • 43. Re: ORA-31167: 64k size limit for XML node
                                Marco Gralike
                                hmm, you are not kidding right...?

                                - clob
                                - xmlparse(document transaction_document wellformed)
                                - where 1=1 ???

                                Workaround is easy. Upgrade to 11.x

                                Edited by: Marco Gralike on Apr 1, 2011 12:23 AM
                                • 44. Re: ORA-31167: 64k size limit for XML node
                                  mdrake-Oracle
                                  Beating the Dead Horse...

                                  It is possible to store an XML document, cintaining one or text() nodes which are larger than 64K in databases prior to 11gR1 using XMLType store as CLOB.

                                  It is not possible to do ANYTHING other than store and retrieve the entire document. No matter how many ways you try and creep up on the problem we will throw an error as soon as we are forced to parse the content of the document.

                                  The two solutions, are

                                  (1) return the entire document to the application and let the application decided how to process the XML.

                                  (2) upgade to database 11gR1 where this problem is fixed

                                  The fix is not backportable...
                                  1 2 3 Previous Next