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)
Recent comments
3 years 4 weeks ago
3 years 16 weeks ago
3 years 21 weeks ago
3 years 22 weeks ago
3 years 26 weeks ago
3 years 47 weeks ago
4 years 15 weeks ago
4 years 45 weeks ago
5 years 29 weeks ago
5 years 30 weeks ago