10 Replies Latest reply on Nov 16, 2016 10:46 AM by BluShadow

    Exception ORA-31011: XML parsing failed

    PooRvi-Oracle

      I am calling a SOAP based webservice using PL/SQL block. The response returned is in the following format :

      <env:Envelope xmlns:env="http://www.w3.org/2003/05/soap-envelope">
       
      <env:Header/>
      <env:Body>
       
      <ns0:getDocumentDataResponse xmlns:ns0="http://xmlns.oracle.com/oxp/service/ScheduleReportService">
        
      <ns3:getDocumentData  ="http://xmlns.oracle.com/oxp/service/ScheduleReportService">JVBERi0xLjYNCjUgMCB vYmoNCjw8DQovVHlwZSAvWE9iamVjdA0KL1N1YnR5cGUgL0ltYWdlDQov
      RmlsdGVyIC9GbGF0ZURlY29kZQ0KL0xlbmd0aCA1MzUNCi9XaWR0aCA5Mg0KL0hlaWdodCAxNQ0K
      L0JpdHNQZXJDb21wb25lbnQgOA0KL0NvbG9yU3BhY2UgL0RldmljZVJHQg0KPj4NCnN0cmVhbQ0K
      eJztVzuOAjEM5U65yJwip6DgGnOhrai5AhLNLh0SRfZ5nDiexPFktS1PFgqMYz
      //kiGlD47xeDzS
      9WoKPdJ4/4w0s/g40nm9Xo5xeUqLGbhsszWT5LKkU0inky0hpBjrXixGmqI/ivp8yTqmAvivK20f
      WT5fqECOBYXn80nuHGvwxe4aU
      /hRx2JKE6nkxNfvOetkhkCcFahkVJqxZTx9/9CuiZxQ6jTzXmLM
      ....
      OWExMDllPjxhMWE2NTE2ZDNmYTcwY2Y2NzhkY2EwMWIzZjlhMTA5ZT5dDQo
      +Pg0Kc3RhcnR4cmVm
      DQoxMDg0MTIwDQolJUVPRg0K
      </ns3:getDocumentData>
        
      </ns0:getDocumentDataResponse>
        
      </env:Body>
      </env:Envelope>

       

      This response is stored in the variable : l_xml_response_text(varchar2) and when I execute the following code: l_xml_response_xmltype := xmltype(l_xml_response_text);

      it Is throwing the error:

      Exception ORA-31011: XML parsing failed

      ORA-19202: Error occurred in XML processing

      LPX-00007: unexpected end-of-file encountered.

       

      On further analysis, I was able to see that DBMS_OUTPUT.PUT_LINE (l_xml_response_text) is printing only a part of the data and not the entire response. Is it possible that, since it is not able to fetch the whole data and therefore not able to extract the ending tags for the XML and thus giving this error?

      If yes, how can we handle this ?

       

      I am working on database version 11g

        • 1. Re: Exception ORA-31011: XML parsing failed
          Marcin Izdebski

          When I check xml syntax in notepad++ using XML check tools I got parsing error too:

          Are you sure your xml message is correct?

          2016-11-16 10_28_40-_new 1 - Notepad++.jpg

          • 2. Re: Exception ORA-31011: XML parsing failed
            odie_63

            This part is not well-formed :

            <ns3:getDocumentData  ="http://xmlns.oracle.com/oxp/service/ScheduleReportService">

             

            It's missing some namespace prefix, presumably "xmlns:ns3" :

            <ns3:getDocumentData  xmlns:ns3="http://xmlns.oracle.com/oxp/service/ScheduleReportService">

            • 3. Re: Exception ORA-31011: XML parsing failed
              PooRvi-Oracle

              The XML I pasted here is just an extract and the the namsespace was left by mistake.

              Although the original response xml is valid and well formed.

              • 4. Re: Exception ORA-31011: XML parsing failed
                Paulzip

                Change l_xml_response_text to be a clob.  Your XML may be bigger than your varchar2 size.

                • 5. Re: Exception ORA-31011: XML parsing failed
                  odie_63

                  PooRvi-Oracle wrote:

                   

                  Although the original response xml is valid and well formed.

                  How did you check the XML was well-formed?

                   

                  How are you calling the web service and reading the response?

                  • 6. Re: Exception ORA-31011: XML parsing failed
                    PooRvi-Oracle

                    Tried changing the datatype to clob but still facing the issue.

                    • 7. Re: Exception ORA-31011: XML parsing failed
                      odie_63

                      PooRvi-Oracle wrote:

                       

                      Tried changing the datatype to clob but still facing the issue.

                      Post your code please.

                       

                      As Paul said, if the response is larger than 32k, you have to define the target variable as CLOB.

                      But it's not sufficient, you also have to adapt your code to read the response in chunk of 32k (assuming you're using UTL_HTTP read API) and build the CLOB piecewise.

                      • 8. Re: Exception ORA-31011: XML parsing failed
                        BluShadow

                        Your XML is missing the xmlns:ns3 namespace declaration and the ns3:getDocumentData tag is missing the attribute name before the "="

                         

                         

                        SQL> select xmltype('
                          2  <env:Envelope xmlns:env="http://www.w3.org/2003/05/soap-envelope" xmlns:ns3="something">
                          3    <env:Header/>
                          4    <env:Body>
                          5      <ns0:getDocumentDataResponse xmlns:ns0="http://xmlns.oracle.com/oxp/service/ScheduleReportService">
                          6        <ns3:getDocumentData x="http://xmlns.oracle.com/oxp/service/ScheduleReportService">JVBERi0xLjYNCjUgMCB vYmoNCjw8DQovVHlwZSAvWE9iamVjdA0KL1N1YnR5cGUgL0ltYWdlDQov
                          7  RmlsdGVyIC9GbGF0ZURlY29kZQ0KL0xlbmd0aCA1MzUNCi9XaWR0aCA5Mg0KL0hlaWdodCAxNQ0K
                          8  L0JpdHNQZXJDb21wb25lbnQgOA0KL0NvbG9yU3BhY2UgL0RldmljZVJHQg0KPj4NCnN0cmVhbQ0K
                          9  eJztVzuOAjEM5U65yJwip6DgGnOhrai5AhLNLh0SRfZ5nDiexPFktS1PFgqMYz//kiGlD47xeDzS
                        10  9WoKPdJ4/4w0s/g40nm9Xo5xeUqLGbhsszWT5LKkU0inky0hpBjrXixGmqI/ivp8yTqmAvivK20f
                        11  WT5fqECOBYXn80nuHGvwxe4aU/hRx2JKE6nkxNfvOetkhkCcFahkVJqxZTx9/9CuiZxQ6jTzXmLM
                        12  OWExMDllPjxhMWE2NTE2ZDNmYTcwY2Y2NzhkY2EwMWIzZjlhMTA5ZT5dDQo+Pg0Kc3RhcnR4cmVm
                        13  DQoxMDg0MTIwDQolJUVPRg0K</ns3:getDocumentData>
                        14      </ns0:getDocumentDataResponse>
                        15    </env:Body>
                        16  </env:Envelope>') as xml
                        17  from dual
                        18  /

                        XML
                        -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                        --------------------------------------------------

                        <env:Envelope xmlns:env="http://www.w3.org/2003/05/soap-envelope" xmlns:ns3="something">
                          <env:Header/>
                          <env:Body>
                            <ns0:getDocumentDataResponse xmlns:ns0="http://xmlns.oracle.com/oxp/service/ScheduleReportService">
                              <ns3:getDocumentData x="http://xmlns.oracle.com/oxp/service/ScheduleReportService">JVBERi0xLjYNCjUgMCB vYmoNCjw8DQovVHlwZSAvWE9iamVjdA0KL1N1YnR5cGUgL0ltYWdlDQov
                        RmlsdGVyIC9GbGF0ZURlY29kZQ0KL0xlbmd0aCA1MzUNCi9XaWR0aCA5Mg0KL0hlaWdodCAxNQ0K
                        L0JpdHNQZXJDb21wb25lbnQgOA0KL0NvbG9yU3BhY2UgL0RldmljZVJHQg0KPj4NCnN0cmVhbQ0K
                        eJztVzuOAjEM5U65yJwip6DgGnOhrai5AhLNLh0SRfZ5nDiexPFktS1PFgqMYz//kiGlD47xeDzS
                        9WoKPdJ4/4w0s/g40nm9Xo5xeUqLGbhsszWT5LKkU0inky0hpBjrXixGmqI/ivp8yTqmAvivK20f
                        WT5fqECOBYXn80nuHGvwxe4aU/hRx2JKE6nkxNfvOetkhkCcFahkVJqxZTx9/9CuiZxQ6jTzXmLM
                        OWExMDllPjxhMWE2NTE2ZDNmYTcwY2Y2NzhkY2EwMWIzZjlhMTA5ZT5dDQo+Pg0Kc3RhcnR4cmVm
                        DQoxMDg0MTIwDQolJUVPRg0K</ns3:getDocumentData>
                            </ns0:getDocumentDataResponse>
                          </env:Body>
                        </env:Envelope>

                        • 9. Re: Exception ORA-31011: XML parsing failed
                          PooRvi-Oracle

                          Please find the code below (modified the code to remove some sensitive info):

                          SET serveroutput ON;

                           

                          DECLARE

                            l_result xmltype;

                            l_resultvarchar VARCHAR2(32767);

                            l_request utl_http.req;

                            l_response utl_http.resp;

                            l_url               VARCHAR2(2000);

                            l_action            VARCHAR2(200);

                            l_soap_envlope      VARCHAR2(32767);

                            l_xml_response_text varchar2(32767);

                            l_xml_response_xmltype XMLTYPE ;

                            l_http_req utl_http.req;

                            l_http_resp utl_http.resp;

                            my_scheme VARCHAR2(256);

                            my_realm  VARCHAR2(256);

                            my_proxy  BOOLEAN;

                            -- l_wallet FND_PROFILE_OPTION_VALUES.PROFILE_OPTION_VALUE%Type;

                            l_wallet        VARCHAR2(500);

                            l_created_date  VARCHAR2(30) := TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP),'RRRR-MM-DD')||'T'||TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP),'HH24:MI:SS')||'Z';

                            l_expiry_date   VARCHAR2(30) := TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP)+1/(24*60),'RRRR-MM-DD')||'T'||TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP)+1/(24*60),'HH24:MI:SS')||'Z';

                            l_timestamp     TIMESTAMP(3) WITH TIME ZONE;

                            v_uuid          VARCHAR2(40);

                            l_bip_output_id VARCHAR2(100);

                          BEGIN

                            dbms_output.put_line('Entered');

                            SELECT SYSTIMESTAMP at TIME zone 'UTC' INTO l_timestamp FROM dual;

                            dbms_output.put_line(TO_CHAR(l_timestamp,'YYYY-MM-DD"T"hh24:mi:ss.ff3"Z"'));

                            SELECT regexp_replace(rawtohex(sys_guid()), '([A-F0-9]{8})([A-F0-9]{4})([A-F0-9]{4})([A-F0-9]{4})([A-F0-9]{12})', '\1-\2-\3-\4-\5')

                            INTO v_uuid

                            FROM dual;

                            l_soap_envlope :=

                            '<soap:Envelope xmlns:sch="http://xmlns.oracle.com/oxp/service/ScheduleReportService" xmlns:soap="http://www.w3.org/2003/05/soap-envelope">  

                          <soap:Header>   

                          </soap:Header>  

                          <soap:Body>     

                          <sch:getDocumentData>        

                          <jobOutputID>1639537</jobOutputID>     

                          </sch:getDocumentData>  

                          </soap:Body>

                          </soap:Envelope>'

                            ;

                           

                            l_url    := '<URL given here>';

                            l_action := 'getDocumentData';

                           

                            l_wallet := '<WALLET path here>';

                            -- call DAS wevservice using utl_http database API

                            UTL_HTTP.SET_PROXY ('<proxy path here>');

                            utl_http.set_wallet('file:'||l_wallet,NULL) ;

                            l_http_req := utl_http.begin_request(l_url, 'POST','HTTP/1.1') ;

                            utl_http.set_header(l_http_req, 'Content-Type', 'application/soap+xml') ;

                            utl_http.set_header(l_http_req, 'Content-Length', LENGTH(l_soap_envlope)) ;

                            utl_http.set_header(l_http_req, 'SOAPAction', l_action) ;

                            utl_http.write_text(l_http_req, l_soap_envlope) ;

                            l_http_resp := utl_http.get_response(l_http_req) ;

                            dbms_output.put_line('Got Response ' || l_http_resp.status_code );

                            IF (l_http_resp.status_code >= 400) AND (l_http_resp.status_code <= 499) THEN -- 1st IF starts here

                              dbms_output.put_line('Error status code.');

                              IF (l_http_resp.status_code = utl_http.HTTP_UNAUTHORIZED) THEN

                                dbms_output.put_line('unauthorized');

                                utl_http.end_response(l_http_resp);

                                RETURN ;

                              ELSE

                                dbms_output.put_line('Please check the URL');

                                utl_http.end_response(l_http_resp);

                                RETURN ;

                              END IF;

                              -- Look for server-side error and report it.

                            ELSIF (l_http_resp.status_code >= 500) AND (l_http_resp.status_code <= 599) THEN

                              dbms_output.put_line('Check if the Web site is up.'||l_http_resp.status_code);

                              utl_http.read_text(l_http_resp, l_xml_response_text);

                             -- dbms_output.put_line(l_xml_response_text);

                              utl_http.end_response(l_http_resp);

                              RETURN ;

                            END IF; -- 1st IF ends  here

                            dbms_output.put_line('getting response');

                            utl_http.read_text(l_http_resp, l_xml_response_text);

                            utl_http.end_response(l_http_resp);

                          -- l_xml_response_xmltype :=XMLTYPE( l_xml_response_text);

                             dbms_output.put_line(l_xml_response_text);

                          EXCEPTION

                          WHEN OTHERS THEN

                            dbms_output.put_line('Exception '||Utl_Http.Get_Detailed_Sqlerrm );

                            utl_http.end_response(l_http_resp);

                          END;

                           

                          I get the XML parsing error when I uncomment ' l_xml_response_xmltype :=XMLTYPE( l_xml_response_text);'.

                          and running the dbms_output.put_line(l_xml_response_text); gives me only a part of the output. (the original response which i checked from SOAP UI is about 1MB)

                          • 10. Re: Exception ORA-31011: XML parsing failed
                            BluShadow

                            As already mentioned you're not reading the full response.  If it's over 32K you need to read it in chunks and build up the clob, before you convert it to XMLTYPE e.g.

                             

                              PROCEDURE p_soap_request(p_username IN VARCHAR2, p_password IN VARCHAR2, p_proxy IN VARCHAR2) IS

                                soap_request  VARCHAR2(30000);

                                soap_respond  CLOB;

                                http_req      utl_http.req;

                                http_resp    utl_http.resp;

                                resp          XMLType;

                                soap_err      exception;

                                v_code        VARCHAR2(200);

                                v_msg        VARCHAR2(1800);

                                v_len number;

                                v_txt Varchar2(32767);

                              BEGIN

                                UTL_HTTP.SET_PROXY(p_proxy);

                                -- Define the SOAP request according the the definition of the web service being called

                                soap_request:= '<?xml version = "1.0" encoding = "UTF-8"?>'||

                                              '<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">'||

                                              '  <SOAP-ENV:Body>'||

                                              '    <m:DownloadRequest xmlns:m="http://www.website.net/messages/GetDetails">'||

                                              '      <m:UserName>'||p_username||'</m:UserName>'||

                                              '      <m:Password>'||p_password||'</m:Password>'||

                                              '    </m:DownloadRequest>'||

                                              '  </SOAP-ENV:Body>'||

                                              '</SOAP-ENV:Envelope>';

                                http_req:= utl_http.begin_request

                                          ( 'http://www.website.net/webservices/GetDetailsService.asmx'

                                          , 'POST'

                                          , 'HTTP/1.1'

                                          );

                                utl_http.set_header(http_req, 'Content-Type', 'text/xml');

                                utl_http.set_header(http_req, 'Content-Length', length(soap_request));

                                utl_http.set_header(http_req, 'Download', ''); -- header requirements of particular web service

                                utl_http.write_text(http_req, soap_request);

                                http_resp:= utl_http.get_response(http_req);

                                utl_http.get_header_by_name(http_resp, 'Content-Length', v_len, 1); -- Obtain the length of the response

                                FOR i in 1..CEIL(v_len/32767) -- obtain response in 32K blocks just in case it is greater than 32K

                                LOOP

                                    utl_http.read_text(http_resp, v_txt, case when i < CEIL(v_len/32767) then 32767 else mod(v_len,32767) end);

                                    soap_respond := soap_respond || v_txt; -- build up CLOB

                                END LOOP;

                                utl_http.end_response(http_resp);

                                resp:= XMLType.createXML(soap_respond); -- Convert CLOB to XMLTYPE

                              END;