10 Replies Latest reply: Mar 28, 2013 5:07 PM by odie_63 RSS

    Internal limit on xmlquery CLOB set to 4000 chars?

    pl_sequel
      Running on Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
      PL/SQL Release 11.2.0.1.0 - Production

      Stumbled upon this error. Would not have noticed it had it not caused a dom4j parsing error in my Java application. Noticed one text node in my xml document was being truncated on an entity , which was causing a parsing error. (missing ; after entity declaration)

      Upon further debugging, noticed the text node was truncated to exactly 4000 characters. (Actual node in source xmltype document is 26k.)

      I was producing an xml document from the xmltype column, and relational table using an xmlquery.
      select xmlquery(
        '<LIST>{for $i in fn:collection("oradb:/ORT/DATA_CAPTURE_RECORD"),                
      $j in fn:collection("oradb:/ORT/DATA_CAPTURE_RECORD_CONTENT"),
      $k in $j/ROW/CONTENT_XML/activity
      where $i/ROW/DATA_CAPTURE_RECORD_ID = $j/ROW/DATA_CAPTURE_RECORD_ID                
      and $j/ROW/ACTIVE_IND=1    
      order by $k/title descending
      return <RECORD>{($i/ROW/DATA_CAPTURE_RECORD_ID,$k//title,$k//titre,$k//summary,$k//sommaire,$i/ROW/DRAFT_VPATH)}</RECORD>}</LIST>'
        RETURNING CONTENT).getClobVal() as xmlContent
         FROM DUAL;
      Noticed the "summary" element in my result was truncated in the middle of an entity declaration.

      Any ideas? Known issue?

      Thanks
        • 1. Re: Internal limit on xmlquery CLOB set to 4000 chars?
          odie_63
          Hi,

          Anything we can use to reproduce the issue ?

          Thanks.
          • 2. Re: Internal limit on xmlquery CLOB set to 4000 chars?
            pl_sequel
            Hi Odie,

            Thanks fro the quick reply. I think I may be missing something obvious here...but I can't seem to reproduce with a simple test case. Here's what I got so far.
            create table temp (id number, content_xml xmltype);
            /
            insert into temp
            select 1, xmlelement("list",xmlelement("record",rpad(empty_clob(),14001, '*'))) from dual;
            /
            commit;
            /
            
            select xmlquery('for $i in fn:collection("oradb:/ORT/TEMP"),
            $j in $i/ROW/CONTENT_XML/list
            where $i/ROW/ID=1
            return <result>{($i/ROW/ID,$j/record)}</result>' returning content).getclobval() from dual;
            Full string is returned is my result.
            • 3. Re: Internal limit on xmlquery CLOB set to 4000 chars?
              pl_sequel
              Hi Odie,

              I think i just found the cause...forgot to mention I also had an xml index created. After creating the same index on my temp table, and running the same query, my text node is truncated to 4000 characters.
              CREATE INDEX temp_xmlidx ON temp (content_xml) INDEXTYPE IS XDB.XMLIndex;
              Does this make sense?

              If I drop my index on my actual table, my text node is no longer truncated. I have to admit, there is no reason to have all xml elements indexed...especially ones containing large text nodes.

              Edited by: pl_sequel on Mar 28, 2013 2:12 PM
              • 4. Re: Internal limit on xmlquery CLOB set to 4000 chars?
                odie_63
                A similar issue has been discussed here :
                {message:id=10266981}

                Besides dropping the index, no solution was found back then.

                I don't reproduce on 11.2.0.2 though, the text node is not truncated.

                Edited by: odie_63 on 28 mars 2013 20:32
                • 5. Re: Internal limit on xmlquery CLOB set to 4000 chars?
                  pl_sequel
                  Thanks Odie, I'm going to read up more on structured xml indexes... since i will be querying specific elements within my xml, and will need to optimize the performance as much as I can.
                  • 6. Re: Internal limit on xmlquery CLOB set to 4000 chars?
                    KnightOfBlueArmor
                    Actually, what happens is not that the value gets truncated - it's that the 4000th character is a null terminator '\0'. This only happens with the "thin" JDBC driver; with the OCI or OCI8 driver, it doesn't happen.

                    You actually can reproduce it in SQL Developer. Just create a table with an XMLType column in it and a large XML document (> 4000 characters). When you select it back with XMLSerialize() or getClobVal(), double-click on the result - at the 4000th position, you'll see something that looks like a space separating two characters, like:
                    <BOOK_T ITLE>
                    When you copy and paste that into a text editor, you'll see everything up to that character, but nothing past it.
                    I've been meaning to submit an SR but I haven't had time to create all the JDBC code needed to reproduce this. It happens on 11.2.0.3 too.

                    Edited by: KnightOfBlueArmor on Mar 28, 2013 12:50 PM
                    • 7. Re: Internal limit on xmlquery CLOB set to 4000 chars?
                      pl_sequel
                      hmm.. not so sure...my test case above doesn't exhibit that behaviour. I loaded an xmltype with 14000 characters...and was able to select it back in SQL Developer with nothing missing. Only when I created the index did the truncation occur...due to how Oracle internally manages storage of the indexed content... which kind of makes sense for performance reasons.
                      • 8. Re: Internal limit on xmlquery CLOB set to 4000 chars?
                        KnightOfBlueArmor
                        Did you select it back with XMLSerialize or getClobVal()? It only seems to occur when converted to a CLOB value for whatever reason.
                        • 9. Re: Internal limit on xmlquery CLOB set to 4000 chars?
                          pl_sequel
                          select xmlquery('for $i in fn:collection("oradb:/ORT/TEMP"),
                          $j in $i/ROW/CONTENT_XML/list
                          where $i/ROW/ID=1
                          return <result>{($i/ROW/ID,$j/record)}</result>' returning content).getclobval() from dual;
                          getClobVal()...
                          • 10. Re: Internal limit on xmlquery CLOB set to 4000 chars?
                            odie_63
                            KnightOfBlueArmor wrote:
                            Actually, what happens is not that the value gets truncated - it's that the 4000th character is a null terminator '\0'. This only happens with the "thin" JDBC driver; with the OCI or OCI8 driver, it doesn't happen.
                            No, it's a different problem, more like what is described in this thread : {thread:id=2467686}

                            The scenario at hand clearly involves the index and how Oracle uses the partial node value to resolve the query.

                            Edited by: odie_63 on 28 mars 2013 23:01