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)

Back to index of questions


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)


Back to top

Back to index of questions