Sambhav Singh, November 30, 2016 - 8:39 am UTC
Hi, Thanks for your help.
MD5 hash value is different from both database.
So i think this approach will not work.
Kindly suggest i there any other alternative
November 30, 2016 - 10:47 am UTC
Are you sure the columns contain the same value? Have you tried any of the other hashes?
Try SH1
Robert Massey, November 30, 2016 - 11:22 am UTC
I had to do something similar. I found that SH1 worked. Here's an example for hashing 'abc'.
In SQL Server:
select convert(varchar(max), hashbytes('sha1', 'abc'), 2)In Oracle:
select rawtohex(dbms_crypto.hash(utl_raw.cast_to_raw('abc'), 3)) from dual;'3' corresponds to dbms_crypto.hash_sh1. If you are using the function in a procedure, you can do this:
declare
sh1 varchar2(40);
begin
select rawtohex(dbms_crypto.hash(utl_raw.cast_to_raw('abc'), dbms_crypto.hash_sh1))
into sh1
from dual;
dbms_output.put_line(sh1);
end;
/All these should produce the same hash: A9993E364706816ABA3E25717850C26C9CD0D89D
November 30, 2016 - 2:08 pm UTC
Thanks for sharing.
Samby, November 30, 2016 - 2:47 pm UTC
Hi
Yes I tried for other columns hashes.iys matching.
Issue is for blob/clob columns.
Hash values are different for these columns.
Please suggest if there is any workaround.
December 01, 2016 - 12:52 am UTC
It may well the case that the storage mechanisms are different between the two platforms, and hence raw comparison fails.
You might need to extract piecewise subset of the blob/clob and then compare those.
Another point...
J. Laurindo Chiappa, November 30, 2016 - 6:16 pm UTC
My suggestion is to try a binary dump for the LOB column in both databases - maybe in one database you have some control characters (such as cr/lf combinations) not present in the other : this could explain the different hash results in the differnt dbs using the same hash algorithm....
Regards,
José Laurindo Chiappa