Friday, July 24, 2009


I have been working on an Application Express 4.0 feature to support consuming REST Web services. REST Web services use a simpler architecture than the popular SOAP style Web services. Instead of posting some big XML document wrapped in a SOAP Envelope, REST requests are typically done by passing name/value pairs in the query string of a URL.

To prove that the support for REST I am building in Application Express is useful, I have been working with popular public REST Web services offered by Yahoo, Google and Amazon. I built a similar application to the Amazon Store sample application using the REST APIs. Everything was going great until I received the following email from Amazon.

"Dear Product Advertising API Developer,

We wanted to remind you that all Product Advertising API developers will be required to authenticate all calls to the Product Advertising API by August 15, 2009. We noticed that requests with your AWS Access Key ID are not being signed and, while you have more than 60 days until the date on which authentication is required, we are, as a courtesy, sending you this email to remind you of the new authentication requirement. Please remember that calls to the Product Advertising API that are not signed will not be processed after August 15, 2009."

What do they mean I need to sign my requests? Luckily they provided some links for developer resources on how to construct signed requests. In a nutshell, you must byte order all of your parameters, add a Timestamp parameter and then you create a base64-encoded HMAC_SHA256 signature using your AWS secret key. Should not be a problem. I recently added support for parameters in REST requests to be populated by the result of a function and I am somewhat familiar with the DBMS_CRYPTO package. Unfortunately I quickly found out that DBMS_CYRPTO only supports HMAC_SHA1 and I not so quickly found out that HMAC_SHA1 is not the same as HMAC_SHA256. Am I stuck?

I binged (Google gets enough love and Microsoft can use the help after their most recent earnings results) for HMAC_SHA256 and PL/SQL and found this forum post. This guy wanted to do exactly what I need to do. Someone suggested that he create a Java stored procedure and then he responded with some Java code example with and hmacSHA256 function. Unfortunately no-one could give him an example of exactly how to create that Java stored procedure and then use it in PL/SQL. That's when I remembered that my good friend and colleague Joel Kallman wrote about creating a Java stored procedure to create a zip file of BLOB's.

Well, surely I can follow Joel's example as a cookbook and do the same thing with this hmacSHA256 function, right? The answer is yes, after spending more time than I probably should have (don't tell Joel if you see him), but hey, I had to prove that this REST stuff would actually be useful to customers. If they can't sign their requests, they won't be able to use Application Express to build cool applications that interact with Amazon Web Services. (At least that is my attempt at invoking what another colleague calls The Oracle Justification Server.)

First, start by compiling the following Java source.

Code listing 1, java source hmacSHA256

create or replace and compile java source named hmacSHA256 as

import java.util.*;

public class hmacSHA256 {
public static String encrypt(
String message,
String keyStr) {

//get the bytes of the keyStr
byte[] key = keyStr.getBytes();
// Start by getting an object to generate SHA-256 hashes with.
MessageDigest sha256 = null;
try {
sha256 = MessageDigest.getInstance("SHA-256");
} catch (NoSuchAlgorithmException e) {
throw new java.lang.AssertionError(".hmacSHA256(): SHA-256 algorithm not found!");
// Hash the key if necessary to make it fit in a block (see RFC 2104).
if (key.length > 64) {
key = sha256.digest();

// Pad the key bytes to a block (see RFC 2104).
byte block[] = new byte[64];
for (int i = 0; i < key.length; ++i) block[i] = key[i];
for (int i = key.length; i < block.length; ++i) block[i] = 0;

// Calculate the inner hash, defined in RFC 2104 as
// SHA-256(KEY ^ IPAD + MESSAGE)), where IPAD is 64 bytes of 0x36.
for (int i = 0; i < 64; ++i) block[i] ^= 0x36;
try {
} catch (UnsupportedEncodingException e) {
throw new java.lang.AssertionError(
"ITunesU.hmacSH256(): UTF-8 encoding not supported!");
byte[] hash = sha256.digest();

// Calculate the outer hash, defined in RFC 2104 as
// SHA-256(KEY ^ OPAD + INNER_HASH), where OPAD is 64 bytes of 0x5c.
for (int i = 0; i < 64; ++i) block[i] ^= (0x36 ^ 0x5c);
hash = sha256.digest();

// The outer hash is the message signature...
// convert its bytes to hexadecimals.
char[] hexadecimals = new char[hash.length * 2];
for (int i = 0; i < hash.length; ++i) {
for (int j = 0; j < 2; ++j) {
int value = (hash[i] >> (4 - 4 * j)) & 0xf;
char base = (value < 10) ? ('0') : ('a' - 10);
hexadecimals[i * 2 + j] = (char)(base + value);

// Return a hexadecimal string representation of the message signature.
return new String(hexadecimals);

Next you create a standard PL/SQL function that uses the java source as in the following code listing.

Code listing 2, PL/SQL hmacSHA256 function

create or replace function hmacSHA256(
p_string in varchar2,
p_key in varchar2) return varchar2
as language java
name 'hmacSHA256.encrypt(
java.lang.String) return String';

Finally, you create a function that takes in the string to sign, your AWS Secret Key, and then creates the HMAC_SHA256 signature.

Code listing 3, amazon_signature function

create or replace function amazon_signature(
p_string in varchar2,
p_key in varchar2) return varchar2
encrypted_raw raw(2000);
output_string varchar2(32000);

encrypted_raw := hmacSHA256(p_string,p_key);
output_string := UTL_I18N.RAW_TO_CHAR (utl_encode.base64_encode(encrypted_raw), 'AL32UTF8');

return output_string;

end amazon_signature;
show errors

Now let's test all this out. Amazon provides a nice self contained Web page utility that uses Javascript to help you create signed requests. I will use 123456 as my AWS Access Key ID and abcdefg as my AWS Secret Access Key. Plug in the following URL in the Unsigned URL text area:,Images&Keywords=liberty+and+tryanny&SearchIndex=Books&AWSAccessKeyId=123456&AssociateTag=apex30-20

This will come up with the following string to sign:




And will produce the following Signature parameter:


If I take the same string to sign and AWS Secret Key of abcdefg and run it through amazon_signature, I get the following results:

Code listing 4, result of amazon_signature function

jason@APX11W> declare
2 l_token varchar2(4000);
3 begin
4 l_token := amazon_signature('GET
6 /onca/xml
7 AWSAccessKeyId=123456&AssociateTag=apex30-20&Keywords=liberty%20and%20tryanny&Operation=ItemSearch&ResponseGroup=It
8 dbms_output.put_line(l_token);
9 end;
10 /

PL/SQL procedure successfully completed.

The only difference in the result is the very end. Prior to using the signature, it must be URL encoded. If you run the string uMJX4cN6EXHyTUrC03Ae9hAcGdTnAHI0KqtovwQUHP8= through wwv_flow_utilities.url_encode2, you will get uMJX4cN6EXHyTUrC03Ae9hAcGdTnAHI0KqtovwQUHP8%3D.


Greg Jarmiolowski said...

This looks like a simpler version of the Java stored procedure


Jason Straub said...


I did see that same post and got excited about it until I realized it was only doing an SHA256 hash and would not do an HMAC_SHA256 signature with a key I can supply. That is the necessary part about signing a URL for Amazon Web Services. You must sign the URL with your secret key, not just hash it.



Anonymous said...

Great post!

Michelle said...

Hi Jason,
Thanks for your post! I was wondering if there was *any* way you would be willing to let me see your sample application using the Amazon REST APIs, or provide me with some additional counselling offline.
I am really struggling with this. I have been working with APEX since 2003, but have never used Web Services, and not too sure where to start. I am working on a kids' reading incentive program using APEX, and Amazon integration would be a huge plus. I would be happy to tell you more about it.
Many thanks!

Michelle said...

Hi Jason,
Do you know if, with the Signature changes on Amazon Web Services, I could simply modify the SOAP envelope in the Amazon Store Packaged app to include the:
soap: Header with the AWS Access Key, Timestamp and Signature? (following the SOAP without WS-Security procedure on the Product Advertising API). If so, should I be able to use the procedure described here to calculate the HMAC-SHA256 digest of the concatenation of the Action and Timestamp parameters, using my AWS Secret Access Key as the key? I have tried, but am receiving an PL/SQL: numeric or value error: hex to raw conversion error when trying to run it. Any help would be appreciated!

Jason Straub said...


Yes that should work and I was going to try exactly that when I had a chance and then blog about it. I am busy with Oracle Open World prep right now, but hope to get to it soon.



Michelle said...

Hi Jason,

Just wanted to let you know that we tried doing and it worked like a charm. Used the SOAP Without WS Security method, generated the signature using your procedure, and it went great! The only thing is that this required a wallet configuration in 'Manage Instance Settings', which we did in our DEV environment, but the people who host my production DB say that 'my approach is wrong' and that the wallet should not be configured within APEX... :-( So I am still stuck there, looking for alternatives... Think a new hosting company may be in my near future, unless you know how I can get around this without doing everything manually (and without access to INTERNAL workspace...). Thanks, hope Open World was a success!!!

Jason Straub said...


Oracle Open World was a success and I was able to spend some time with one of your colleagues.

Web Service support in Application Express uses UTL_HTTP to do the HTTP POST to the Web service. If the URL endpoint is HTTPS, then a wallet path and password needs to be passed to UTL_HTTP. Application Express needs to know what that path and password is. (The password is stored encrypted in a table in the Application Express schema.)

If your hosting provider refuses to create a wallet that is accessible by the database server where Application Express is running and will not provide the path and password in Application Express Instance Admin Environment settings, than perhaps you do need to find another hosting provider that will, unfortunately.



Michelle said...

I am making a pest of myself now... I am struggling to find a good hosting provider that will allow me to set the wallet path within APEX (without the prohibitive costs of a dedicated instance). Do you have any advice on how I can interact with Amazon API (I need to use ItemSearch and ItemLookup) without posting to https? You have done some work with REST requests, I am wondering if you might give me your thoughts on this, if you might let me look at what you have done, if I can hire your services... This is something I would really want to put in place before starting to market my (really cool!!) website for kids... May I contact you somehow? Thank you in advance for any help/insight you can provide...

Jason Straub said...


I have been meaning to update the flex_ws_api with support for RESTful Web services. How about I create a new post when I have added that support and as an example I will call the Amazon Product Search API with the ItemSearch operation as the example? I confirmed this can be done without HTTPS. I will try to have this done by the end of next week. Will that help?



Michelle said...

Yes, you are a star.... I can't wait, thank you so much.

Anonymous said...

Sorry to update an old blog, but I found this really useful in debugging my same requirement. In 12c it looks like the DBMS_CRYPTO is updated to include HMAC_SH256...

l_key VARCHAR(100) := 'abcdefg';
l_string VARCHAR2(2000) := 'GET
l_sig_mac RAW(2000);
l_base64_sig_mac VARCHAR2(2000);
l_sig_mac :=
DBMS_CRYPTO.mac(UTL_I18N.string_to_raw(l_string, 'AL32UTF8'), DBMS_CRYPTO.HMAC_SH256, UTL_I18N.string_to_raw(l_key, 'AL32UTF8'));
l_base64_sig_mac := UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(l_sig_mac));
DBMS_OUTPUT.put_line('MAC Signature (Base64-encoded): ' || l_base64_sig_mac);