Calling a SOAP Web Service Using PL/SQL with Web Services Security (including a nonce and password digest)

I was recently asked to write some PL/SQL to call a simple REST webservice. That's fairly straight forward using the standard UTL_HTTP database package. The service was then changed to a SOAP service which, because it is now hosted on AWS, requires authentication. In this case, it uses Web Services Security password digest authentication. It took quite a few trial-and-error attempts to get this working, so I thought I'd post it up to help anyone else trying to accomplish something similar in PL/SQL. Below is a simple example SQL*Plus script. This shouldn't be used as-is for production code because it waits for response from the service before continuing. In a production environment you should write your messages to a queue or database table, and dequeue it using a separate process that then makes the web service call so that your main process is not sitting around waiting for HTTP responses.

DECLARE
  --
  l_service_url         VARCHAR2(1000);
  l_http_request        utl_http.req;
  l_http_response       utl_http.resp;
  l_password            VARCHAR2(50);
  l_nonce_raw           RAW(100);
  l_nonce_b64           VARCHAR2(24);
  l_password_digest_b64 VARCHAR2(100);
  --
  l_request_body        VARCHAR2(32767);
  l_response_text       VARCHAR2(32767);
  l_timestamp_char      VARCHAR2(100);
  l_trx_timestamp_char  VARCHAR2(100);
  l_count               NUMBER;
  --
BEGIN
  --
  l_service_url         := 'http://sit.ifp.mycompany.com/cxf/centralLoggingIS';
  l_timestamp_char      := TO_CHAR(SYSTIMESTAMP,'YYYY-MM-DD"T"HH24:MI:SS.FF3"Z"');
  l_trx_timestamp_char  := TO_CHAR(SYSTIMESTAMP,'YYYY-MM-DD"T"HH24:MI:SSTZH:TZM');
  l_password            := 'password_that_me_and_service_know';
  l_nonce_raw           := utl_i18n.string_to_raw(dbms_random.string('a',16),'utf8');
  l_nonce_b64           := utl_i18n.raw_to_char(utl_encode.base64_encode(l_nonce_raw),'utf8');
  l_password_digest_b64 := utl_i18n.raw_to_char
                           ( utl_encode.base64_encode
                             ( dbms_crypto.hash
                               ( l_nonce_raw||utl_i18n.string_to_raw(l_timestamp_char||l_password,'utf8')
                               , dbms_crypto.hash_sh1
                               )
                             )
                           , 'utf8'
                           );
  l_request_body :=
'<soapenv:Envelope xmlns:ent="http://www.mycompany.com/enterprise/messages/centralLogging/v1/xsd/EntLogEBM.xsd" xmlns:ent1="http://www.mycompany.com/enterprise/objects/centralLogging/v1/xsd/EntLogEBO.xsd" xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
  <soapenv:Header>
    <wsse:Security soapenv:mustUnderstand="1" xmlns:wsse="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd" xmlns:wsu="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd">
      <wsse:UsernameToken>
        <wsse:Username>CLog_USER</wsse:Username>
        <wsse:Password Type="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-username-token-profile-1.0#PasswordDigest">'||l_password_digest_b64||'</wsse:Password>
        <wsse:Nonce EncodingType="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-soap-message-security-1.0#Base64Binary">'||l_nonce_b64||'</wsse:Nonce>
        <wsu:Created>'||l_timestamp_char||'</wsu:Created>
      </wsse:UsernameToken>
    </wsse:Security>
  </soapenv:Header>
  <soapenv:Body>
    <ent:insertLog>
      <ent:LogItem>
        <ent1:InterfaceProcessRef>Pete_PLSQL_Test</ent1:InterfaceProcessRef>
        <ent1:StageRef>Pete_Test_Stage_1</ent1:StageRef>
        <ent1:ProcessTimeStamp>'||l_trx_timestamp_char||'</ent1:ProcessTimeStamp>
        <ent1:TxnIdentifier1>Pete_Test_TxnIdentifier1</ent1:TxnIdentifier1>
        <ent1:TxnAttr1>Pete_test_TxnAttr1</ent1:TxnAttr1>
        <ent1:Message>This is a call from PLSQL</ent1:Message>
      </ent:LogItem>
    </ent:insertLog>
  </soapenv:Body>
</soapenv:Envelope>';
  --
  -- Preparing request...
  l_http_request := utl_http.begin_request(l_service_url, 'POST', 'HTTP/1.1');
  --
  -- Set HTTP header attributes
  utl_http.set_header(l_http_request, 'Content-Type', 'text/xml charset=UTF-8');
  utl_http.set_header(l_http_request, 'Content-Length', LENGTH(l_request_body));
  --
  -- Set request body
  utl_http.write_text(l_http_request, l_request_body);
  --
  -- Get response, read it and output it.
  l_http_response := utl_http.get_response(l_http_request);
  l_count := 0;
  LOOP
    l_count := l_count + 1;
    utl_http.read_line(l_http_response, l_response_text);
    dbms_output.put_line('Count is '||l_count);
    dbms_output.put_line(l_response_text);
    IF l_count > 100 THEN EXIT; END IF;
  END LOOP;
  --
  -- End our request.
  utl_http.end_response(l_http_response);
  --
EXCEPTION
  WHEN utl_http.end_of_body
    THEN
      dbms_output.put_line('end of response reached');
      dbms_output.put_line(l_response_text);
      utl_http.end_response(l_http_response);
END;

/