Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Computing Hash value

Re: Computing Hash value

From: Nigel Thomas <nigel_at_preferisco.com>
Date: Thu, 21 Sep 2006 05:08:58 -0700 (PDT)
Message-ID: <20060921120858.23778.qmail@web54713.mail.yahoo.com>


> I'm trying to calculate the hash value of statements as oracle does in
> the v$sqlarea.

How about parsing the query and letting Oracle do the work for you?  

DECLARE
  c number;
  v_sqltxt varchar2(1000) := 'select ''test string'' from dual';   hv v$sql.hash_value%type;
BEGIN
  dvms_output.enable(1000000);
  c := dbms_sql.open_cursor
  dbms_sql.parse(c, v_sqltxt, dbms_sql.native);   dbms_sql.close_cursor(c);  

  select hash_value
  into hv
  where sql_text = v_sqltxt;  

  dbms_output.put_line('Hash value ='||hv); END;
/    

anonymous block completed
Hash value =511190436  

NB
- this only works for SQL that fits into SQL_TEXT; you may need to use SQL_FULLTEXT for very long SQL. - the statement has to parse successfully, else DBMS_SQL raises an exception    

HTH   Regards Nigel  

none that I know of. BTW that hash function changed in 10g, since the HV are alphanumeric instead of numeric only.

Raj

--
http://www.freelists.org/webpage/oracle-l
Received on Thu Sep 21 2006 - 07:08:58 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US