Sfrgttk

Discouraging missile alpha strikes

Why did Tywin never remarry?

Why does Python copy NumPy arrays where the length of the dimensions are the same?

Does the phrase がんばする makes sense?

Subcaptions in subfigures

What is the Guild Die for?

Is there a technology capable of disabling the whole of Earth's satellitle network?

Does limiting the number of sources help simplify the game for a new DM with new and experienced players?

find command cannot find my files which do exist

A dragon's soul trapped in a ring of mind shielding wants a new body; what magic could enable her to do so?

How to not forget my phone in the bathroom?

Workplace intimidation due to child's chronic health condition

Sing Baby Shark

Does human life have innate value over that of other animals?

Does an increasing sequence of reals converge if the difference of consecutive terms approaches zero?

Headless horseman claims new head

Why are energy weapons seen as more acceptable in children's shows than guns that fire bullets?

Does the race of half-elves on Khorvaire in Eberron have its own name?

Uncountable set with a non-discrete metric

Finding a basis of an infinite dimensional vector space with a given vector

How to assess the susceptibility of a U.S. company to go bankrupt?

Substitute ./ and ../ directories by actual names

Why does calling Python's 'magic method' not do type conversion like it would for the corresponding operator?

Can a rabbi conduct a marriage if the bride is already pregnant from the groom?

Is there any function like checksum in Oracle

Oracle concatenation of queries and function index usageMap Oracle roles to Active Directory groups?How to document Oracle database?Are there any incompatibilities between Oracle 11 and 12?How to Resolve Corruption Detected by BACKUP WITH CHECKSUM, But NOT DBCC CHECKDB?Function Variable Number of Arguments plsqlMysql checksum for data onlyOracle Equivalent of SQL Server's Bitwise OperatorsIs there harm in applying passwords to built-in Oracle Roles like DBA, Connect, etcValidation of data with checksum vs Binary_checksum

1

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?

oracle checksum

share|improve this question

asked 2 days ago

NoahNoah

82

New contributor

Noah is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a comment | 

1

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?

oracle checksum

share|improve this question

asked 2 days ago

NoahNoah

82

New contributor

Noah is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a comment | 

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?

oracle checksum

share|improve this question

asked 2 days ago

NoahNoah

82

New contributor

Noah is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

share|improve this question

asked 2 days ago

NoahNoah

82

New contributor

Noah is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

share|improve this question

asked 2 days ago

NoahNoah

82

New contributor

Noah is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

asked 2 days ago

NoahNoah

82

New contributor

Noah is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

asked 2 days ago

NoahNoah

82

1 Answer
1

active

oldest

votes

3

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!

share|improve this answer

edited 10 mins ago

answered 2 days ago

VéraceVérace

16.1k33550

add a comment | 

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

Noah is a new contributor. Be nice, and check out our Code of Conduct.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f230020%2fis-there-any-function-like-checksum-in-oracle%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

3

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!

share|improve this answer

edited 10 mins ago

answered 2 days ago

VéraceVérace

16.1k33550

add a comment | 

3

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!

share|improve this answer

edited 10 mins ago

answered 2 days ago

VéraceVérace

16.1k33550

add a comment | 

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!

share|improve this answer

edited 10 mins ago

answered 2 days ago

VéraceVérace

16.1k33550

-- 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;

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 ;

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 ;

share|improve this answer

edited 10 mins ago

answered 2 days ago

VéraceVérace

16.1k33550

answered 2 days ago

VéraceVérace

16.1k33550

answered 2 days ago

VéraceVérace

16.1k33550