Re: % Negation Character

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_at_ge.com> wrote:
>
> Hi Mark,
>
> Thank you so m uch for your reply it was really helpful since I'm
> 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_at_il.proquest.com>
> *www.proquest.com* <http://www.proquest.com/>
> *www.csa.com* <http://www.csa.com/>
>
> *ProQuest...*Start here.
>
>
>
> ------------------------------
> *From:* oracle-l-bounce_at_freelists.org [
> mailto:oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org>] *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 ...
>
>
> Do you know or have any clue about this??? I really appreciate the help
>
>
> 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.'

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