/*

Description - Converts SQLID into HASH_VALUE
Input Parameter - SQL_ID
Source - http://blog.tanelpoder.com/2009/02/22/sql_id-is-just-a-fancy-representation-of-hash-value/

Basically all it does is takes the SQL ID, interpret it as a 13 character base-32 encoded number and then take only the lowest 4 bytes worth of information (4 bytes in base-256) out of that number and that’s the hash value.

Actually, since 10g the full story goes like this:

1) Oracle hashes the library cache object name with MD5, producing a 128 bit hash value
2) Oracle takes last 64 bits of the MD5 hash and this will be the SQL_ID (but it’s shown in base-32 for brevity rather than in hex or as a regular number)
3) Oracle takes last 32 bits of the MD5 hash and this will be the hash value (as seen in v$sql.hash_value).

In 10gR1+, you can do this:
SQL> select dbms_utility.SQLID_TO_SQLHASH(‘btxdhy7gkbwjk’) hash_value FROM DUAL;
HASH_VALUE
———-
3743806002

*/

select
    lower(trim('&&1')) sql_id
  , trunc(mod(sum((instr('0123456789abcdfghjkmnpqrstuvwxyz',substr(lower(trim('&&1')),level,1))-1)
                       *power(32,length(trim('&&1'))-level)),power(2,32))) hash_value
from
    dual
connect by
    level <= length(trim('&&1'))
/