RE: Computing Hash value

Hi guys,
I checked it and it's great !!!
Thanks a lot.

Most appreciated,

Adi

-----Original Message-----
From: Nigel Thomas [mailto:nigel_at_preferisco.com] Sent: 21 September 2006 14:09
To: rjamya_at_gmail.com; adi_at_zetapoint.com
Cc: oracle-l_at_freelists.org
Subject: Re: Computing Hash value

> 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  

• Original Message ---- From: rjamya <rjamya_at_gmail.com> To: adi_at_zetapoint.com Cc: oracle-l_at_freelists.org Sent: Thursday, September 21, 2006 12:29:56 PM Subject: Re: Hash value

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