Hashing in SQL Server and Oracle for the same output

Thanks go out to my colleagues for testing this out further, but we were testing out a way to get Oracle to generate hash values with the same output as the default SQL Server MD5 hashbytes function. In previous projects I used to rely on the DBMS_CRYPTO package in Oracle but this requires additional privileges. So if there is a some red tape to cut through the following code using the DBMS_OBFUSCATION_TOOLKIT works as well, and you can pass this in one go without requesting additional privileges or creation custom functions. Using a standard sanding of a pipe (‘|’) character you can call the DBMS_OBFUSCATION_TOOLKIT for MD5 in one statement like this:

UPPER(CAST(
 RAWTOHEX (SYS.DBMS_OBFUSCATION_TOOLKIT.MD5 (input_string =>
 NVL('<attribute A>','NA') || '|' ||
 NVL('<attribute B>', 'NA' || '|' ||
 NVL('<attribute C>', 'NA' || '|'
)) AS CHAR(32)))

This yields the same result as the SQL Server counterpart with the syntax:

UPPER(CONVERT(
 CHAR(32),HASHBYTES('MD5',
 ISNULL(RTRIM('<attribute A>'),'NA')+'|'+
 ISNULL(RTRIM('<attribute B>'),'NA')+'|'+
 ISNULL(RTRIM('<attribute C>'),'NA')+'|'
),2))

I found it to be useful as DBMS_CRYPTO sometimes requires more effort to set up. Of course the logic needs to handle other datatypes than just character fields such as date / time and numeric values. Especially date / time values need some attention, but it all works OK applying the same conversion mask using the above examples as starting point.

Apparently the DBMS_OBFUSCATION is meant to be replaced by DBMS_CRYPTO over time according to this Oracle documentation:

http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_crypto.htm#i1004143.

But for now we will probably stick with the obfuscation toolkit. We can always regenerate this later if we need to change.