CODEWITHSUNDEEP
oracle-databasehashmd5
Fabrice BOUCHAREL
Fabrice BOUCHAREL

Reputation: 171

Different Output with same Input for ORACLE MD5 Function

At a given time I stored the result of the following ORACLE SQL Query :

SELET col , TO_CHAR( LOWER( STANDARD_HASH( col , 'MD5' ) ) AS hash_col FROM MyTable ;

A week later, I executed the same query on the same data ( same values for column col ).

I thought the resulting hash_col column would have the same values as the values from the former execution but it was not the case.

Is it possible for ORACLE STANDARD_HASH function to deliver over time the same result for identical input data ? It does if the function is called twice the same day.

Upvotes: 0

Views: 1002

Answers (1)

EdStevens
EdStevens

Reputation: 3872

All we have about the data changing (or not) and the hash changing (or not) is your assertion.

You could create and populate a log table:

create table hash_log (
   sample_time timestamp,
   hashed_string varchar2(200),
   hashed_string_dump varchar2(200),
   hash_value varchar2(200)
   );

Then on a daily basis:

insert into hash_log values 
  (select systimestamp,
          source_column,
          dump(source_column),
          STANDARD_HASH(source_column , 'MD5' )
   from source_table
   );

Then, to spot changes:

select distinct      hashed_string ||
                     hashed_string_dump ||
                     hash_value 
from hash_log;

Upvotes: 1

Related Questions