1

I am trying to find an alternative to the ORA_HASH Oracle function in Postgres(edb). I know there are hashtext() and MD5(). Hashtext should be ideal, but it's not documented and so I can't use it for some reasons. I'd like to know if there is any way of using MD5() and getting the same value that you'd get in ORA_HASH giving the same value for both of them.

For example, this is how I use it and what I get in Oracle:

SELECT ORA_HASH('huyu') FROM dual;
----------------------------------
3284595515

I'd like to do something similar in postgres, so that if I pass the 'huyu' value, I'd get the exact same '3284595515' value.

Now, I know that ORA_HASH restores a number and MD5 restores hexadecimal value. I think I'd need the function that converts the hexadecimal 32 into a number, but I can't get the same value and I'm not sure if it is possible.

Thank you in advance for any suggestions

Julia
  • 23
  • 2
  • I'd say ORA_HASH is very old Oracle's proprietary hashing function, which is also used internally by the database. If you search through internet, you will somewhere find what its C function name and its disassembly. ORA_HASH is quite simple hashing algorithm invented before photographically safe hashing functions were introduced. – ibre5041 Jul 07 '22 at 13:48
  • MD5 is a standard hashing algorithm that is likely to be available most anywhere you go. You should be able to get Oracle's `dbms_crypto` package's MD5 hash to match PostgreSQL's MD5 hash (or any other language/ framework/ database's MD5 hash). Could you have Oracle use a standard hash algorithm rather than `ora_hash`? – Justin Cave Jul 07 '22 at 13:58
  • I cant change the use of ora_hash in what I need to do, unfortunately. That's why I am asking this specific case, because I have to deal with this specific problem( – Julia Jul 07 '22 at 14:15
  • 2
    Seems that this problem hasn't been solved yet, due to proprietary software issues https://stackoverflow.com/questions/45948266/what-is-the-algorithm-used-by-the-ora-hash-function https://stackoverflow.com/questions/64578376/how-to-replace-ora-hash-function-of-oracle-in-postgres – Pepe N O Jul 07 '22 at 14:47

1 Answers1

1

If you rely on the exact implementation of ORA_HASH, which is a proprietary hash function in a closed-source software, you are locked to this vendor, sorry.

I don't see how using PostgreSQL's hashtext is worse than ORA_HASH: it is documented (in the source), it's implementation is public, and it is not going to be removed, because it is required for hash partitioning.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • The use of hashtext() is a simple "burocratic" problem in my case, so I wanted to be sure there is no alternative – Julia Jul 08 '22 at 15:19