DBAs have hidden DBMS_CRYPTO package to any role, grantee, and I wonder why. I have to use wrapper instead :(

Is there any way to get the same hash for the text field with the same text? All below methods give different results for the same text as they seem depending on rowid, or something similar:

 , utl_raw.cast_to_raw(sys_op_map_nonnull(log_msg))                                                  AS "RAW"
 , rawtohex(utl_raw.cast_to_raw(sys_op_map_nonnull(log_msg)))                                        AS raw_hex
 , rawtohex(standard_hash(sys_op_map_nonnull(log_msg), 'MD5'))                                       AS md5_hex
 , ora_hash(log_msg)
 , dbms_obfuscation_toolkit.md5(input => utl_raw.cast_to_raw(sys_op_map_nonnull(log_msg)))           AS md5
 , rawtohex(dbms_obfuscation_toolkit.md5(input => utl_raw.cast_to_raw(sys_op_map_nonnull(log_msg)))) AS hex

log_msg is CLOB

  • 2
    Are you saying that each of the hash functions you are trying to use returns different values at different times for the same input value? You may want to provide a reproducible example of that.
    – mustaccio
    Commented Sep 16, 2020 at 17:37

2 Answers 2

  • DBMS_OBFUSCATION_TOOLKIT was deprecated and replaced by DBMS_CRYPTO.
  • ORA_HASH does not produce a unique checksum, per se, and does not support LOB types.
  • STANDARD_HASH uses DBMS_CRYPTO under the hood, but also does not support LOB types.

Having recently faced a similar problem myself, I was able to get execute privileges on DBMS_CRYPTO for the application schema by demonstrating the need. The DBAs didn't have any problem granting the access as long is it was required and documented. It's the only real option for what you're trying to do. My function to produce a SHA256 hash wound up looking like this:

function get_sha256sum (p_clob in clob) return raw
    return dbms_crypto.hash (src => utl_raw.cast_to_raw(p_clob), typ => 4);
end get_sha256sum;

Note: don't try to cast the raw checksum to anything else - it's only accurate as a checksum in the original format.


DBMS_OBFUSCATION_TOOLKIT is no longer available on any currently supported versions of Oracle Database.

My recommendation is for you to upgrade your DB so that you can use DBMS_CRYPTO.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.