Ny function like checksum in Oracle

checksumoracle

I need to calculate the checksum of some columns. The built-in function checksum is used to calculate the checksum in Microsoft SQL Server.

Is there any built-in function checksum in Oracle?

Best Answer

You can use Oracle's STANDARD_HASH function.

Oracle's STANDARD_HASH function "computes a hash value for a given expression" (see the documentation here). Use the checksums in the WHERE clause of the UPDATE (in the MERGE statement).

You might do something like this:

-- 2 tables
create table table1 as
select 1 id, 1 a1, 1 b1, 1 c1, 1 d1, 1 e1, 1 f1 from dual;

create table table2 as
select 2 id, 2 a2, 2 b2, 2 c2, 2 d2, 2 e2, 2 f2 from dual;

and then calculate a SHA256 checksum:

SELECT
  STANDARD_HASH ( T.id || T.a1 || T.b1 || T.c1 || T.d1 || T.e1 || T.f1, 'SHA256' )
AS my_hash_1
FROM table1 T ;
  • MY_HASH_1 0x2558A34D4D20964CA1D272AB26CCCE9511D880579593CD4C9E01AB91ED00F325

and

SELECT 
  STANDARD_HASH ( T.id || T.a2 || T.b2 || T.c2 || T.d2 || T.e2 || T.f2, 'SHA256' ) 
AS my_hash_2 
FROM table2 T ;

  • MY_HASH_2 0xCC2E018AA6EB9612CCD027BBDCDC9B8C8D351789F14CAE4D688A876C18938235

This will tell you if your records are the same or different (well, to a very high probablity! :-) ).

With STANDARD_HASH, you get to choose your hashing algorithm. You might want to be careful about your choice - MD5 is much less processor intensive than SHA256. If security is not a major concern, you might want to use that instead? See the fidddle here for more.

See here for the excellent question, and here (same thread) for the excellent answer, both of which I (shamelessly!) lifted to answer this question. I felt that the thread and answer were both good enough to merit a place on dba.stackexchange!

Related Question