Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Bitwise AND operator in Oracle PL/SQL

Posted on 2000-04-06
9
Medium Priority
?
6,136 Views
Last Modified: 2012-06-21
Is there any bitwise AND operator in Oracle  (SQL,  PL/SQL)?
If not, what can I do to evaluate if a bit in an integer is set?

Example:
myint = 9 (bitwise = 1001)
How can I check to see if the second rightmost bit is set or not?


Sagres.
0
Comment
Question by:Sagres
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 4

Accepted Solution

by:
syakobson earned 400 total points
ID: 2690867
PL/SQL provides function BITAND(a,b) where a and b are numbers. To check if second rightmost bit is set issue BITAND against your number and number 2. If result is 2, then bit is set:

SQL> begin
  2  if BITAND(&1,2) = 2
  3  then
  4  dbms_output.put_line('Bit is set');
  5  else
  6  dbms_output.put_line('Bit is not set');
  7  end if;
  8  end;
  9  /
Enter value for 1: 9
old   2: if BITAND(&1,2) = 2
new   2: if BITAND(9,2) = 2
Bit is not set

PL/SQL procedure successfully completed.

SQL> /
Enter value for 1: 10
old   2: if BITAND(&1,2) = 2
new   2: if BITAND(10,2) = 2
Bit is set

PL/SQL procedure successfully completed.

SQL>

Solomon Yakobson

0
 
LVL 4

Expert Comment

by:syakobson
ID: 2690892
You can also do it in sql using MOD and TRUNC and POWER functions. To extract N'th bit from a number use:

SELECT TRUNC(MOD(X,POWER(2,N))/2) FROM dual;

where X is the number:

SQL> SELECT TRUNC(MOD(&1,POWER(2,&2))/2) FROM dual
SQL> /
Enter value for 1: 9
Enter value for 2: 2
old   1: SELECT TRUNC(MOD(&1,POWER(2,&2))/2) FROM dual
new   1: SELECT TRUNC(MOD(9,POWER(2,2))/2) FROM dual

TRUNC(MOD(9,POWER(2,2))/2)
--------------------------
                         0

SQL> /
Enter value for 1: 10
Enter value for 2: 2
old   1: SELECT TRUNC(MOD(&1,POWER(2,&2))/2) FROM dual
new   1: SELECT TRUNC(MOD(10,POWER(2,2))/2) FROM dual

TRUNC(MOD(10,POWER(2,2))/2)
---------------------------
                          1

SQL>

Solomon Yakobson.

0
 
LVL 4

Expert Comment

by:dda
ID: 2690902
You can also take a look at UTL_RAW package ($ORACLE_HOME/rdbms/admin/utlraw.sql). It has a number of bit-level functions.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 3

Expert Comment

by:jaramill
ID: 2691089
BITAND looks much simpler than the UTL_RAW.BIT_AND function.  That one requires you to put in the string in bit form "1001".  With BITAND by itself you just put in the number and it works.  Solomon's solution seems simple.

Gio
0
 
LVL 4

Expert Comment

by:syakobson
ID: 2691478
Yes, BITAND is simpler in case you work with numbers. Recently, in one of the previous questions I suggested to use UTL_RAW package since the question was not just bitwise AND but also OR and NOT. I have no clue why Oracle implemented BITAND but not BITOR, BITXOR, BITNOT. Maybe because they can be relatively easy derived from BITAND?

Solomon Yakobson.
0
 
LVL 3

Expert Comment

by:jaramill
ID: 2691487
But I've never heard of BITAND.  I don't even see it in the documentation.  Where can I find documentation for BITAND Solomon?

Thanks,
Gio
0
 
LVL 4

Expert Comment

by:dda
ID: 2692029
jaramill:

Next time try searching the usenet like this:
http://www.deja.com/dnquery.xp?ST=MS&QRY=oracle+sql+bitwise+function&svcclass=dnserver&DBS=2

:)

Dmitry.
0
 

Author Comment

by:Sagres
ID: 2692666
Bitand works fine, it is what we were looking for.
0
 
LVL 4

Expert Comment

by:syakobson
ID: 2693366
BITAND is part of package STANDARD. It a special package, "heart" of PL/SQL if you will. You can find it in $ORACLE_HOME/RDBMS/ADMIN/STANDARD.SQL

Solomon Yakobson.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Suggested Courses

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question