Friday, October 14, 2022

There is no BITOR() in Oracle SQL

In Oracle SQL, I can do a bitwise AND of two numbers, but there is no equivalent function to do a bitwise OR.  However, it turns out to be really easy to do using BITAND().
I was manipulating some trace values where each binary digit, or bit, corresponds to a different function (see PeopleSoft DBA Blog: Add Flags to Trace Level Overrides in Process Definitions).  I wanted to ensure certain attributes were set.  So, I wanted to do a bitwise OR between the current flag value and the value of the bits I wanted to set. 
In bitwise OR, if either or both bits are set, then the answer is 1.  It is like addition, but when both the bits are 1, the answer is 1 rather than 2.  I can add the bits up and then deduct BITAND().  Thus:
BITOR 0 1       + 0 1       BITAND 0 1
0 0 1   =   0 0 1   -   0 0 0
1 1 1       1 1 2       1 0 1
Or I could write it as 
BITOR(x,y)  =  x + y - BITAND(x,y)

Here is a simple example with two decimal numbers expressed in binary.  The results of AND and OR operations are below, with their decimal values.

 27 = 00011011
 42 = 00101010

AND = 00001010 = 10
 OR = 00111011 = 59
I can then write a simple SQL expression to calculate this, and perhaps put it into a PL/SQL function thus:
WITH FUNCTION bitor(p1 INTEGER, p2 INTEGER) RETURN INTEGER IS
BEGIN
  RETURN p1+p2-bitand(p1,p2);
END;
SELECT BITAND(27,42) 
,      27+42-BITAND(27,42) 
,      bitor(27,42)
FROM   DUAL
/

BITAND(27,42) 27+42-BITAND(27,42) BITOR(27,42)
------------- ------------------- ------------
           10                  59           59