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@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?
 
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@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.

Raj


--
http://www.freelists.org/webpage/oracle-l


Other related posts: