Database Reference
In-Depth Information
As you can see, the hash is just some number. It is the value we would want to use before updating. To update that
row, we would lock the row in the database as it exists right now, and then compare the hash value of that row with the
hash value we computed when we read the data out of the database. The logic for doing so could look like the following:
EODA@ORA12CR1> exec :dname := lower(:dname);
PL/SQL procedure successfully completed.
EODA@ORA12CR1> update dept
2 set dname = :dname
3 where deptno = :deptno
4 and ora_hash( dname || '/' || loc ) = :hash
5 /
1 row updated.
EODA@ORA12CR1> select dept.*,
2 ora_hash( dname || '/' || loc ) hash
3 from dept
4 where deptno = :deptno;
DEPTNO DNAME LOC HASH
---------- ---------- ---------- ----------
10 accounting NEW YORK 2818855829
Upon re-querying the data and computing the hash again after the update, we can see that the hash value is
different. If someone had modified the row before we did, our hash values would not have compared. We can see this
by attempting our update again, using the
old
hash value we read out the first time:
EODA@ORA12CR1> update dept
2 set dname = :dname
3 where deptno = :deptno
4 and ora_hash( dname || '/' || loc ) = :hash
5 /
0 rows updated.
As you see, there were zero rows updated, since our hash value did not match the data currently in the database.
In order for this hash-based approach to work properly, we must ensure every application uses the same
approach when computing the hash, specifically they must concatenate
dname
with '
/
' with
loc
- in that order. To
make that approach universal, I would suggest adding a virtual column to the table (in Oracle 11
g
Release 1 and
above) or using a view to add a column, so that the function is hidden from the application itself. Adding a column
would look like this in Oracle 11
g
Release 1 and above:
EODA@ORA12CR1> alter table dept
2 add hash as
3 ( ora_hash(dname || '/' || loc ) );
Table altered.
EODA@ORA12CR1> select *
2 from dept
3 where deptno = :deptno;
DEPTNO DNAME LOC HASH
---------- ---------- ---------- ----------
10 accounting NEW YORK 2818855829
