1

I am trying to find the alternative to Ora_hash in Snowflake. My intent is to find an alternate in SNowflake which generates same HASH value for a string which ORA_HASH generates.

I tried using HASH, MD5, SHA1, SHA2 in Snowflake but none of them generates the same value as ORA_HASH.

Is there any other function which might match ORA_HASH or is there any other way?

Ankit Srivastava
  • 185
  • 3
  • 13
  • Do you want a hash function that works in the same basic way as the Oracle function or one that will return the same output for a given input as the Oracle function? If it is the 2nd one then it’s not possible as the Oracle function is proprietary – NickW Sep 27 '21 at 07:06
  • Before trying to get a match for the ORA_HASH algorithm, I recommend checking what happens when you use Oracle's STANDARD_HASH function (that supports MD5 and SHA) and select a hash in Snowflake to match. There's a chance that the hashes won't match even using identical hashing algorithms because the data is encoded differently, fed into the algorithm differently, etc. If they do match, it may be worth investigating how to get the ORA_HASH implemented in Snowflake. If they don't match, it's unlikely the ORA_HASH will either. – Greg Pavlik Sep 27 '21 at 12:35

1 Answers1

1

Oracle's ORA_HASH is a relatively light hashing algorithm. However it is proprietary, so the details of the implementation are not published, as far as I know. However, being relatively lightweight, it might be possible to reverse-engineer it, and write your own. See the following answer for some pointers if you want to do that:

What is the algorithm used by the ORA_HASH function?

Joe King
  • 2,955
  • 7
  • 29
  • 43