RE: % Negation Character

  • From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>
  • To: <citlali.guerrero@xxxxxx>, "Nigel Thomas" <nigel_cl_thomas@xxxxxxxxx>
  • Date: Mon, 18 Jun 2007 14:20:03 -0400

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@xxxxxxxxxxxxxxx <mailto: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] 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
        
         

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

Other related posts: