BINARY_CHECKSUM IN ORACLE

#1

hello gents,

i have a task of porting from sql server to oracle, i am looking for
help on the following

IN sql server i write
-----------
SELECT BINARY_CHECKSUM ( FIELD_A,FIELD_B,FIELD_C,FIELD_D) AS CHK
FROM TEST_TABLE

-------------

i dont have a function which is equivalent to binary_checksum. can i
have this in any other way.
this is to uniquely identify a record.

#2

On Tue, 02 May 2006 08:52:12 -0700, Vinay Bhushan wrote:

Quote:

this is to uniquely identify a record.

In what way do you mean 'uniquely identify a record'. Is the rowid not
unique enough? If not, could you perhaps use the DBMS_CRYPTO.Hash
function?

--
Hans Forbrich
Canada-wide Oracle training and consulting
mailto: Fuzzy.GreyBeard_at_gmail.com
*** Top posting [replies] guarantees I won't respond. ***

#3

Quote:

this is to uniquely identify a record.

In what way do you mean 'uniquely identify a record'. Is the rowid not
unique enough? If not, could you perhaps use the DBMS_CRYPTO.Hash
function?

ROWID won't do in this case, BINARY_CHECKSUM is more like
a hash on arbitrary list of columns. There's no equivalent in Oracle,
and it's not probably possible to create one that will exactly match
this SQL Server function because it accepts variable number of
arguments, each of any supported type. Oracle attempted to create
similar function with OWA_OPT_LOCK.CHECKSUM, but it is
prototyped differently and only works on whole rows.

Question to the OP: for which purpose BINARY_CHECKSUM is
used in the SQL Server application being ported? Maybe it is not
really needed in Oracle. Or maybe OWA_OPT_LOCK is exactly
what you are looking for. Very hard to tell without knowing the
task at hand.

Hth,
Vladimir M. Zakharychev
N-Networks, makers of Dynamic PSP(tm)
http://www.dynamicpsp.com

#4

On Tue, 02 May 2006 10:44:07 -0700, Vladimir M. Zakharychev wrote:

Quote:

ROWID won't do in this case, BINA ...

Please expand. (If the uniqueness MUST BE based on a hash, I agree with
your assessment. If it's just plain uniqueness, then I'm intrigued by
your answer.)

Quote:

Very hard to tell without knowing the task at hand.

Bingo ...

--
Hans Forbrich
Canada-wide Oracle training and consulting
mailto: Fuzzy.GreyBeard_at_gmail.com
*** Top posting [replies] guarantees I won't respond. ***

#5

Vinay Bhushan wrote:

Quote:

hello gents,

i have a task of porting from sql server to oracle, i am looking for
help on the following

IN sql server i write
-----------
SELECT BINARY_CHECKSUM ( FIELD_A,FIELD_B,FIELD_C,FIELD_D) AS CHK
FROM TEST_TABLE

-------------

i dont have a function which is equivalent to binary_checksum. can i
have this in any other way.
this is to uniquely identify a record.

Assuming 10g:

SELECT ora_hash(value)
FROM dual;

Daniel A. Morgan
www.psoug.org

#6

Quote:

Please expand. (If the uniqueness MUST BE based on a hash, I agree with
your assessment. If it's just plain uniqueness, then I'm intrigued by
your answer.)

Well, it's not really *uniqueness* that BINARY_CHECKSUM ensures
(actually it doesn't,) it's the fact that checksummed row was or wasn't
changed since checksum was last computed. ROWID uniquely
identifies the row, but it doesn't change when row changes (except a
few special cases.) BINARY_CHECKSUM allows you to capture hash
of the row as of the time of the call and you can then use it to
quickly find changed rows or check if this particular row was changed
since you captured the checksum. Best use of this function and
its case-insensitive cousin CHECKSUM is probably optimistic
locking (OWA_OPT_LOCK package serves the same purpose.)

Hth,
Vladimir M. Zakharychev
N-Networks, makers of Dynamic PSP(tm)
http://www.dynamicpsp.com

#7

Quote:

Please expand. (If the uniqueness MUST BE based on a hash, I agree with
your assessment. If it's just plain uniqueness, then I'm intrigued by
your answer.)

On second thought, if OP really meant to use BINARY_CHECKSUM
to uniquely identify rows, then ROWID is certainly what he's after,
especially in the absence of primary keys.

Regards,
Vladimir M. Zakharychev
N-Networks, makers of Dynamic PSP(tm)
http://www.dynamicpsp.com

#8

On Tue, 02 May 2006 12:08:37 -0700, Vladimir M. Zakharychev wrote:

Quote:

Please expand. (If the uniqueness MUST BE based on a hash, I agree with
your assessment. If it's just plain uniqueness, then I'm intrigued by
your answer.)

On second thought, if OP really meant to use BINARY_CHECKSUM to uniquely
identify rows, then ROWID is certainly what he's after, especially in the
absence of primary keys.

;-)

#9

Vinay Bhushan (bhushanvinay (AT) gmail (DOT) com) wrote:
: hello gents,

: i have a task of porting from sql server to oracle, i am looking for
: help on the following

: IN sql server i write
: -----------
: SELECT BINARY_CHECKSUM ( FIELD_A,FIELD_B,FIELD_C,FIELD_D) AS CHK
: FROM TEST_TABLE

Perhaps MD5 will work well enough. google "Oracle Sql md5" for examples.

You may have to write your own wrapper(s) to make it do exactly what you
want.

In your wrapper, to concatenate fields, you should instead loop over them
and concatenate the previous checksum each time so (for example)
'h','ello' will checksum differently than 'he','llo'

$0.10

#10

Thanks a lot for the effort,

In regard to the question what i asked i think it would have been much
clear if i could have mentioned the oracle version and the purpose of
doing it also.

Currently i use Oracle version 9 i ,

The reason why its a binary check sum is to hold a logical key to the
position, if a accounting data refresh happens and a position doesnot
change then we dont calculate or change other data accordingly hence we
calculate a checksum for fields like accountid, price, date and other
data in it which helps us to identify the position uniquely.

i will try with the follwoing and let you know regarding this
"OWA_OPT_LOCK"

is there any reason why oracle doesnot provide a binary_checksum
function. may be the question is too much to think about all i want is
to calculate the binary checsum at EOD.

Regards
Vinay