• Questions
• # Built in Functions -- bitand

Thanks for the question, Krishna.

Asked: June 30, 2000 - 1:51 am UTC

Last updated: March 07, 2013 - 9:00 am UTC

Version: 8.0.4

Viewed 10K+ times! This question is

What is the significance of the 'bitand' function used
in the definition of the v_\$ dynamic performance views.

## and Tom said...

Its used in lots of the views (ALL_*, DBA_*, USER_*) as well as the v\$. bitand is undocumented but used internally alot.

Bitand() is a bit wise manipulator of 32bit signed integers. We can use it to see if a specific "bit" in the signed integer is ON or OFF. For example (uses some base conversion functions I have at
to show binary representations)

ops\$tkyte@DEV8I.WORLD> select to_bin(1234) bin,
2 bitand(1234,1)+0 bit1,
3 bitand(1234,2)+0 bit2,
4 bitand(1234,4)+0 bit3
5 from dual
6 /

BIN BIT1 BIT2 BIT3
------------ ---------- ---------- ----------
10011010010 0 2 0

ops\$tkyte@DEV8I.WORLD>

that shows that bitand could tell us if a given bit (2^n) is on or off. If its ON, the the return will be >0, if its off -- it'll be zero.

## Rating

(12 ratings)

Is this answer out of date? If it is, please let us know via a Comment

A reader, July 15, 2002 - 8:36 am UTC

Tom,

I am confused with bitand function. Can you explain in really simple words? Where it can be used?

Thanks,

July 15, 2002 - 9:03 am UTC

### Undocumented?

Mark J. Bobak, July 15, 2002 - 11:52 am UTC

Um, Tom, first you say it's undocumented, and then you give
a pointer to where it's documented? Looks to me like it's
been documented since at least 8.1.7, though I'm aware it
existed well before that, 8.0, probably even 7.3....?

-Mark

July 15, 2002 - 12:41 pm UTC

It was documented starting in 817 -- sorry about that. (answered the question for 804 -- it gets a little hairy with all of the changes/additions in the releases!)

### is utl_raw.bitand the same as the bitand discussed here..

pasko, July 16, 2002 - 3:39 am UTC

Hi Tom

I am trying to get the 5 LSB ( least significant bits) from each Byte of a RAW value.

Is it OK if i use :

utl_raw.BIT_AND( my_raw_value , raw_1F )

given that my raw_1F is defined as
raw(16) := HEXTORAW('1F1F1F1F1F1F1F1F1F1F1F1F1F1F1F1F') ;

Could i also use the other bitand?

Thanks.

July 16, 2002 - 7:32 am UTC

You cannot use the other bitand -- it works on 32 bit signed integers. Your raw string (first of all it is raw, not number so that wouldn't work) appears much larger then that.

You can use utl_raw. You could use:

utl_raw.bit_and( my_raw_value, hextoraw( rpad('1F',length(my_raw_value),'1F') ) );

that'll ensure that the mask 1f is as long as it needs be and no longer. when we use length on my_raw_value, my_raw_value will be turned into hex first so we'll get the right number of 1F's

### the Length trick is Great...

Pasko, July 16, 2002 - 8:55 am UTC

Hi Tom,
The length trick is Great !
Actually i'm getting the my_raw_value from
the return value of
dbms_obfuscation_toolkit.md5 using the seed as
sys_guid() and to_char(sysdate) and some other varchar2 value.

That way i can translate each byte to of the raw value to only 5 LSB's.

I think dbms_obfuscation_toolkit.md5 returns only 16 byte RAWs , right?

How could i use mod( my_raw , 32 ) , i guess that would achieve the same result.

Thanks

### Whoops...

Mark J. Bobak, July 17, 2002 - 3:40 am UTC

Sorry, Tom. I didn't realize this was first posted more
than two years ago, and for 8.0.4.

### Pardon the stupid question

Arun Mathur, January 12, 2005 - 3:10 pm UTC

I noticed most, if not examples, of bitand functions add 0 to the result of a bitand operation. Would you mind helping me understand the reasons behind doing this, and out of curiosity, what happens if someone doesn't add a 0 to the bitand result?

Thanks,
Arun

January 12, 2005 - 4:03 pm UTC

some versions of sqlplus refused to believe bitand was a number (before it was a documented function, it is relatively "new" to have bitand accessible to us)

### Correction

Arun Mathur, January 12, 2005 - 3:11 pm UTC

"I noticed most, if not examples"

I meant to write,

"I noticed most, if not all, examples"

Sorry guys,
Arun

Arun Mathur, January 12, 2005 - 4:53 pm UTC

I'm using the bitand function as part of my FGAC predicates. For a given table, I use the same predicate function for all dml statements.

For some reason, I would get a check violation when attempting to insert a row into a certain table, even though I can update with the same values. However, once I modified the predicate by adding 0 to the bitand result, I could insert as well as update. But, I'm nowhere near out of the water because I created a sample test table hoping I can reproduce the insert check violation error and post it, but the sample test table worked fine for both inserts and updates. Good stuff in any case :)

Cheers,
Arun

Reader, February 23, 2010 - 5:14 pm UTC

Tom,
Is there a function in Oracle sql to convert decimal representation to binary representation?
March 01, 2010 - 8:35 am UTC

Reader, March 01, 2010 - 2:19 pm UTC

Tom,

In the to_base function, can you explain why you are doing?

l_str := substr( l_hex, mod(l_num,p_base)+1, 1 ) || l_str;
March 02, 2010 - 6:57 am UTC

it is a simple "get the remainder after integer division by the base and 'shift' sort of routine"

Take a number like 9 - convert to base two. The first step would be to find the remainder after division by 2 (remainder = 1). That is the first bit. Now "shift" that portion off (trunc(l_num/p_base) does that). We are left with the number 4. What is the remainder of after division by 2 (remainder = 0), that is the second bit. Now shift again (divide by base) and we have l_num=2. Remainder after division = 0, that is the third bit. Shift, we have 1. remainder = 1, that is the fourth bit - we are left with zero. Hence we are done... so

1001 is binary for decimal 9.

Put in some debug and see what it does next time :)

