SHA-512 on Oracle

Question

I want to encrypt some data with SHA-512 on Oracle 12.

For example, I would like to encrypt this string:

230049008251111 

to this:

DA9AA3ACDE64DB3297FF75FDE407DAF3DB7EFF0CDF987C6C16BAF28242997046997EBF5A2F6C4F7449A4936C6518A6FD24A3C0984E9C09BF19395175F1BE2B5F

This is according to sha512 generator I'm using. What is the best way to do so on Oracle?

Thanks

Answer 1

Oracle has built-in database support for SHA-512.

If you want a solution for pure SQL and your input string is within the SQL string length limit (4000 characters unless you've enabled the 12c extended varchar2 semantics) you can do this:

select standard_hash ( '230049008251111', 'SHA512')
from dual
/

If you have longer strings Oracle provides a package DBMS_CRYPTO (not to be confused with the deprecated DBMS_CRYPTO_TOOLKIT). You could use it like this ...

declare
     str clob;
     hsh raw(64);
begin      
     str := '230049008251111';
     hsh := dbms_crypto.hash (str, dbms_crypto.HASH_SH512);
     dbms_output.put_line(hsh);
end;
/

... or whatever makes sense for your application. There are three variants of dbms_crypto.hash() which take BLOB, CLOB and RAW. There is no variant for plain old varchar2. Not sure why. Find out more.

Both these calls produce the same output (as might be hoped):

DA9AA3ACDE64DB3297FF75FDE407DAF3DB7EFF0CDF987C6C16BAF28242997046997EBF5A2F6C4F7449A4936C6518A6FD24A3C0984E9C09BF19395175F1BE2B5F

---

As @WernfriedDomscheit observes, execute on DBMS_CRYPTO is not granted by default. However, STANDARD_HASH() is. So, depending on your requirements, you may need to engage with your DBA team to get the necessary privileges.