How to match the values generated by MD5 hash (Informatica) and Standard hash (Oracle)?

Question

Is there a way to match MD5 hash generated by Informatica and Standard hash (Oracle)? The values generated by Informatica and Oracle are different.

Example:

MD5 hash (Informatica)

MD5(TO_CHAR(ENC_CREATE_DTM)||'~'||TO_CHAR(ENCOUNTER_TYPE))

Hash Value: 132334D66279AF035C5B03B5293457EB10693BBA

Standard hash (Oracle)

select standard_hash(ENC_CREATE_DTM
||'~'||ENCOUNTER_TYPE)hash_cd

Hash Value: 1DBED2F180706832F0D138FEB426356B

Answer 1

As shown in the documentation, standard_hash uses SHA1 by default, but you can tell it to use MD5:

standard_hash(ENC_CREATE_DTM
||'~'||ENCOUNTER_TYPE, 'MD5')

But you also need to make sure your non-string values (like date/timestamps) are being converted to strings in the same format - don't rely on implicit conversion or NLS settings, be explicit and specific.