At JMJ we use Oracle's Exadata Express cloud service to integrate and pull together all the data from the various applications we use - Atlassian Service Desk, Quickbooks etc. Exadata Express comes pre-built with APEX and ORDS and out of the box provides great functionality for building applications, calling third party SOAP and REST services and hosting REST web services. The APEX_WEB_SERVICE package provides a simple API for accessing external services from sites such as ERP Cloud and Quickbooks Online.
However, there is always a catch - and with Exadata Express it comes when trying to call a web service that uses OAUTH 1.0 and requires the HMAC-SHA1 signature algorithm. If that sounds like an alien language to you - you're not alone. A brief read through the HMAC-SHA1 standard located at https://tools.ietf.org/html/rfc2104 shows us that this isn't straightforward.
Put simply, sites requiring OAUTH 1.0 such as Quickbooks need your web service call (URL, method and parameters) to be encoded using a key to generate a signature. This signature is then sent over HTTPS along with your web service call so that the service provider knows that the message was sent by you.
Many of the sites using OAUTH recommend using a standard library to calculate the signature, and this is sound advice. The Enterprise Editions of the Oracle Database come with the DBMS_CRYPTO built-in package. This contains the following handy MAC function:
All good - except this package is not available to us in Exadata Express! That makes our task a little harder.
So where did we look next? Following the recommendation to re-use a pre-built library, there are several Java HMAC-SHA1 examples on Github. Here's an example of how Java can calculate an HMAC-SHA1 signature:
This gave us hope - perhaps we could create a Java Stored Procedure and call this from PL/SQL in Exadata Express to generate our signature? Only - hopes dashed again - it turns out Exadata Express doesn't allow you to create Java Stored Procedures. The only remaining option was to look at building our own procedure to do this task.
First we needed to be able to generate an SHA1 hash. Here's where we were fortunate as there are open-source packages available to use. One is found here: https://github.com/vadimonus/plsql-hash. The SHA1 function was placed into a package in Exadata Express:
Next we reviewed the HMAC-SHA1 standards document, and checked this against pseudocode and test cases on Wikipedia. Here's the resulting HMAC_SHA1 function we built to generate the signatures:
But was it right? We tested this against the Wikipedia sample use cases here: https://en.wikipedia.org/wiki/Hash-based_message_authentication_code.
All good - everything matched. We were able to apply this signature to our web service call and successfully connect to Quickbooks. Problem solved, even without DBMS_CRYPTO, and importantly without the need for an Enterprise Edition of the database!
We hope you find this helpful if like us you're a big fan of Exadata Express and the endless possibilities it provides for integration. We're always interested in integration work so please reach out if you need a hand with getting your Apex integration up and running!