Testing For Record Changes

We'd like to be smart when performing updates to tables so that the LAST_UPDATE_DTS column really represents the last time new data was entered. To do this we need to test the proposed new set of data against what currently exists. I'm thinking about something like the following:

old_hash := dbms_utility.get_hash_value(current_record_in_varchar2, ?, ?) new_hash := dbms_utility.get_hash_value(proposed_new_record_in_varchar2, ?, ?)

Then an update would only be performed if old_hash is != new_hash.

I have a few questions:

How do I convert an entire record to varchar2 so that it can be passed into the get_hash_value function? (I'd prefer to avoid column by column concatination so that the function can remain generic)  

What other parameters should be used in the get_hash_value call?

Will the get_hash_value function always produce the same value for a given string? (I'm pretty sure it does, but want to be sure)

Is this approach more efficient than using DECODE to detect field by field changes? I favor this approach because if its possible to convert an entire record to varchar2 the function we create can be very generic (no record-specific data would be involved.)

Is it more efficient to just eliminate the get_hash_value call and test for string equivilence?

I'm very open to other suggestions as well.

Brian Received on Sat May 08 1999 - 09:11:19 CDT