[FIXED] How to specify basic authentication in PL/SQL SOAP WSDL web service call

Issue

I am trying to call an Oracle Fusion Cloud web service from an Oracle Applications EBS server using PL/SQL. I can perform the web service call successfully from SOAPUI, however in SOAPUI the authentication (basic authentication) is specified in a separate window. My intention is to use the working SOAP envelope from SOAPUI, but how can I specify the web service basic authentication in PL/SQL (Oracle 11g)?

From google it looks like the basic authentication can be included in the SOAP envelope's header. However all the examples refer to http://docs.oasis-open.org which is a website that I don't know whether I can trust especially when it uses http and requires PasswordText as part of their url. See this example:

<soapenv:Header xmlns:wsa="http://www.w3.org/2005/08/addressing">
      <wsse:Security xmlns:wsse="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd"><wsse:UsernameToken wsu:Id="UsernameToken-9419978" xmlns:wsu="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd"><wsse:Username>admin</wsse:Username><wsse:Password Type="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-username-token-profile-1.0#PasswordText">admin</wsse:Password></wsse:UsernameToken></wsse:Security>
</soapenv:Header>

The dba also had to setup an ACL and a wallet (without a password).

The code I have at the moment is:

create or replace 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;

I'm not sure what to replace <m:DownloadRequest xmlns:m="http://www.website.net/messages/GetDetails"> with for my server. Also not sure how to specify the wallet.

My SOAPUI envelope is:

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:typ="http://xmlns.oracle.com/apps/financials/commonModules/shared/model/erpIntegrationService/types/">
   <soapenv:Header/>
   <soapenv:Body>
      <typ:submitESSJobRequest>
         <typ:jobPackageName>/oracle/apps/ess/financials/commonModules/shared/common/interfaceLoader</typ:jobPackageName>                             
         <typ:jobDefinitionName>InterfaceLoaderController</typ:jobDefinitionName>
         <!--Zero or more repetitions:-->
<typ:paramList>15</typ:paramList><!--GL Costing-->
<typ:paramList>17518</typ:paramList><!--UCM File Number-->
<typ:paramList>N</typ:paramList>
<typ:paramList>N</typ:paramList>
<typ:paramList>#NULL</typ:paramList>
      </typ:submitESSJobRequest>
   </soapenv:Body>
</soapenv:Envelope>

The SOAP WSDL url is:

https://your.domain.fin.region.oraclecloud.com:443/publicFinancialCommonErpIntegration/ErpIntegrationService?WSDL

Solution

Finally got it working with this code:

declare
l_envelope       varchar2(32767);
l_http_request   utl_http.req;
l_http_response  utl_http.resp;
begin 
   utl_http.set_wallet
   (
      'file:/oracle/db/11.2.0/admin/EBST/wallet'
      ,'walletpassword'
   );
   l_envelope := '<?xml version="1.0"?>
   <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:typ="http://xmlns.oracle.com/apps/financials/commonModules/shared/model/erpIntegrationService/types/">
   <soapenv:Header/>
   <soapenv:Body>
      <typ:submitESSJobRequest>
         <typ:jobPackageName>/oracle/apps/ess/financials/commonModules/shared/common/interfaceLoader</typ:jobPackageName>                             
         <typ:jobDefinitionName>InterfaceLoaderController</typ:jobDefinitionName>
         <!--Zero or more repetitions:-->
<typ:paramList>15</typ:paramList><!--GL Costing-->
<typ:paramList>17518</typ:paramList><!--UCM File Number-->
<typ:paramList>N</typ:paramList>
<typ:paramList>N</typ:paramList>
<typ:paramList>#NULL</typ:paramList>
      </typ:submitESSJobRequest>
   </soapenv:Body>
</soapenv:Envelope>';
   -- dbms_output.put_line(l_envelope);
   l_http_request := utl_http.begin_request
   (
      'https://username:password@yourdomain.com:443/publicFinancialCommonErpIntegration/ErpIntegrationService?WSDL', 
      'POST', 
      'HTTP/1.1'
   );
   utl_http.set_header(l_http_request, 'Content-Type', 'text/xml');
   utl_http.set_header(l_http_request, 'Content-Length', length(l_envelope));
   utl_http.set_header(l_http_request, 'SOAPAction', 'http://xmlns.oracle.com/apps/financials/commonModules/shared/model/erpIntegrationService/submitESSJobRequest');
   utl_http.write_text(l_http_request, l_envelope);
   l_http_response := utl_http.get_response(l_http_request);
   utl_http.read_text(l_http_response, l_envelope);
   utl_http.end_response(l_http_response); 
end;

Answered By - Superdooperhero

Answer Checked By - Robin (PHPFixing Admin)