2 Replies Latest reply: Oct 12, 2012 12:39 PM by Rooney RSS

    ORA-31011: XML parsing failed

    Rooney
      Hello,

      I wrote a function in PL/SQL that returns an XMLTYPE.
      This Function calls a webservice in EDQ (Enterprise Data Quality).

      DB version using is:
      Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
      PL/SQL Release 11.2.0.3.0 - Production
      "CORE     11.2.0.3.0     Production"
      TNS for Linux: Version 11.2.0.3.0 - Production
      NLSRTL Version 11.2.0.3.0 - Production
      I tested my function and was able to get back the XML using the following code:
      -// Check if i am able to connect to the site.
      EXEC UTL_HTTP.SET_WALLET('file:/u01/app/oracle/product/11.2.0/db_1/owm/wallets/oracle/', 'USSC#2012');
      SELECT UTL_HTTP.REQUEST('http://bl11.ussc.gov:7013/dndirector/webservices/Business%20Rules:BUSINESS_RULES?wsdl') FROM DUAL;
      XML returned below:
      "<?xml version="1.0" encoding="UTF-8"?><!-- Published by JAX-WS RI at http://jax-ws.dev.java.net. RI's version is JAX-WS RI 2.1.1-b03-. --><!-- wsdl file generated Oct 11, 2012 9:45 AM --><wsdl:definitions xmlns:soap="http://schemas.xmlsoap.org/wsdl/soap/" xmlns:tns="http://www.datanomic.com/ws" xmlns:wsdl="http://schemas.xmlsoap.org/wsdl/" xmlns:xs="http://www.w3.org/2001/XMLSchema" targetNamespace="http://www.datanomic.com/ws">
        <wsdl:types>
          <xs:schema elementFormDefault="qualified" targetNamespace="http://www.datanomic.com/ws">
            <xs:element name="request">
              <xs:complexType>
                <xs:sequence>
                  <xs:element maxOccurs="unbounded" minOccurs="0" name="record">
                    <xs:complexType>
                      <xs:sequence>
                        <xs:element minOccurs="0" name="SENT_ID" type="xs:decimal"></xs:element>
                      </xs:sequence>
                    </xs:complexType>
                  </xs:element>
                </xs:sequence>
                <xs:attribute name="id" type="xs:string"></xs:attribute>
              </xs:complexType>
            </xs:element>
            <xs:element name="response">
              <xs:complexType>
                <xs:sequence>
                  <xs:element maxOccurs="unbounded" minOccurs="0" name="record">
                    <xs:complexType>
                      <xs:sequence>
                        <xs:element minOccurs="0" name="FINAL_MESSAGE" type="xs:string"></xs:element>
                      </xs:sequence>
                    </xs:complexType>
                  </xs:element>
                </xs:sequence>
                <xs:attribute name="id" type="xs:string"></xs:attribute>
              </xs:complexType>
            </xs:element>
          </xs:schema>
        </wsdl:types>
        <wsdl:message name="Request">
          <wsdl:part element="tns:request" name="body"></wsdl:part>
        </wsdl:message>
        <wsdl:message name="Response">
          <wsdl:part element="tns:response" name="body"></wsdl:part>
        </wsdl:message>
        <wsdl:portType name="PortType">
          <wsdl:operation name="process">
            <wsdl:input message="tns:Request"></wsdl:inp"
      when running my Pl/SQL Function, I do get ORA-31011: XML parsing failed error
      Connecting to the database BL27_USSC_CASES.
      ORA-31011: XML parsing failed
      ORA-19202: Error occurred in XML processing
      LPX-00229: input source is empty
      ORA-06512: at "USSC_CASES.CALL_EDQ_WEBSERVICE", line 74
      ORA-06512: at line 7
      Process exited.
      Disconnecting from the database BL27_USSC_CASES.
      here is my function:
      The XML in my function is generated by SOAP, I was able to copy the WSDL URL from EDQ into SOAP and generate the XML.
      create or replace
      FUNCTION CALL_EDQ_WEBSERVICE (SENT_ID NUMBER) RETURN XMLTYPE IS
      
      --//URL CALL
      SOAP_URL CONSTANT VARCHAR2(1000) := 'http://bl11.ussc.gov:7013/dndirector/webservices/Business%20Rules:BUSINESS_RULES?wsdl';
      
      --// SOAP ENVELOPE TEMPLATE, CONTAINING $ SUBSTITUTION VARIABLES
      SOAP_ENVELOPE CONSTANT VARCHAR2(2000) := 
      '<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ws="http://www.datanomic.com/ws">
         <soapenv:Header/>
         <soapenv:Body>
            <ws:request id="?">
               <!--Zero or more repetitions:-->
               <ws:record>
                  <!--Optional:-->
                  <ws:SENT_ID>$SENT_ID</ws:SENT_ID>
               </ws:record>
            </ws:request>
         </soapenv:Body>
      </soapenv:Envelope>';
      
      C_WALLET constant varchar2(4000) := 'file:/u01/app/oracle/product/11.2.0/db_1/owm/wallets/oracle/';
      C_WALLET_PASS constant varchar2(4000) := 'USSC#2012';
      
      --//LOCAL VARIABLES
      SOAPENVELOPE VARCHAR2(2000);
      REQUEST UTL_HTTP.REQ;
      RESPONSE UTL_HTTP.RESP;
      BUFFER VARCHAR2(32767);
      SOAPRESPONSE CLOB;
      XMLRESPONSE XMLTYPE;
      EOF BOOLEAN;
      
      BEGIN
           --// CREATE THE SOAP ENVELOPE
           SOAPENVELOPE := REPLACE(SOAP_ENVELOPE, '$SENT_ID', SENT_ID);
        
              -- //set wallet for HTTPS access
              UTL_HTTP.SET_WALLET(C_WALLET, C_WALLET_PASS);
        
           --// MAKE THE POST CALL TO THE WEB SERVICE
           REQUEST := UTL_HTTP.BEGIN_REQUEST(SOAP_URL, 'POST', UTL_HTTP.HTTP_VERSION_1_1);
           UTL_HTTP.SET_HEADER (REQUEST, 'Content-Type', 'text/xml; charset=utf-8');
           UTL_HTTP.SET_HEADER (REQUEST, 'Content-Length', LENGTH(SOAPENVELOPE));
           UTL_HTTP.SET_HEADER (REQUEST, 'SoapAction', 'http://www.datanomic.com/ws/BUSINESS_RULES');
           UTL_HTTP.WRITE_TEXT (REQUEST, SOAPENVELOPE);
           
           --// READ THE WEB SERVICE HTTP RESPONSE
           RESPONSE := UTL_HTTP.GET_RESPONSE(REQUEST);
           DBMS_LOB.CREATETEMPORARY(SOAPRESPONSE, TRUE);
           EOF := FALSE;
           
           LOOP
                EXIT WHEN EOF;
                BEGIN
                     UTL_HTTP.READ_LINE(RESPONSE, BUFFER, TRUE);
                     IF LENGTH(BUFFER) > 0 THEN
                          DBMS_LOB.WRITEAPPEND(SOAPRESPONSE, LENGTH(BUFFER), BUFFER);
                     END IF;
                     EXCEPTION WHEN UTL_HTTP.END_OF_BODY THEN 
                     EOF := TRUE;
                END;
           END LOOP;
              UTL_HTTP.END_RESPONSE(RESPONSE);
           
           --// AS THE SOAP RESPONDS WITH XML, WE CONVERT THE RESPONSE TO XML
              XMLRESPONSE := XMLTYPE(SOAPRESPONSE);
              DBMS_LOB.FREETEMPORARY(SOAPRESPONSE); 
           RETURN(XMLRESPONSE);
           
      EXCEPTION WHEN OTHERS THEN
           IF SOAPRESPONSE IS NOT NULL THEN
              DBMS_LOB.FREETEMPORARY(SOAPRESPONSE);
           END IF;  --// This is line 74
           RAISE;
      
      END CALL_EDQ_WEBSERVICE;
      Thank you in advance.