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

Question

Is it possible to generate a hash code from both database servers and compare them? How to write the following pseudo SQL in SQL Server? Especially two getHash functions that accept mutlyiple numeric / float columns on 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 lines are exactly the same (case sensitive). Here I used "A" as a simple example, but it can be any line.

If you avoid data type differences by converting them to a large string, you should be able to create the same md5 hash code on different platforms. Note that SQL Server added “0x” to the hash to indicate the hexadecimal representation that I shared with the substring.

Answer 2

In SQL Server you have hashbytes () ; in Oracle you have DBMS_CRYPTO.Hash () . You should use them to calculate the MD5 hash on both sides, although I'm not sure if the hashes will match ... it's worth it.

There are other ways to compare tables, but to answer your question, these are two own functions on any platform.

Answer 3

You can use CHECKSUM() in SQL Server to compute hashes with multiple columns.