Hello,
The following PL/SQL function has been used to call a SOAP web service for years without issue:
CREATE OR REPLACE FUNCTION CALL_SOAP_WEB_SERVICE (
p_ws_url IN VARCHAR2,
p_param IN VARCHAR2,
p_wallet IN VARCHAR2)
RETURN CLOB
IS
l_soap_req VARCHAR2(32767) := NULL;
l_soap_resp VARCHAR2(32767) := NULL;
l_request utl_http.req;
l_response utl_http.resp;
l_req_context utl_http.request_context_key;
l_xml_response CLOB;
BEGIN
-- Initialize web service connection
l_req_context := utl_http.create_request_context('file:'||p_wallet);
utl_http.set_wallet('file:'||p_wallet, NULL);
utl_http.set_detailed_excp_support(TRUE);
utl_http.set_transfer_timeout(500);
-- Build SOAP request to send to web service
l_soap_req :=
'<?xml version="1.0" encoding="utf-8"?>
<SOAP-ENV:Envelope
xmlns:SOAP-ENV = "http://www.w3.org/2001/12/soap-envelope"
SOAP-ENV:encodingStyle = "http://www.w3.org/2001/12/soap-encoding">
<SOAP-ENV:Body xmlns:m = "http://www.xyz.org/quotations">
<m:GetQuotation>
<m:QuotationsName>'||p_param||'</m:QuotationsName>
</m:GetQuotation>
</SOAP-ENV:Body>
</SOAP-ENV:Envelope>';
-- build request
l_request := utl_http.begin_request(p_ws_url, 'POST', 'HTTP/1.1', l_req_context);
utl_http.set_authentication_from_wallet(l_request, 'ws_user', 'Basic', FALSE);
utl_http.set_header(l_request, 'Content-Type', 'text/xml; charset=utf-8');
utl_http.set_header(l_request, 'Content-Length', LENGTH(l_soap_req));
utl_http.set_header(l_request, 'SOAPAction', '""');
utl_http.write_text(l_request, l_soap_req);
-- get response
l_response:= utl_http.get_response(l_request);
BEGIN
LOOP
utl_http.read_text(l_response, l_soap_resp);
l_xml_response := l_xml_response||l_soap_resp;
END LOOP;
EXCEPTION
WHEN utl_http.end_of_body THEN
NULL;
WHEN OTHERS THEN
dbms_output.put_line('error trying to parse web service response');
RAISE;
END;
utl_http.end_response(l_response);
END IF;
RETURN l_xml_response;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('ERROR in CALL_SOAP_WEB_SERVICE: '||SQLERRM);
RAISE;
END CALL_SOAP_WEB_SERVICE;
/
However after an upgrade from Oracle 12c to Oracle 19c the function now returns web service responses containing unreadable characters (inverted question marks, etc). I've tried a few suggestions online for resolving the issue but I haven't been able to come up with a solution. Does anyone have any ideas on what might be happening?