Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Existing PL/SQL function used to call web service returns unreadable characters after upgrade to Oracle 19c

user11408655May 2 2023 — edited May 3 2023

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?

Comments
Post Details
Added on May 2 2023
23 comments
1,414 views