MD5 of entire row in oracle

user_9122757 0
Viewed 119

Below is the query to get MD5 of entire row in snowflake

SELECT MD5(TO_VARCHAR(ARRAY_CONSTRUCT(*)))FROM T

taken from here

what is the alternative query in oracle to achieve such requirement without having to put all column names manually.

oracle
1 Answers

You may use the packaged function dbms_sqlhash.gethash as described below, but remember:

  • the package was removed from the documentation (I guess in 11g), so in the recent releases this is an undocumented feature

  • if you calculate the hash code from more than one row you must define order (order by on a unique key(s)) . Otherwise the calculated hash is not deterministic. (This was probaly the reason of the removal)

  • the columns with other data types than varchar2 are converted to strings before the hash calculation, so the result is dependent on the NLS setting. You must stabilize the NLS setting to get reproducible results, e.g. with alter session set nls_date_format='dd-mm-yyyy hh24:mi:ss';

  • The column must be concatenated with some special delimiter (that does not occure in the data) to aviod collision: 'A' || null is the same as null || 'A'. This are unknown internals, so it is rather hard to compare the result MD5 hash with hash calculated on other (non Oracle) data.

  • You need extra grant to execute the package

Some additional info

Example

select * from tab where x=1;

         X Y Z                  
---------- - -------------------
         1 a 13.10.2021 00:00:00
         
select
dbms_sqlhash.gethash(
  sqltext     => 'select * from tab where x=1',
  digest_type => 2 /* dbms_crypto.hash_md5 */
) MD5
from dual;

MD5
--------------------------------
215A9C4642A3691F951DD8060877D191

Order Independent Hash Code of a Table

Contrary to a file (where the order matter) in a database table the order is not relevant. It would be therefore meaningfull to have a possibility to calculate an order independent hash code of a table. Unfortunately this feature is currently not available in Oracle, but was implemented as a prototype as described here

user_48081220
Related