13 Replies Latest reply: Jun 8, 2012 5:10 AM by odie_63 RSS

    32k limit in Reading XML

    344384
      Hi

      My procedure connects to a webservice and gets the data as XML

      This is what my code looks like
      HTTP_RESP := UTL_HTTP.GET_RESPONSE (HTTP_REQ);
      
      UTL_HTTP.READ_TEXT (HTTP_RESP, SOAP_RESPOND);
      
      SOAP_RESPOND :=
               XMLTYPE (SOAP_RESPOND).
               EXTRACT (
                  '/soap:Envelope/soap:Body/Streets_Events_GetResponse/Streets_Events_GetResult/text()',
                  'xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/",
                                          xmlns="http://webapi/"').
               GETCLOBVAL ();
      
            Xml_Result :=
               XMLTYPE (
                  DBMS_XMLGEN.CONVERT (SOAP_RESPOND, DBMS_XMLGEN.ENTITY_DECODE));
      
            INSERT INTO XXHCC_STAGING_XML
                 VALUES (Xml_Result, SYSDATE, ' Street_Events_Get SOAP Response');
      
            COMMIT;
      It doesnt work sometime and I have a feeling its reaching its 32K limit and not responding. If i give the same parameters the webservice when run individually runs fine, but when i call from SQL plus or any other oracle procedure i get the following error
      ORA-00600: internal error code, arguments: [733], [268431488], [pga heap], [], [], [], [], []
      Am i right in assuming that from pl/sql it can only read 32K of data and if I long XML document with records being returned it would fail.

      Its happening for only certain records and most of the time it works fine, I bring the data with a date range, and when i bring between 2 years it fails as it has got many records around 40-50 records in a xml document.

      What is the best alternative to overcome this.

      Thanks

      Sri
        • 1. Re: 32k limit in Reading XML
          odie_63
          Hi,

          You have to declare a "Transfer-Encoding: chunked" header and read the response with a loop.

          See this thread : {thread:id=2375680}
          • 2. Re: 32k limit in Reading XML
            344384
            Thanks very much I shall look into it and let u know if it works.

            Thanks once again.

            Sri
            • 3. Re: 32k limit in Reading XML
              344384
              Hi

              I have one more question,

              Is there anyway of knowing if the document or SOAP response which i get is more than 32k length,

              When i invoke individuall from a http endpoing i get the soap response displayed in the browser, I want to know from there if its more than 32k length

              Is it possible?

              Thanks

              S
              • 4. Re: 32k limit in Reading XML
                344384
                I tried the dbms_lob approach and it seems to have worked for the record which was giving error, but when i try with other record i get this error
                ORA-31011: XML parsing failed
                ORA-19202: Error occurred in XML processing
                LPX-00231: invalid character 47 ('/') found in a Name or Nmtoken
                Any idea what the above error is, but when i call the individual web service it has 130 records and it displays well in the browser.

                So not sure what the problem is

                S
                • 5. Re: 32k limit in Reading XML
                  odie_63
                  Display the content of the CLOB before converting it to XMLType, and check if it's not truncated.
                  • 6. Re: 32k limit in Reading XML
                    344384
                    while not(eob)
                        loop
                           begin
                             UTL_HTTP.READ_TEXT (HTTP_RESP, buffer, 32767);
                    
                                  if buffer is not null and length(buffer)>0 then
                                      dbms_lob.writeappend(SOAP_RESPOND, length(buffer), buffer);
                                  end if;
                             exception when UTL_HTTP.END_OF_BODY then
                                  eob := true;
                                  end;
                        end loop;
                    
                     UTL_HTTP.END_RESPONSE (HTTP_RESP);
                     SOAP_RESPOND := XMLTYPE(SOAP_RESPOND).EXTRACT('/soap:Envelope/soap:Body/Streets_Events_GetResponse/Streets_Events_GetResult/text()',
                                                        'xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/",
                                                        xmlns="http://webapi/"').GETCLOBVAL();
                              
                    Xml_Result := XMLTYPE(DBMS_XMLGEN.CONVERT(SOAP_RESPOND, DBMS_XMLGEN.ENTITY_DECODE));
                             
                    INSERT INTO XXHCC_STAGING_XML VALUES ( Xml_Result, sysdate,'Street_Events_GetLOB SOAP Response' );
                    Commit;
                    StResult :=Xml_Result;
                              
                    dbms_lob.freetemporary(soap_respond);
                    My procedure has one out parameter which is XMLTYPE, and if i assign the soap_respond to stresult it says invalid expression.

                    Can you please tell me how do i check if soap_respond is truncated ? Its returning 130 records when i call it in the browser.

                    S
                    • 7. Re: 32k limit in Reading XML
                      344384
                      Hi Odie

                      I tried to do like this commented the extract part from the soap respond
                       UTL_HTTP.END_RESPONSE (HTTP_RESP);
                      
                            -- SOAP_RESPOND := XMLTYPE(SOAP_RESPOND).EXTRACT('/soap:Envelope/soap:Body/Streets_Events_GetResponse/Streets_Events_GetResult/text()',
                            --                             'xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/",
                            --                             xmlns="http://webapi/"').GETCLOBVAL();
                      
                            --       Xml_Result := XMLTYPE(DBMS_XMLGEN.CONVERT(SOAP_RESPOND, DBMS_XMLGEN.ENTITY_DECODE));
                      
                            INSERT INTO XXHCC_STAGING_XML
                                 VALUES (
                                    xmltype (soap_respond),
                                    SYSDATE,
                                    'Street_Events_GetLOB SOAP Response');
                      
                            COMMIT;
                      
                            --StResult :=Xml_Result;
                            StResult := xmltype (soap_respond);
                      I get the response in sql plus (displays the contents fully from the variable) but when i go to toad to look at the soap respond contents i get
                      and if i scroll through the records i keep getting the below message in toad.
                      OCI - 31167 XML Nodes over 64K in Size Cannot be Inserted
                      I can see the output in sqlplus with & and < and > symbols as its not encoded.

                      What is the best solution to overcome this?

                      S
                      • 8. Re: 32k limit in Reading XML
                        Jason_(A_Non)
                        What is your version again?
                        select * from v$version;

                        Are you reading/understanding Odie's answers? He's really trying to help you but you need to make sure and answer his questions for him to help answer your questions.
                        Display the content of the CLOB before converting it to XMLType, and check if it's not truncated.
                        dbms_output.put_line will show the clob. That's all he was asking, in order to verify the contents were the expected XML and not truncated.

                        The DBMS_XMLGEN.CONVERT handled converting the encoded characters to &, <, etc. You need to make sure you read the documentation on a function to understand what it does. That would have answered your question on why items were still encoded.
                        if i assign the soap_respond to stresult it says invalid expression.
                        Show the error message and how you got it.
                        • 9. Re: 32k limit in Reading XML
                          344384
                          My version is

                          Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
                          PL/SQL Release 9.2.0.6.0 - Production

                          The reason is i m hittting the 64K limit with the XML

                          In my code
                          -- SOAP_RESPOND := XMLTYPE(SOAP_RESPOND).EXTRACT('/soap:Envelope/soap:Body/Streets_Events_GetResponse/Streets_Events_GetResult/text()',
                          --                                    'xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/",
                          --                                    xmlns="http://webapi/"').GETCLOBVAL();
                                    
                          -- Xml_Result := XMLTYPE(DBMS_XMLGEN.CONVERT(SOAP_RESPOND, DBMS_XMLGEN.ENTITY_DECODE));
                                    
                                  INSERT INTO XXHCC_STAGING_XML VALUES (xmltype(soap_respond), sysdate,'Street_Events_GetLOB SOAP Response' );
                                  Commit;
                          
                                    --StResult :=Xml_Result;
                                    StResult := xmltype(soap_respond);
                          My procedure when run from sql plus works fine, i can see the whole xml output, but the moment i go to TOAD and do a refresh of my staging table i get this
                          OCI - 31167 XML Nodes over 64K in Size Cannot be Inserted
                          It works if i store the xml as clob and retrieve it as clob, but the only problem i face is then its not encoded properly.

                          I have seen in other posts where it says we cannot perform any extract or extractvalue functions on the xmltype.

                          What is the best way of overcoming this, My procedure displays the xml and another procedure which calls my procedure picks it and shreds into columns.

                          I m wondering if there is anyway of bypassing this 64k limit

                          S
                          • 10. Re: 32k limit in Reading XML
                            Jason_(A_Non)
                            Alright, things are starting to make sense now given what it appears you have tried and your version. Had you answered Odie's request, we might have gotten there faster but that is besides the point.

                            In your above code, if you were to uncomment the line
                            SOAP_RESPOND := XMLTYPE(SOAP_RESPOND).EXTRACT...
                            and then do a dbms_output.put_line(soap_respond); on it, you would see that the CLOB is truncated at 64k. That stems from {thread:id=352665} in that Oracle is silently truncating your results.

                            As the contents of Streets_Events_GetResult may sometimes be > 64K, you need a way to extract the "XML" that is encoded within that node. I skipped the 9.x line of Oracle so I'm not sure what exactly is available, but one way is to simply use CLOB manipulation to find where the Streets_Events_GetResult node begins and ends and substring out everything in-between them.

                            You will then need to do
                            Xml_Result := XMLTYPE(DBMS_XMLGEN.CONVERT(SOAP_RESPOND, DBMS_XMLGEN.ENTITY_DECODE));
                            still as you need to decode the encodings before converting the XML into an XMLType. This should work fine, unless one of the individual nodes within that XML contains more than 64K.
                            • 11. Re: 32k limit in Reading XML
                              odie_63
                              >
                              As the contents of Streets_Events_GetResult may sometimes be > 64K, you need a way to extract the "XML" that is encoded within that node. I skipped the 9.x line of Oracle so I'm not sure what exactly is available, but one way is to simply use CLOB manipulation to find where the Streets_Events_GetResult node begins and ends and substring out everything in-between them.
                              >

                              I agree.

                              If we want to stick to the "XML way", two other options I can think of, using Java :

                              - XSLT to extract the text() node and send back the stream to PL/SQL
                              - SAX parser

                              but both appear overkill compared to simplicity of the LOB manipulation approach.
                              • 12. Re: 32k limit in Reading XML
                                344384
                                Hi

                                So you mean to say look for the resultset i want and pick that much from the clob using dbms_lob.instr and substr.

                                But it wouldnt reduce my xml much, i m just removing the top 2-3 nodes and picking the entire document.

                                If i want the same functionality which extract does in the above stmt, how do i go above it, I know i have to remove whatever is in between "Streets_Events_GetResult" tags from the clob and send it.

                                S
                                • 13. Re: 32k limit in Reading XML
                                  odie_63
                                  >
                                  But it wouldnt reduce my xml much, i m just removing the top 2-3 nodes and picking the entire document.
                                  >
                                  Jason explained it already...

                                  It's not about reducing the size of the whole XML. The problem is the size of individual node values.
                                  In your case, the embedded (escaped) XML payload is a text() node whose size is greated than 64k, hence the issue.

                                  So if you can extract it as CLOB, and assuming the resulting XML doesn't contain nodes > 64k in its turn, it'll be OK.

                                  Edited by: odie_63 on 8 juin 2012 12:09