DevOps, CI/CD and Automation

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.