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,
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
