UTL_RAW.BIT_AND Attaches Extra 0 After AND Operation in Oracle SQL

oracle

For a given binary number, say length longer than 128, I want to check whether a particular bit is on or off (say 30th bit) I am using utl_raw.bit_and function to do and between 2 binary numbers. I expect the input binary number to be longer than 128 and I can't use oracle bitand function then. When I do a bit_and, I see extra 0 appended to the result. I am not sure how it got in there when the length of both the input are the same. Here is my sample sql

SELECT 
A, 
LENGTH(A) LENGTH_A, 
B, 
LENGTH(B) LENGTH_B, 
UTL_RAW.BIT_AND(TRIM(A),TRIM(B)) AND_AB, 
LENGTH(UTL_RAW.BIT_AND(TRIM(A), TRIM(B))) LENGTH_AND_AB FROM (
SELECT 
    TRIM(RPAD('0', LENGTH('1111111111111100000000000000011') - 30    ,'0')) || '1' ||TRIM(RPAD('0', 29, '0')) A,  
'1111111111111100000000000000011' B FROM DUAL);

This is the result of the operation

A                                 LENGTH_A  B                               LENGTH_B  AND_AB                           LENGTH_AND_B
0100000000000000000000000000000   31        1111111111111100000000000000011 31        00100000000000000000000000000000 32

Best Answer

That is not handled as binary, but hexadecimal.

select
          length( '0100000000000000000000000000000') as l_a_in_chars,
  utl_raw.length( '0100000000000000000000000000000') as l_a_in_bytes,
          length( '1111111111111100000000000000011') as l_b_in_chars,
  utl_raw.length( '1111111111111100000000000000011') as l_b_n_bytes,
          length('00100000000000000000000000000000') as l_ab_in_chars,
  utl_raw.length('00100000000000000000000000000000') as l_ab_in_bytes
from dual;

L_A_IN_CHARS L_A_IN_BYTES L_B_IN_CHARS L_B_N_BYTES L_AB_IN_CHARS L_AB_IN_BYTES
------------ ------------ ------------ ----------- ------------- -------------
          31           16           31          16            32            16

It is not the length in characters that matters, but the length in bytes, and that was the same the whole time. You can not have hexadecimal with odd number of characters. It is not the output that has an extra 0 attached, but your input, on the automatic conversion from hex to raw:

select hextoraw('0100000000000000000000000000000') from dual;

HEXTORAW('0100000000000000000000
--------------------------------
00100000000000000000000000000000

Your input does not fit into RAW(15), requires RAW(16) where the required 0-s are added as required, and there is no such thing as RAW(15.5):

SQL> select cast ('0100000000000000000000000000000' as raw(16)) from dual;

CAST('01000000000000000000000000
--------------------------------
00100000000000000000000000000000

SQL> select cast ('0100000000000000000000000000000' as raw(15)) from dual;
select cast ('0100000000000000000000000000000' as raw(15)) from dual
             *
ERROR at line 1:
ORA-25137: Data value out of range

Since you seem to store your numbers as strings, you can quite easily check the position of 1 "bit":

SQL> select substr('1111111111111100000000000000011', -30, 1) from dual;

S
-
1
Related Question