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

hash value in oracle
oracle hash function
oracle hash clob
dbms_crypto.hash example
hash sql
dbms_crypto_toolkit hash
data warehouse hash key
sql server md5 hash data type

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

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.

Hashing in SQL Server and Oracle for the same output, Database SQL Language Reference. Contents. Previous · Next You can obtain different hash results for the same query by changing the seed value. SELECT  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.

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.

ORA_HASH, It needs to do that consistently, of course, and then - by comparing the HASH you just did SQL - you either a) succeed, b) get an error message To move a Terabyte table between 2 multi-CPU servers you need to kick off  With Exadata or Oracle 11g, did Oracle introduce a hash function which can accept multiple columns as arguments and give a hash value for those columns? Something like: hash(col1, col2, col3, ) Need it to store natural keys in tables, allows me to compress a multi-column natural key into a single column.

You can use CHECKSUM() in SQL Server to compute a multi-column hash.

Creating a unique HASH value for the contents of - Ask Tom, GETHASH to get the hash value of a result set for one of our requirements. I would expect in the first instance that either the query itself is returning different on configuration I think), whereas SQL Plus will select all of them. first is: GENERATE AND MANUALLY COMPARE THE TWO RESULT SETS. As Oscar mentioned in his comment, you are seeing no results because the nvarchar value 0xA00592FC3E531C5F7608110F73E8AE4B4F2EA4C3 is different to the binary value 0xA00592FC3E531C5F7608110F73E8AE4B4F2EA4C3. Ideally, you would be storing your hash values in an appropriate binary or varbinary data typed column, but if you absolutely cannot change that in your database, you need to convert your nvarchar value to binary or varbinary (or vice versa) in order to actually compare the two values:

If you have limited access to the Oracle system, and haven't been granted execute permissions on DBMS_CRYPTO.Hash(), you can still use DBMS_OBFUSCATION_TOOLKIT.MD5() to generate the same hashed value on both Oracle and SQL Server. You just have to make sure to convert the string to the same code page as the machine running SQL Server. I believe the default on most North American Windows boxes is windows-1252 ANSI Latin 1; Western European (Windows) (from https://docs.microsoft.com/en-us/windows/desktop/intl/code-page-identifiers)

I don't particularly like having function calls in my WHERE clauses, so I put the SQL side hash into a CTE, and then join to the OPENQUERY results.

;WITH sqlHash AS
(
    SELECT s.PK
        ,UPPER(CONVERT(CHAR(32), HASHBYTES('MD5', s.Column1 + '|' + s.Column2 + '|' + s.ColumnN), 2)) AS sqlHash
    FROM sqltable s
)
SELECT s.PK
FROM sqlHash s
JOIN OPENQUERY
     ( oracleLinkedServer, '
     SELECT 
        PK
        ,CONVERT(UPPER(RAWTOHEX(
            SYS.DBMS_OBFUSCATION_TOOLKIT.MD5(input_string => "oraColumn1" || ''|'' || "oraColumn2" || ''|'' || "oraColumnN")
            )),''AL32UTF8'',''WE8MSWIN1252'') AS oracleHash
       FROM oracleTable' ) o ON o.PK = s.PK
WHERE s.sqlHash <> o.oracleHash

A couple of things to point out here that might not be immediately apparent.

First, I'm adding a pipe character, |, between each column in the concatenated text. This is differentiate combinations like "digital" + "aaron" and "digi" + "talaaron" from producing the same hash, as the string would become "digital|aaron" and "digi|talaaron" which should produce different hashed values.

Second, when we get the SQL side has in the CTE, the value comes back as a VARBINARY. If we just displayed this value, there would be a leading 0x. But we want to strip this off, and also convert the value to something a bit more useful than a VARBINARY. So we convert to CHAR(32) with a style of 2.

Hash value generated does not match, 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​  You can potentially speed up the identification of changed data by adding a new column to your target table containing a hash value generated by applying the DBMS_UTILITY.GET_HASH_VALUE() function to a concatanation of your columns (populating and updating this value with a trigger I guess).

Comparing Data of Two Oracle Tables Using MD5 Hash, In relational database business there is offen a need to compare data of two tables In most cases the check is only a binary one, so the answer is yes if the two tables The main goal it to omit the expesive sorting operation which is the working Quick research provides that Oracle can calculate MD5 hash out of the box. Thanks for the question, Yamini. Asked: May 19, 2017 - 8:55 am UTC. Answered by: Connor McDonald - Last updated: May 19, 2017 - 7:46 pm UTC

How to Build Hash Keys in Oracle, In the Oracle database, hash functions are used for hash joins, hash For me as a Data Warehouse consultant, it is important to understand If data has to be compared or moved from one environment to another, the sequence numbers must be recalculated. SQL runtime to generate 1 million has keys. So many changes to a sql id on the same day seems odd. I have checked the stats and refreshed wherever found them as stale, checked if there were changes in the sql code -- no changes at all. I know there may be more details required for this query but would request you to accept this case and I can then provide all the relevant details as

The HashBytes function in T-SQL, Microsoft SQL Server has supported the same hashing values from two values: the algorithm to use and the value to get the hash for. I will now explain the HashBytes function as it existed before 2014, not It can be noted that collations are only reviewed when we compare values between two files. Currently, we do this by computing the individual hashes of each column value, perform bit-xor on the column hashes to get the row hash, then perform bit-xor on all the row hashes to come up with the table hash.

Comments
  • Given that many of the datatypes between SQL Server and Oracle are unlikely to have 100% matching representations, it's unlikely to work
  • it may work if you convert all values to a big string (x, 1.234, 2.345 = "x1.2342.345") and then compute hash based on same encoding (md5 for example).
  • @tbone concatenate them to a big string may introduce more uncertainty.
  • @Damien_The_Unbeliever agreed. I am doing this to avoid to pull too many data from remote server because of slow connection.
  • @NickW I'm fairly sure that given the same input string, an Oracle implementation of md5 will give same 32 hex value as SQL Server implementation (assuming no use of random numbers or random salts). I don't have access to SQL Server, but I'd challenge u to prove this wrong.
  • Thanks. In my case i have changed AL32UTF8 for AL16UTF16LE on Oracle side and cast the text on MS SQL side to nvarchar ( cast('ĄĘA' as nvarchar(max) ) but anyway many thanks for this.
  • How can I get the hash for the entire row instead of just the column?
  • @Aravindh You can concatenate strings, but it sounds like you want a unique hash/id for a row, which means you should look into sys_guid
  • @tbone In my case I want to compare rows between sql server and oracle and I dont think GUID will help since I cant generate them on the sql server side as well?
  • @Aravindh in that case, pick the columns that would best represent uniqueness in the row and concatenate them into 1 big string (of a reasonable size, say up to 4000). You'd convert dates to strings of course.
  • These two functions don't accept multiple columns so users had to combine multiple columns into one. Yes, as you mentioned, it's not sure if the hashed values match even if they values are the same, especially for these float number.
  • @NickW If you want to hash multiple columns at the same time, on Oracle you can use DBMS_SQLHASH: docs.oracle.com/cd/B19306_01/network.102/b14266/appendixb.htm But I think that package is very unlikely to match any output from SQL Server.