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