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;
/
