Get hash values from SQL Server and Oracle and compare them?

Question

Is it possible to generate hash code from both database server and compare them? How to write the following pseudo SQL in SQL Server? Especially the two getHash functions which accept mutliple numeric/float columns in SQL server and oracle.

select s.PK
from sqltable s
    join openquery(oracleLinkedServer, 
      'select PK, getHash(Column1, floatColumn2, ..., floatColumnN) oracleHash 
       from oracleTable') o on o.PK = s.PK
where
    getHash(Column1, floatColumn2, ..., floatColumnN) <> oracleHash

Answer 1

In SQL Server:

select upper(substring(sys.fn_sqlvarbasetostr(hashbytes('MD5','A')),3,32));

result:

7FC56270E7A70FA81A5935B72EACBE29

In Oracle :

select rawtohex(
    DBMS_CRYPTO.Hash (
        UTL_I18N.STRING_TO_RAW ('A', 'AL32UTF8'),
        2)
    ) from dual;

result:

7FC56270E7A70FA81A5935B72EACBE29

Make sure your strings are exactly the same (case sensitive). Here I used 'A' as a simple example, but it could be any string really.

If you avoid data type differences by converting to a big string, you should be able to produce the same md5 hash on different platforms. Note that SQL Server prepended a '0x' to the hash to denote hex representation, which I stripped with the substring.

Answer 2

In SQL Server you have hashbytes(); in Oracle you have DBMS_CRYPTO.Hash(). You should be able to use them to calc an MD5 hash on both sides, though I am not positive the hashes will match... its worth a shot.

There are other ways to compare tables but to answer your question these are the two native functions on either platform.