How to Use Correctly DBMS_CRYPTO.HASH With Clob and Raw Input?

(Doc ID 760247.1)

Last updated on NOVEMBER 04, 2015

Applies to:

Oracle Database - Enterprise Edition - Version and later
Information in this document applies to any platform.
Information in this document applies to any platform.

Checked for relevance on 04-Jun-2013


When using the dbms_crypto.hash procedure, it is easy to overlook how this actually works and obtain the undesired results. Below is one example of such possible scenarios.

The scenario assumes that the database character set is not AL32UTF8 or UTF8 and that the character set is one Western European character set (assume WE8MSWIN1252), but the same can happen for any other characterset.

Trying to encrypt the same character (Ö), it appears that the procedure returns different results for the same character, when the character is over codepoint 127.

It is also good to mention that this procedure is deterministic, so it should return for the same input the same output.

For character 214:
SQL> begin
2 dbms_output.put_line(rawtohex(dbms_crypto.hash(src => hextoraw('d6'), typ => dbms_crypto.HASH_MD5)));
3 dbms_output.put_line(rawtohex(dbms_crypto.hash(src => to_clob(chr(214)), typ => dbms_crypto.HASH_MD5)));
4 end;
5 /

while for 120;
SQL> begin
2 dbms_output.put_line(rawtohex(dbms_crypto.hash(src => hextoraw('78'), typ =>dbms_crypto.HASH_MD5)));
3 dbms_output.put_line(rawtohex(dbms_crypto.hash(src => to_clob(chr(120)) , typ =>dbms_crypto.HASH_MD5)));
4 end;
5 /


Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms