Consider the following simple setup
SQL> create table t ( x int, b blob );
Table created.
SQL> declare
2 bin blob;
3 begin
4 insert into t values (1, empty_blob())
5 returning b into bin;
6
7 dbms_lob.writeappend(bin,10000,utl_raw.cast_to_raw(rpad('x',10000,'x')));
8 commit;
9 end;
10 /
PL/SQL procedure successfully completed.
SQL> select ora_hash(b) from t;
ORA_HASH(B)
-----------
3856858313
SQL> select ora_hash(b) from t;
ORA_HASH(B)
-----------
3856858313
SQL> select ora_hash(b) from t;
ORA_HASH(B)
-----------
3856858313
That all seems fine…but thats because I ran the ORA_HASH commands quickly one after the other… Now that 30 seconds has passed (as I type this into the blog)…lets run the same ORA_HASH again
SQL> select ora_hash(b) from t;
ORA_HASH(B)
-----------
1078420585
It changes ! In fact, one most platforms it appears to change every 3 seconds, so presumably somewhere in there its related to one the internal Oracle timers in some way.
Bottom line – you can’t use ORA_HASH on LOB’s. (There is also some information in Metalink note 427803.1)
Learning is not a spectator sport 
Leave a reply to Gary Myers,SydOracle (@sydoracle) Cancel reply