RE: Computing Hash value

  • From: Adi Hirschtein <adi@xxxxxxxxxxxxx>
  • To: 'Nigel Thomas' <nigel@xxxxxxxxxxxxxx>, rjamya@xxxxxxxxx
  • Date: Thu, 21 Sep 2006 16:50:38 +0200

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

Most appreciated,


-----Original Message-----
From: Nigel Thomas [mailto:nigel@xxxxxxxxxxxxxx] 
Sent: 21 September 2006 14:09
To: rjamya@xxxxxxxxx; adi@xxxxxxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
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?
  c number;
  v_sqltxt varchar2(1000) := 'select ''test string'' from dual';
  hv v$sql.hash_value%type;
  c := dbms_sql.open_cursor
  dbms_sql.parse(c, v_sqltxt, dbms_sql.native);
  select hash_value
  into    hv
  where sql_text = v_sqltxt;
  dbms_output.put_line('Hash value ='||hv);
anonymous block completed
Hash value =511190436
- 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
Regards Nigel
----- Original Message ----
From: rjamya <rjamya@xxxxxxxxx>
To: adi@xxxxxxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
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.



Other related posts: