sql bit oracle,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 00 = 16

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

References