The Oracle (tm) Users' Co-Operative FAQ
How can I perform bitwise operations in Oracle
| Author's name: Connor McDonald Author's Email: connor_mcdonald@yahoo.com |
Date written: June 20th, 2001 Oracle version(s): 8.1.x |
| Finally, Oracle have got around to adding their BITAND function to the documentation, but there is a little more to bitwise operations that is important to consider. (Updated July 15th, 2005) |
Ever since Oracle 7 and probably even previous, Oracle has contained a BITAND function that returns the logical AND of two integers. Somewhere around 8i they finally decided to stick it in the documentation. You can safely rely on this function being retained in future versions of Oracle, because it is used extensively throughout the data dictionary. For example, a column is deemed to be "hidden" if bitand(property, 32) = 0 on the SYS.COL$ table.
Note that BITAND returns a binary integer, so to use it in SQL you will need to convert it to a numeric.
SQL> select bitand(49,54) from dual;
ERROR:
ORA-00932: inconsistent datatypes
SQL> select bitand(49,54)+0 from dual;
BITAND(49,54)+0
---------------
48
Using this function, its trivial to build the other logical operations
function bitor(p_dec1 number, p_dec2 number) return number is begin return p_dec1-bitand(p_dec1,p_dec2)+p_dec2; end; function bitxor(p_dec1 number, p_dec2 number) return number is begin return bitor(p_dec1,p_dec2)-bitand(p_dec1,p_dec2); -- or you could use: return p_dec1-2*bitand(p_dec1,p_dec2)+p_dec2; end;
Note that BITAND only supports positive integers, so if you want to include support for negatives you will need to wrap some of your own code around the above to handle them.
Because many people were unaware of the BITAND builtin, they often sought solace in the UTL_RAW package which looks promising when you describe it.
SQL> desc UTL_RAW FUNCTION BIT_AND RETURNS RAW Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- R1 RAW IN R2 RAW IN FUNCTION BIT_COMPLEMENT RETURNS RAW Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- R RAW IN FUNCTION BIT_OR RETURNS RAW Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- R1 RAW IN R2 RAW IN FUNCTION BIT_XOR RETURNS RAW Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- R1 RAW IN R2 RAW IN
But you need to be careful here, as can be best shown with an example. First we'll use the standard BITAND to see what the real result should be:
SQL> exec dbms_output.put_line(bitand(147,63)); 19 PL/SQL procedure successfully completed.
Now we'll try UTL_RAW to accomplish the same thing
SQL> declare 2 x number := 147; 3 y number := 63; 4 begin 5 dbms_output.put_line(utl_raw.bit_and(x,y)); 6 end; 7 / ERROR at line 1: ORA-06550: line 5, column 24: PLS-00306: wrong number or types of arguments in call to 'BIT_AND' ORA-06550: line 5, column 3: PL/SQL: Statement ignored
This is because the UTL_RAW package (as the name suggests) only works with RAW data. "No problem" we think, we'll just do some datatype conversions...
SQL> declare 2 x number := 147; 3 y number := 63; 4 x_raw raw(4) := utl_raw.cast_to_raw(x); 5 y_raw raw(4) := utl_raw.cast_to_raw(y); 6 begin 7 dbms_output.put_line(utl_raw.bit_and(x_raw,y_raw)); 8 end; 9 / 303037 PL/SQL procedure successfully completed.
So the PL/SQL worked, but the result is not what we were expecting. Then you may click onto the fact that since the result is raw, an additional conversion is needed:
SQL> declare 2 x number := 147; 3 y number := 63; 4 x_raw raw(4) := utl_raw.cast_to_raw(x); 5 y_raw raw(4) := utl_raw.cast_to_raw(y); 6 begin 7 dbms_output.put_line(utl_raw.cast_to_varchar2(utl_raw.bit_and(x_raw,y_raw))); 8 end; 9 / 007 PL/SQL procedure successfully completed.
Still no joy! The reason for this behaviour is that we not taking into account all of the datatype conversions that are taking place. What is really happening is:
The number 147 is converted to varchar2 '147', which as a raw is "31,34,37" (that is, ascii 49, ascii 52, ascii 55). Similarly, 63 becomes raw "36,33" (ascii 54, ascii 51). It is these values that get passed to the BIT_AND function, which gives
49 52 55 AND 54 51 - ================== 48 48 55
which when converted back to varchar2 yields 007.
It is possible to use UTL_RAW, but you just need to take care with all of the conversions. A package to do this is:
create or replace
package bitops2 is
function bitand(p_dec1 number, p_dec2 number) return varchar2 ;
function bitor(p_dec1 number, p_dec2 number) return varchar2 ;
function bitxor(p_dec1 number, p_dec2 number) return varchar2 ;
end;
/
create or replace
package body bitops2 is
function raw_ascii(p_dec number) return raw is
v_result varchar2(1999);
v_tmp1 number := p_dec;
begin
loop
v_result := chr(mod(v_tmp1,256)) || v_result ;
v_tmp1 := trunc(v_tmp1/256);
exit when v_tmp1 = 0;
end loop;
return utl_raw.cast_to_raw(v_result);
end;
function ascii_raw(p_raw varchar2) return number is
v_result number := 0;
begin
for i in 1 .. length(p_raw) loop
v_result := v_result * 256 + ascii(substr(p_raw,i,1));
end loop;
return v_result;
end;
function bitand(p_dec1 number, p_dec2 number) return varchar2 is
begin
return
ascii_raw(
utl_raw.cast_to_varchar2(
utl_raw.bit_and(
raw_ascii(p_dec1),
raw_ascii(p_dec2)
)
)
);
end;
function bitor(p_dec1 number, p_dec2 number) return varchar2 is
begin
return
ascii_raw(
utl_raw.cast_to_varchar2(
utl_raw.bit_or(
raw_ascii(p_dec1),
raw_ascii(p_dec2)
)
)
);
end;
function bitxor(p_dec1 number, p_dec2 number) return varchar2 is
begin
return
ascii_raw(
utl_raw.cast_to_varchar2(
utl_raw.bit_xor(
raw_ascii(p_dec1),
raw_ascii(p_dec2)
)
)
);
end;
end;
/
If your bitwise requirements extend larger than the maximum size of the number datatype, or if you would rather deal with bit strings directly, the following package will assist:
create or replace
package bitops is
function bitand(p_bit1 varchar2, p_bit2 varchar2) return varchar2 ;
function bitor(p_bit1 varchar2, p_bit2 varchar2) return varchar2 ;
function bitxor(p_bit1 varchar2, p_bit2 varchar2) return varchar2 ;
end;
/
create or replace
package body bitops is
function bitand(p_bit1 varchar2, p_bit2 varchar2) return varchar2 is
v_result varchar2(1999);
v_tmp1 varchar2(1999) := p_bit1;
v_tmp2 varchar2(1999) := p_bit2;
v_len1 number := length(p_bit1);
v_len2 number := length(p_bit2);
v_len number := greatest(v_len1,v_len2);
begin
v_tmp1 := lpad(v_tmp1,v_len,'0');
v_tmp2 := lpad(v_tmp2,v_len,'0');
for i in 1 .. v_len loop
v_result := v_result || least(substr(v_tmp1,i,1),substr(v_tmp2,i,1));
end loop;
return v_result;
end;
function bitor(p_bit1 varchar2, p_bit2 varchar2) return varchar2 is
v_result varchar2(1999);
v_tmp1 varchar2(1999) := p_bit1;
v_tmp2 varchar2(1999) := p_bit2;
v_len1 number := length(p_bit1);
v_len2 number := length(p_bit2);
v_len number := greatest(v_len1,v_len2);
begin
v_tmp1 := lpad(v_tmp1,v_len,'0');
v_tmp2 := lpad(v_tmp2,v_len,'0');
for i in 1 .. v_len loop
v_result := v_result || greatest(substr(v_tmp1,i,1),substr(v_tmp2,i,1));
end loop;
return v_result;
end;
function bitxor(p_bit1 varchar2, p_bit2 varchar2) return varchar2 is
v_result varchar2(1999);
v_tmp1 varchar2(1999) := p_bit1;
v_tmp2 varchar2(1999) := p_bit2;
v_len1 number := length(p_bit1);
v_len2 number := length(p_bit2);
v_len number := greatest(v_len1,v_len2);
begin
v_tmp1 := lpad(v_tmp1,v_len,'0');
v_tmp2 := lpad(v_tmp2,v_len,'0');
for i in 1 .. v_len loop
v_result := v_result || to_char(abs(to_number(substr(v_tmp1,i,1))-to_number(substr(v_tmp2,i,1))));
end loop;
return v_result;
end;
end;
/
SQL> declare
2 x varchar2(20) := '10101010101010101010';
3 y varchar2(20) := '11110000111100001111';
4 begin
5 dbms_output.put_line(bitops.bitand(x,y));
6 end;
7 /
10100000101000001010
PL/SQL procedure successfully completed.
Addenda
Bart Pots has extended the package to handle bit strings of differing length and provided numeric input and output. The code is below:
create or replace package bitops2 is function raw_ascii(p_dec number) return raw; function ascii_raw(p_raw varchar2) return number; function bitand(p_dec1 number, p_dec2 number) return number; function bitor(p_dec1 number, p_dec2 number) return number; function bitxor(p_dec1 number, p_dec2 number) return number; end; / create or replace package body bitops2 is function raw_ascii(p_dec number) return raw is v_result varchar2(1999); v_tmp1 number := p_dec; begin loop v_result := chr(mod(v_tmp1,256)) || v_result ; v_tmp1 := trunc(v_tmp1/256); exit when v_tmp1 = 0; end loop; return utl_raw.cast_to_raw(v_result); end; function ascii_raw(p_raw varchar2) return number is v_result number := 0; begin for i in 1 .. length(p_raw) loop v_result := v_result * 256 + ascii(substr(p_raw,i,1)); end loop; return v_result; end; function bitand(p_dec1 number, p_dec2 number) return number is v_tmp_raw1 raw(1999); v_tmp_raw2 raw(1999); nr_diff_length number(4,0); nr_pos number(4,0); v_tmp_raw_zero raw(1999) := raw_ascii(0); begin v_tmp_raw1 := raw_ascii(p_dec1); v_tmp_raw2 := raw_ascii(p_dec2); nr_diff_length := greatest( utl_raw.length(v_tmp_raw1), utl_raw.length(v_tmp_raw2)); for nr_pos in utl_raw.length(v_tmp_raw1) + 1.. nr_diff_length loop v_tmp_raw1 := utl_raw.concat( v_tmp_raw_zero,v_tmp_raw1 ); end loop; for nr_pos in utl_raw.length(v_tmp_raw2) + 1 .. nr_diff_length loop v_tmp_raw2 := utl_raw.concat( v_tmp_raw_zero,v_tmp_raw2 ); end loop; return ascii_raw( utl_raw.cast_to_varchar2( utl_raw.bit_and( v_tmp_raw1, v_tmp_raw2))); end; function bitor(p_dec1 number, p_dec2 number) return number is v_tmp_raw1 raw(1999); v_tmp_raw2 raw(1999); nr_diff_length number(4,0); nr_pos number(4,0); v_tmp_raw_zero raw(1999) := raw_ascii(0); begin v_tmp_raw1 := raw_ascii(p_dec1); v_tmp_raw2 := raw_ascii(p_dec2); nr_diff_length := greatest( utl_raw.length(v_tmp_raw1), utl_raw.length(v_tmp_raw2)); for nr_pos in utl_raw.length(v_tmp_raw1) + 1.. nr_diff_length loop v_tmp_raw1 := utl_raw.concat( v_tmp_raw_zero,v_tmp_raw1 ); end loop; for nr_pos in utl_raw.length(v_tmp_raw2) + 1 .. nr_diff_length loop v_tmp_raw2 := utl_raw.concat( v_tmp_raw_zero,v_tmp_raw2 ); end loop; return ascii_raw( utl_raw.cast_to_varchar2( utl_raw.bit_or( v_tmp_raw1, v_tmp_raw2))); end; function bitxor(p_dec1 number, p_dec2 number) return number is v_tmp_raw1 raw(1999); v_tmp_raw2 raw(1999); nr_diff_length number(4,0); nr_pos number(4,0); v_tmp_raw_zero raw(1999) := raw_ascii(0); begin v_tmp_raw1 := raw_ascii(p_dec1); v_tmp_raw2 := raw_ascii(p_dec2); nr_diff_length := greatest( utl_raw.length(v_tmp_raw1), utl_raw.length(v_tmp_raw2)); for nr_pos in utl_raw.length(v_tmp_raw1) + 1.. nr_diff_length loop v_tmp_raw1 := utl_raw.concat( v_tmp_raw_zero,v_tmp_raw1 ); end loop; for nr_pos in utl_raw.length(v_tmp_raw2) + 1 .. nr_diff_length loop v_tmp_raw2 := utl_raw.concat( v_tmp_raw_zero,v_tmp_raw2 ); end loop; return ascii_raw( utl_raw.cast_to_varchar2( utl_raw.bit_xor( v_tmp_raw1, v_tmp_raw2))); end; end;
Further reading: Technet documentation on BITAND (may require OTN registration)