```ops\$tkyte%ORA9IR2> create or replace function to_base( p_dec in number, p_base in number )
2  return varchar2
3  is
4   l_str varchar2(255) default NULL;
5   l_num number default p_dec;
6   l_hex varchar2(16) default '0123456789ABCDEF';
7  begin
8   if ( p_dec is null or p_base is null )
9   then
10    return null;
11   end if;
12   if ( trunc(p_dec) <> p_dec OR p_dec < 0 ) then
13    raise PROGRAM_ERROR;
14   end if;
15
16
17   dbms_output.put_line( 'inputs: ' );
18   dbms_output.put_line( 'l_hex := "' || l_hex || '"' );
19   dbms_output.put_line( 'p_base:= "' || p_base || '"' );
20   dbms_output.put_line( 'l_num := "' || l_num || '"' );
21   loop
22    dbms_output.put_line
23    ( 'looping, l_num := ' || l_num ||
24      ', mod(l_num,p_base)+1 = ' || (mod(l_num,p_base)+1) ||
25      ', substr(l_hex, mod(l_num,p_base)+1, 1 ) = "' || substr(l_hex, mod(l_num,p_base)+1, 1 ) || '"' ||
26      ', l_str = "' || l_str || '"' );
27    l_str := substr( l_hex, mod(l_num,p_base)+1, 1 ) || l_str;
28    l_num := trunc( l_num/p_base );
29    exit when ( l_num = 0 );
30   end loop;
31
32
33   return l_str;
34  end to_base;
35  /

Function created.

ops\$tkyte%ORA9IR2>
ops\$tkyte%ORA9IR2> exec dbms_output.put_line( to_base( 9, 2 ) );
inputs:
l_hex := "0123456789ABCDEF"
p_base:= "2"
l_num := "9"
looping, l_num := 9, mod(l_num,p_base)+1 = 2, substr(l_hex, mod(l_num,p_base)+1, 1 ) = "1", l_str = ""
looping, l_num := 4, mod(l_num,p_base)+1 = 1, substr(l_hex, mod(l_num,p_base)+1, 1 ) = "0", l_str = "1"
looping, l_num := 2, mod(l_num,p_base)+1 = 1, substr(l_hex, mod(l_num,p_base)+1, 1 ) = "0", l_str = "01"
looping, l_num := 1, mod(l_num,p_base)+1 = 2, substr(l_hex, mod(l_num,p_base)+1, 1 ) = "1", l_str = "001"
1001

PL/SQL procedure successfully completed.
```

Reader, March 02, 2010 - 9:30 am UTC

Tom,

l_hex := "0123456789ABCDEF"

For binary, we need only 0's and 1's. Can you tell the reason behind using l_hex?
March 02, 2010 - 12:43 pm UTC

because the function is "to_base" and handles any base from 2 to 16.

### num_to_bin ( )

A reader, March 07, 2013 - 5:54 am UTC

Hi Tom,

Is there is any technical reason that oracle is not having predefined function like num_to_bin ( )
Regards
Ramki

March 07, 2013 - 9:00 am UTC

we don't have a function for everything, that is why we can write our own plsql functions to fill in any gaps.

no technical reason..