Oracle PL/SQL – BITAND function example
The BITAND
function treats its inputs and its output as vectors of bits, the output is the bitwise AND of the inputs.
Basically it performs below steps.
- Converts the inputs into binary.
- Performs a standard bitwise AND operation on these two strings.
- Converts the binary result back into decimal and returns the value.
1. BITAND examples
SELECT BITAND (12,10) FROM DUAL; -- output 8
1 1 0 0 = 12
1 0 1 0 = 10
-------------
1 0 0 0 = 8
SELECT BITAND(24,18) FROM DUAL; -- output 16
1 1 0 0 0 = 12
1 0 0 1 0 = 18
-------------
1 0 0 0 0 = 16
Try this – Binary to Decimal converter
2. BITAND use to calculate transaction status.
2.1 In this example suppose txn_details
table contains transaction details of some payment gateway with different banks. Here, the txn_status
column of txn_details
table contains single numeric value, but having several meaning within each bit of this value.
We considered the txn_status
meanings like
- Binary value 001 (decimal 1) means Request Sent to bank.
- Binary value 010 (decimal 2) means Response Received.
- Binary value 100 (decimal 4) means Error Received In Response.
CREATE TABLE txn_details
(
TXN_ID number(5) primary key,
BANK_NAME varchar2(20),
TXN_STATUS number(1)
);
2.2 Inserting sample values.
INSERT INTO txn_details VALUES (1,'ABC Bank',3);
INSERT INTO txn_details VALUES (2,'National Bank',0);
INSERT INTO txn_details VALUES (3,'Corporation Bank',1);
INSERT INTO txn_details VALUES (4,'ABC Bank',7);
2.3 The example uses the DECODE
function to provide two values for each of the three bits in the txn_status
value, one value if the bit is turned on and one if it is turned off.
For the REQ_SENT
column, BITAND first compares txn_status
with 1 (binary 001). Only significant bit values are compared, so any binary value with a 1 in its rightmost bit will evaluate positively and return 1.
Even numbers will return 0. The DECODE
function compares the value returned by BITAND with 1. If they are both 1, then the value of REQ_SENT
is “YES”, else “NO”.
The other two columns RESPONSE_RECEIVED
and ERROR_IN_RESPONSE
evaluated similarly.
SELECT txn_id,bank_name,txn_status ,
DECODE(BITAND(txn_status, 1), 1, 'YES', 'NO') "REQ_SENT",
DECODE(BITAND(txn_status, 2), 2, 'YES', 'NO') "RESPONSE_RECEIVED",
DECODE(BITAND(txn_status, 4), 4, 'YES', 'NO') "ERROR_IN_RESPONSE"
FROM txn_details;
Output
TXN_ID | BANK_NAME | TXN_STATUS | REQ_SENT | RESPONSE_RECEIVED | ERROR_IN_RESPONSE |
---|---|---|---|---|---|
1 | ABC Bank | 3 | YES | YES | NO |
2 | National Bank | 0 | NO | NO | NO |
3 | Corporation Bank | 1 | YES | NO | NO |
4 | ABC Bank | 7 | YES | YES | YES |