# Re: % Negation Character

• From: "Andrew Kerber" <andrew.kerber@xxxxxxxxx>
• To: citlali.guerrero@xxxxxx
• Date: Mon, 18 Jun 2007 14:40:27 -0500

```As an aside, I am really curious why you would want to do bitwise functions
against columns in an Oracle database?  Its not like  you're working in
assembler code where the functionality is needed.

On 6/18/07, Guerrero, Citlali (GE, Corporate, consultant) <
citlali.guerrero@xxxxxx> wrote:
```
```
Hi Mark,

starting on Oracle =) also many thanks to you Nigel your reply was also very
helpful and clear to me =), thank you all for your help =) , if any of you
had questions about Perl I can help and I hope help also with Oracle I'll do
my best.

Thank you¡¡¡¡¡¡¡

Warn Regards, Citlali

Bobak, Mark escribió:

Hi Citlali,

Unforetunately, AskTom, which has some great examples, seems to be down at
the moment.  However, I don't think you want to use the UTL_RAW stuff.

There is the bitand() function, which is built-in to Oracle, and which is
now documented, since 9i, I think.

From that, you can build a simple bitor():

CREATE OR replace FUNCTION bitor( x IN NUMBER, y IN NUMBER ) RETURN NUMBER  AS
BEGIN
RETURN x + y - bitand(x,y);
END;
/

And, once you have both bitand() and bitor(), you can build a bitxor():

CREATE OR replace FUNCTION bitxor( x IN NUMBER, y IN NUMBER ) RETURN NUMBER  AS
BEGIN
RETURN bitor(x,y) - bitand(x,y);
END;
/

Finally, for One's complement, you can xor the number with all 1's.

Here's the code for bitcomplement():

create or replace function bitcomplement(x in number, y in number) return
number as
begin
return bitxor(x,power(2,y)-1);
end;

/

So, you can do something like:

SQL> select bitcomplement(2,4) from dual;

BITCOMPLEMENT(2,4)
------------------
13

Note that the second argument to bitcomplement is how "wide" the bitfield
should be.  In this case, it's 4, if you're expecting 13.

Note that the value of the one's complement will depend on how many 1's you're
xoring against.

Finally, thanks to Connor McDonald for the bitor() and bitxor() functions.  The
bitcomplement() function is my own creation.

-Mark

*--
Mark J. Bobak*
*Senior Database Administrator, System & Product Technologies*
ProQuest
789 E. Eisenhower, Parkway, P.O. Box 1346
Ann Arbor MI 48106-1346
734.997.4059  or 800.521.0600 x 4059
*mark.bobak**@il.proquest.com* <mark.bobak@xxxxxxxxxxxxxxx>
*www.proquest.com* <http://www.proquest.com/>
*www.csa.com* <http://www.csa.com/>

*ProQuest...*Start here.

------------------------------
*From:* oracle-l-bounce@xxxxxxxxxxxxx [
mailto:oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx>] *On
Behalf Of *Guerrero, Citlali (GE, Corporate, consultant)
*Sent:* Monday, June 18, 2007 11:07 AM
*To:* Nigel Thomas
*Cc:* oracle-l
*Subject:* Re: % Negation Character

Hi Nigel,

Thanks for the tip, however, as you mention on your email, I'm facing
some troubles with the data types,  I mean if  I perform:

raw_value := utl_raw.cast_from_number('2'); -- This is equal to
c103

and then I try to get the complement or negative value with this:

select select utl_raw.bit_COMPLEMENT('c103') from dual;
I get the value:

UTL_RAW.BIT_COMPLEMENT('C103')
---------------------------------
3efc

And when I try to perform this: SELECT utl_raw.cast_to_number('3efc')
FROM dual
I got the result:

UTL_RAW.CAST_TO_NUMBER('3EFC')
---------------------------------
-105

What I'm expecting is: 13 ...

Regards Citlali
Nigel Thomas escribió:

Citali
>    I'm migrating from Sybase to Oracle, on Sybase I had a function with
>    update XYZ
>    set flag = flag &~4589
You can use BITAND(arg1, arg2) - see the SQL reference for more
information. Shame there's no BITOR to go with it... See also UTL_RAW
package and functions BIT_AND, BIT_OR, BIT_XOR and BIT_COMPLEMENT. You just
need to be careful with datatypes, I seem to remember.

I played with UTL_RAW quite recently, but on a previous site so I don't
have it to hand, sorry.

HTH

Regards Nigel

-- http://www.freelists.org/webpage/oracle-l

-- http://www.freelists.org/webpage/oracle-l
```
```

--
Andrew W. Kerber

'If at first you dont succeed, dont take up skydiving.'
```