I realized the other day how much I depend on Google to help find quick answers, and how much information is actually out on the web. I also realized that I don’t contribute much, so I’m hoping to change that.

I needed to check bitmasks against an Oracle DB the other day. My bitmask was in hex (base 16 for the uninitiated), and the Oracle DB’s column was in decimal, so I first needed to convert the hex into decimal, and then do a bit AND operation.

For this example, the table is “TABLE1” and the column is “BITMASK”. Let’s say that we’re looking for all rows where bit 5 is set, which is 0x10, or decimal 16. (Yes the conversion is simple in this example, but not when you’re looking for bit 78). In that case the query looks like:

SELECT * FROM TABLE1 WHERE BITAND( TO_NUMBER(’10’, ‘XX’), BITMASK) = 1;

Let’s pull that apart. BITAND(val1, val2) is a function that does a bit AND between val1 and val2, similar to the “val1 & val2” operation in Java and C/C++. The TO_NUMBER() function converts the string “10” from hex to decimal. Then we look for rows where the BITAND() method returns 1, and presto, we have our bit AND operation against Oracle.

Credits:
Oramoss Blog
Oracle Users’s Coop