ORA_HASH and LOBs …. not nice partners

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)