Checksum of row using ora_hash giving different results for different users

Question

Trying to generate a unique checksum for a data row in am Oracle table, for use in making sure that the row isn't changed between two users retrieving them and trying to update them at the same time.

 SELECT ora_hash( KY_REFUND_ID CD_STATUS || KY_CHECK_NUM || 
        COMMENT || CREATED_BY || TS_CREATED || TX_UPDATED_BY || TS_UPDATED) as checksum
 INTO p_checksum
 FROM REFUND_CHECKS r
 WHERE ROWID = p_rowid;

The odd thing is, we get a different checksum if the procedure is called within sqldeveloper in debug, as opposed to calling it via the website. That becomes a problem when the checksum is calculated again internally to compare with my value - I got 12345, but internally the same data results in 78904, so the system says they don't match.

As far as I can see, the only way two different users get a different checksum when looking at the same data...is they're not looking at the same data. I suspect there's some unseen "something" that different between the two calls. And the only thing I can see that differs is the account being used.

In sqldeveloper, the call is made using the schema name, but the website is calling in as dotnet_user.

By any chance, is the account name used as some extra value in the math when determining the checksum? If not, what other unseen differences might exist that could result in different results, and more importantly, how can they be standardized so both sides get the same result?

Answer 1

The problem is that you're relying on implicit conversion. You're generating a string by concatenating several values together, which means the timestamp (presumably, from the names) columns are being implicitly converted to strings using the session's NLS settings. And you have different NLS settings in SQL Developer and through you web client.

As a simple demonstration:

alter session set nls_timestamp_format = 'YYYY-MM-DD HH24:MI:SS';

select ora_hash(timestamp '2018-01-01 00:00:00') as ts_hash,
       'abc' || timestamp '2018-01-01 00:00:00' as str,
       ora_hash('abc' || timestamp '2018-01-01 00:00:00') as str_hash
from dual;

   TS_HASH STR                      STR_HASH
---------- ---------------------- ----------
1986439397 abc2018-01-01 00:00:00  588765268

alter session set nls_timestamp_format = 'DD-Mon-YYYY HH:MI:SS AM';

select ora_hash(timestamp '2018-01-01 00:00:00') as ts_hash,
       'abc' || timestamp '2018-01-01 00:00:00' as str,
       ora_hash('abc' || timestamp '2018-01-01 00:00:00') as str_hash
from dual;

   TS_HASH STR                          STR_HASH
---------- -------------------------- ----------
1986439397 abc01-Jan-2018 12:00:00 AM 2809284723

Same timestamp value, and same hash of the timestamp itself in its native data type; but different implicit conversions to strings, and so different hashes of those strings.

Change your code to explicitly convert the timestamps to a specific fixed format and it will no longer rely on NLS, and so will become consistent, e.g.

 SELECT ora_hash( KY_REFUND_ID CD_STATUS || KY_CHECK_NUM || 
          COMMENT || CREATED_BY ||
          to_char(TS_CREATED, 'SYYYYMMDDHH24MISSFF9') || TX_UPDATED_BY ||
          to_char(TS_UPDATED, 'SYYYYMMDDHH24MISSFF9')) as checksum
 INTO p_checksum
 FROM REFUND_CHECKS r
 WHERE ROWID = p_rowid;