How to use Oracle bitwise operators &, |, ^, ~?

Question

I need to use Oracle bitwise operation in my project. I know there is the BITAND() available for the purpose. As far as I know BITAND() is the only such operator and other operators such as bitwise OR operator can be derived from BITAND().

I also found that Oracle has another set of bitwise operators:

AND   &
OR    |
XOR   ^
NOT   ~

I thought to myself: why bother to derive the other operators from BITAND() instead of using this set of operators which are already available? The problem is that, by searching for examples, it is found that all the tech documents provide only the concepts, such as

15 & 9 yields 9 (1111 & 1001 = 1001)
15 | 9 yields 15 (1111 | 1001 = 1111)
15 ^ 9 yields 6 (1111 ^ 1001 = 0110)

but I haven't been able to find a single example of how these operators are used. I tried myself:

select 15 & 9 from dual;

only to find that & is not even recognized as a bitwise operator. Instead, I got prompted to provide a value for the &9 variable.

My questions are:

Can the &, |, ^, ~ operators be used in place of BITAND()?

If yes, how?

If not, why?

If the &, |, ^, ~ operators are for some different purposes, what are they used for and how are they used?

Answer 1

As far as I know, most of Oracle's bitwise functions are in UTL_RAW, where they're intended to be used for RAW datatypes. This is a bit cumbersome, but you could write your own wrapper functions pretty easily.

declare
  x1 raw(10) := utl_raw.cast_from_binary_integer(15);
  x2 raw(10) := utl_raw.cast_from_binary_integer(9);
  raw_result raw(10);
  result number;
begin
  raw_result := utl_raw.bit_and(x1,x2);
  result := utl_raw.cast_to_binary_integer(raw_result);
  dbms_output.put_line('bit_and: ' || result);

  raw_result := utl_raw.bit_or(x1,x2);
  result := utl_raw.cast_to_binary_integer(raw_result);
  dbms_output.put_line('bit_or: ' || result);

  raw_result := utl_raw.bit_xor(x1,x2);
  result := utl_raw.cast_to_binary_integer(raw_result);
  dbms_output.put_line('bit_xor: ' || result);
end;
/

Answer 2

Sounds like kfinity's answer is the way to go, but for the sake of argument, this Ask Tom article shows examples for all but bitnot. Perhaps it could help someone.

select bitand(15,9) bitand,
(15 + 9) - bitand(15,9) bitor,
(15 + 9) - BitAND(15,9) * 2 bitxor 
from dual;

    BITAND      BITOR     BITXOR
---------- ---------- ----------
         9         15          6
1 row selected.

I'd love to see a way to do bitnot.

Answer 3

SQL> select BitAnd(15, 9) from dual;
BITAND(15,9)
------------
           9
SQL>

The list of Oracle's Bit/Binary Functions is here: https://docs.oracle.com/cd/E41183_01/DR/Bit_Binary_Functions.html