Main Tutorials

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.

  1. Converts the inputs into binary.
  2. Performs a standard bitwise AND operation on these two strings.
  3. 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
Note
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

  1. Binary value 001 (decimal 1) means Request Sent to bank.
  2. Binary value 010 (decimal 2) means Response Received.
  3. 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

References

  1. BITAND Function :- Oracle official docs
  2. Bitwise AND wiki
  3. Binary to Decimal converter

About Author

author image
Dhaval Dadhaniya is a software engineer by profession and reader/writter by passion. He is working in a well reputed MNC as an applications Developer with 5 years of experience. He have good understanding and knowledge of Java, Database, Spring, Hibernate and exploring other technologies related to Software development. Befriend him on Facebook

Comments

Subscribe
Notify of
0 Comments
Inline Feedbacks
View all comments