Re: Bitwise Comparisions in SQL or PL/SQL

From: Mark D Powell <markp7832_at_my-deja.com>
Date: Tue, 29 Aug 2000 13:41:40 GMT
Message-ID: <8ogeig$6jg$1@nnrp1.deja.com>

In article <967553482.6705.1.pluto.d4ee154e_at_news.demon.nl>,   "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote:
> Please voice your complaints, justified or not, with Oracle, by
 logging an
> ITAR.
> Your remarks will not heard by them using this forum.
>
> Regards,
>
> Sybrand Bakker, Oracle DBA
>
> "Marcin Buchwald" <velvet_at_gazeta.pl> wrote in message
> news:39AB9EC7.CFD32E60_at_gazeta.pl...
> >
> >
> > unfortunetly we have undocumented bit_and
> > function but we havn't bit_or...
> >
> > I am combating with the same problem.
> > My suggestions are
> > 1. use external functions (c++ or java)
> > 2. use ... "divide by 2" logic having in mind the
> > difference between number and pls_numeric
> > (oracle library vs host computations)
> >
> > I am not satisfied with this suggestions!
> > I want bitwise operation in syb/ms fasion in Oracle!!!
>
>

I am sure I pulled this off this newsgroup sometime in the past but I do not have the original author's name or the date stored as I normally do. I do know Thomas Kyte has posted on this subject so this may be his. I am not sure I have tested this, but after a quick glance it does appear correct.

CREATE OR replace FUNCTION bitor( x IN NUMBER, y IN NUMBER )  RETURN NUMBER AS

   l_x PLS_INTEGER    DEFAULT x;
   l_y PLS_INTEGER    DEFAULT y;
   l_r PLS_INTEGER    DEFAULT 0;
   l_tmp PLS_INTEGER := 1;

BEGIN
FOR i IN 1 .. 32 LOOP

  IF ( bitand(l_x,l_tmp) = l_tmp OR
       bitand(l_y,l_tmp) = l_tmp ) THEN
       l_r := l_r + l_tmp;

  END IF;
  l_tmp := l_tmp * 2;
  exit when ( l_tmp >= l_x AND l_tmp >= l_y ); END LOOP; RETURN l_r;
END;
/

--
Mark D. Powell  -- The only advice that counts is the advice that
 you follow so follow your own advice --


Sent via Deja.com http://www.deja.com/
Before you buy.

Received on Tue Aug 29 2000 - 08:41:40 CDT