## Question and Answer

## You Asked

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

</code> http://asktom.oracle.com/~tkyte/hexdec/index.html <code>

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.

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

</code> http://asktom.oracle.com/~tkyte/hexdec/index.html <code>

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

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

# Comments

Tom,

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

Thanks,

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

Thanks,

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

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

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!)

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.

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.

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

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

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

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

Sorry, Tom. I didn't realize this was first posted more

than two years ago, and for 8.0.4.

than two years ago, and for 8.0.4.

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

Thanks,

Arun

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)

adding zero helped it believe.

adding zero helped it believe.

"I noticed most, if not examples"

I meant to write,

"I noticed most, if not all, examples"

Sorry guys,

Arun

I meant to write,

"I noticed most, if not all, examples"

Sorry guys,

Arun

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

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

Tom,

Is there a function in Oracle sql to convert decimal representation to binary representation?

Is there a function in Oracle sql to convert decimal representation to binary representation?

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;

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;

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 :)

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.

Tom,

l_hex := "0123456789ABCDEF"

For binary, we need only 0's and 1's. Can you tell the reason behind using l_hex?

l_hex := "0123456789ABCDEF"

For binary, we need only 0's and 1's. Can you tell the reason behind using l_hex?

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

Hi Tom,

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

Regards

Ramki

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

Regards

Ramki

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..

no technical reason..