Liron Amitzi Oracle ACE

Oded Raz Oracle ACE Director

Bitwise Operators in Oracle

Posted by Liron Amitzi on Jun 10th, 2010 and filed under Oracle, Tips & Tricks. You can follow any responses to this entry through the RSS 2.0. You can leave a response or trackback to this entry

Working with bit size variables and bitwise operators is quite common in programming, but less common in databases. In Oracle, there is not even a bit datatype. But why do we need using bits in Oracle?

Let’s take an example where bit can be very useful. An example can be restaurant website, where we need to keep information about the type of food in the restaurants. There are several ways to keep this information (column for each type and 0 or 1 for true and false, or a separate one-to-many table that contains a rows which are restaurant id and food type couple), but we can also use bits to do that. We can decide that Italian food will be the first bit, steakhouse will be the second, pizza is the third and so on. From that we can generate a number that matches the food types in the restaurant. For example, with the type we mentioned, a Italian restaurant that has also pizza will be 101 in binary which is the number 5. A steak house will be 010 which is 2.

This solution requires a very little storage, and it is very easy to handle in the application.

The only problem is that we may need bitwise operators in Oracle for some queries. Oracle supplies the BITAND function, but does not supply any other bitwise functions. We will have to create these for ourselves:

BITOR

create or replace function bitor (x number, y number)
return number
is
begin
   return (x+y)-bitand(x,y);
end;

BITXOR

<pre>create or replace function bitxor (x number, y number)
return  number
is
begin
   return bitor(x,y)-bitand(x,y);
end;

Leave a Reply

 
Log in / Advanced NewsPaper by Gabfire Themes