Apologies if this is not the right forum for this post but I was just wondering if anyone would know why SHA256 or MD5 Hash produced on Oracle 12.1 database and Snowflake would not produce the same Hash result when SHA256 is a Standard governed by National Institue for Standards and Technology (NIST). Please do suggest an alternative forum if you believe there is a better location for this Post:
Please see details below:
Oracle Command used:
select KEY_ID, STANDARD_HASH(KEY_ID, 'MD5') from TABLE_A where KEY_ID = 10881;
select KEY_ID, STANDARD_HASH(KEY_ID, 'SHA256') from TABLE_A where KEY_ID = 10881;
Snowflake MD5 Command:
select KEY_ID, MD5(KEY_ID) from TABLE_A where KEY_ID = 10881; or
select KEY_ID, MD5_HEX(KEY_ID) from TABLE_A where KEY_ID = 10881;
Snowflake SHA256 Command:
select KEY_ID, SHA2(KEY_ID) from TABLE_A where KEY_ID = 10881; or
select KEY_ID, SHA2_HEX(KEY_ID) from TABLE_A where KEY_ID = 10881;
SHA256 RESULT:
Oracle 12.1 SHA256: 414CB32CFB90DF95A331096A934BA94D5AFA83145738F9289C3E8DE5013C5976
Snowflake SHA256: 91b610d1815106e9edd7e90a00bc860073211b77640a52dd3fbb87b7a0867262
MD5 RESULT:
Oracle 12.1 MD5: 62E8E7508DE4A796733E958DC1CCCCAE
Snowflake MD5: 9ec1994bb176cb0b7a2cec414fe8ffb2
DATA TYPES
The data type in Oracle is NUMBER and in Snowflake the data type is configured as NUMBER(38,0) . The value of the column in both databases is 10881.
FYI: The physical data in Snowflake is stored on AWS s3 storage with an External Table defined in Snowflake but I'm not expecting anyone on this forum to be a Snowflake Cloud expert.
Just hoping that someone within the Oracle domain might have had some insights, exposure or experience.