Hashing strings in Oracle 8i,9i,10g

Posted by David Yahalom under Oracle

There’s an easy way to hash strings in Oracle 8i,9i,10g.

In Oracle 10g you can use the ORA_HASH function which computes a hash value for any given expression. It recieves three arguments:

• expr - determines the data for which you want Oracle Database to compute a hash value. You can go wild on the length of this string there isn’t any restriction on this.
• max_bucket (optional) - determines the maximum bucket value returned by the hash function. You can specify any value between 0 and 4294967295. The default is 4294967295.
• seed_value argument (optional) - enables Oracle to produce many different results for the same set of data. Oracle applies the hash function to the combination of expr and seed_value. You can specify any value between 0 and 4294967295. The default is 0.

SQL> select ora_hash('hello world') from dual;

ORA_HASH('HELLOWORLD')
----------------------
1896528268

SQL>

In Oralce 8i and 9i there is not ORA_HASH function. What we can use instead is the DBMS_UTILITY.GET_HASH_VALUE function. It is similar yet different from ORA_HASH .

The GET_HASH_VALUE function takes three mandatory parameters:

• name - The string we want converted from string to integer via hash.
• base - The base value for the integer used in the hashing algorithm.
• hash_size - This is the size of the hash table. The total number of values that are available to the hashing algorithm as conversions from the string inputs. It is recommended that this will be the power of two.

For example, to get a hash value on a string where the hash value should be between 1000 and 3047, use 1000 as the base value and 2048 as the hash_size value.

SQL> select DBMS_UTILITY.GET_HASH_VALUE('hello world1', 2,1048576) from dual;

DBMS_UTILITY.GET_HASH_VALUE('HELLOWORLD1',2,1048576)
----------------------------------------------------
113894

SQL>

Started a new job!

Posted by David Yahalom under General IT

Pop up the champagne bottles people!

I have started a new job as a Senior Project Manager at the Veracity Group , the leading Oracle Consulting group in Israel. Veracity is the only Oracle certified Value Added Distributor (VAD) in Israel and part of a very small and exclusive list of Oracle VADs around the world. Our customers benefit from the unique advantage we offer of both directly licensing and selling Oracle products as well as having an outstanding expert Professional Services team. We’re Simply the best in our field, trust me. As part of my new job at Veracity, I’m also holding the CTO title of a very special group in the Oracle Database development business, but more on this later.

I’ve been able to accomplish a great deal in the last six years as a database consultant, especially during my time at Xpert-One1 which was a very rewarding experience. For some time now I’ve been looking into getting a more managerial position and when the opportunity presented itself, I simply couldn’t resist. My new position puts me directly in the middle decision making process about all things technological - so I think this would make a great learning experience for me. I’m still (and always will be) a bits and bytes kind of guy so I’m looking forward combining my deep passion for technology with having a managerial position. Any tips on how to exactly accomplish this from all you veterans out there are welcome!