BINARY_CHECKSUM IN ORACLE

[Vinay Bhushan]

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.

[HansF]

Post by Vinay Bhushan
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?

[Vladimir M. Zakharychev]

Post by HansF

Post by Vinay Bhushan
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

[HansF]

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.)

Very hard to tell without knowing the task at hand.

Bingo ...

[Vladimir M. Zakharychev]

Post by HansF
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

[Vladimir M. Zakharychev]

Post by HansF
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

[HansF]

Post by HansF
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. :)

;-)

[DA Morgan]

Post by Vinay Bhushan
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

[Malcolm Dew-Jones]

Vinay Bhushan (***@gmail.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

[Vinay Bhushan]

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

[Vladimir M. Zakharychev]

Post by Vinay Bhushan
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.

How about creating a trigger for each row that will recalculate
dependent data when the row changes? Or am I missing something?

Post by Vinay Bhushan
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.

Well, probably because they provide all too many other features
and overlooked this one or thought that those in need of it will
code it themselves (then they realised there is need and created
OWA_OPT_LOCK package.) Besides, in Oracle writers do
not block readers so if someone locks a row for update this
doesn't prevent others from reading it - only from changing it.
OWA_OPT_LOCK is mainly for web applications, which do
not keep sessions open between calls and thus can't use
traditional locking mechanism.