Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Bitwise AND operator in Oracle PL/SQL

Posted on 2000-04-06
9
Medium Priority
?
5,918 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: 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!

 
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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
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.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

715 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