?
Solved

Using utl_raw package wrapped in Connor McDonald package to do bitand operations on numbers, but returning the wrong answer.

Posted on 2003-03-10
3
Medium Priority
?
783 Views
Last Modified: 2013-12-12
Folks,

I'm using the following code to perform a bitand of two numbers.


FUNCTION raw_ascii(p_dec number) RETURN raw
IS

  v_result varchar2(1999);
  v_tmp1   number := p_dec;

BEGIN

  LOOP
    v_result := CHR(MOD(v_tmp1,256)) || v_result ;
    v_tmp1 := TRUNC(v_tmp1/256);
    exit when v_tmp1 = 0;
  END LOOP;
   
  RETURN utl_raw.cast_to_raw(v_result);

END;


FUNCTION ascii_raw(p_raw varchar2) RETURN number
IS
  v_result number := 0;

BEGIN
 
  FOR i in 1 .. LENGTH(p_raw)
  LOOP
    v_result := v_result * 256 + ASCII(SUBSTR(p_raw,i,1));
  END LOOP;
  RETURN v_result;

END;

FUNCTION bitand(p_dec1 number, p_dec2 number) RETURN varchar2
IS

BEGIN
   
   dbms_output.put_line('pdec1 ' || p_dec1);
   dbms_output.put_line('pdec2 ' || p_dec2);

   RETURN
   ascii_raw(
     utl_raw.cast_to_varchar2(
       utl_raw.bit_and(
         raw_ascii(p_dec1),
         raw_ascii(p_dec2)
       )
     )
   );
END;



FUNCTION Determine_TLQ(det IN NUMBER) RETURN BOOLEAN
IS

Day_Mean      CONSTANT NUMBER:=72;

tlq_mask_bin    CONSTANT VARCHAR2(32):='11000010000000000000000000000001';
tlq_mask_dec      CONSTANT NUMBER:=3254779905;
tlq_mask_hex      CONSTANT VARCHAR2(8):='C2000001';

cnt            NUMBER;
bad_flag_count      NUMBER;
temp_num      NUMBER;
temp_field      varchar2(16);
bit_field      varchar2(16);            
temp_bin      varchar2(256);

BEGIN
      bad_flag_count:=0;
            
      FOR cnt IN 1..Det
      LOOP
            temp_field:=v_tlq_list(cnt);
                  dbms_output.put_line('before ' || temp_field);      
            
            temp_num:=to_dec(temp_field,16);
                  dbms_output.put_line('Dec ' || temp_num);
                        
            bit_field:=bitand(temp_num, tlq_mask_dec);                        
            
            dbms_output.put_line('bit output ' || bit_field);
      
              dbms_output.put_line('bin temp num   ' || to_bin(temp_num));
            dbms_output.put_line('mask           ' || tlq_mask_bin);
            dbms_output.put_line('bin bit output ' || to_bin(bit_field));
                  
 
             IF (TO_NUMBER(bit_field)) >0
            THEN                  
                  bad_flag_count:=bad_flag_count+1;
                        dbms_output.put_line('bad ' || bad_flag_count);
            END IF;

            IF (Bad_Flag_Count>Day_Mean)
            THEN
                  RETURN TRUE;
            END IF;
      
      END LOOP;            
      RETURN FALSE;            

END Determine_TLQ;

This is the output.

before 00000001
Dec 1
pdec1 1
pdec2 3254779905
bit output 1
bin temp num   1
mask           11000010000000000000000000000001
bin bit output 1
bad 1
before C2000001
Dec 3254779905
pdec1 3254779905
pdec2 3254779905
bit output 3254779905
bin temp num   11000010000000000000000000000001
mask           11000010000000000000000000000001
bin bit output 11000010000000000000000000000001
bad 2
before 00008000
Dec 32768
pdec1 32768
pdec2 3254779905
bit output 2147483649
bin temp num   1000000000000000
mask           11000010000000000000000000000001
bin bit output 10000000000000000000000000000001
bad 3
before C0000001
Dec 3221225473
pdec1 3221225473
pdec2 3254779905
bit output 3221225473
bin temp num   11000000000000000000000000000001
mask           11000010000000000000000000000001
bin bit output 11000000000000000000000000000001
bad 4
before 00000000
Dec 0
pdec1 0
pdec2 3254779905
bit output 1
bin temp num   0
mask           11000010000000000000000000000001
bin bit output 1
bad 5
before 82000001
Dec 2181038081
pdec1 2181038081
pdec2 3254779905
bit output 2181038081
bin temp num   10000010000000000000000000000001
mask           11000010000000000000000000000001
bin bit output 10000010000000000000000000000001
bad 6
before 42000001
Dec 1107296257
pdec1 1107296257
pdec2 3254779905
bit output 1107296257
bin temp num   1000010000000000000000000000001
mask           11000010000000000000000000000001
bin bit output 1000010000000000000000000000001
bad 7


The problem I have is that the oracle docs states that when bit ands are done, the unprocessed part of the longer raw string, is appended to the output, which results in a wrong value. So when I enter two numbers of different lengths, and do the bitand, I get the wrong result.

I could do the bitand  by converting the numbers to binary strings, and using a related bitand function, but this would be slow.

Does anybody have a good way of performing a bitand on numbers of uneven length and getting the correct result back. For example, is it possible to 'lpad' a number to make it the same size as the other parameter to bitand, so the resultant value is correct.


regards
Bob.



0
Comment
Question by:scope_creep
[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
3 Comments
 
LVL 11

Expert Comment

by:pennnn
ID: 8103332
If "the unprocessed part of the longer raw string, is appended to the output", then why not just trim the appended unprocessed part before returning the result? Just get the length of the shorter string and after the bit_and operation just trim the result to the desired length.
Hope that helps!
0
 
LVL 4

Accepted Solution

by:
stemu2000 earned 1000 total points
ID: 8106876
following should do:

first you need to convert your two numbers to hex, you can do this by using TO_CHAR function with format mask 'X':


select to_char(255,'XXXXXXXXXXXXXXX') from dual;

TO_CHAR(255,'XXX
----------------
              FF



then make sure both strings are the same lengths, lets say both number should have 20 characters, you can use LPAD for this:

select lpad('FF',20,'0') from dual;

LPAD('FF',20,'0')
--------------------
000000000000000000FF




then make your bitand:

select utl_raw.bit_and('00000000000000000000000000FFF1','0000000000000000000000000020F7') from dual;

UTL_RAW.BIT_AND('0000000000000
------------------------------0000000000000000000000000020F1




and now convert the hex string back to number:

select to_number('0000000000000000000000000000F1',rpad('',20,'X')) from dual;

TO_NUMBER('0000000000000000000000000000F1',RPAD('',20,'X'))
-----------------------------------------------------------





so a program might look like this:


set serveroutput on
declare
 h1 varchar2(30);
 h2 varchar2(30);
 res number;
begin
 h1 := lpad(trim(to_char(3490680,'XXXXXXXXXXXXXXXXXXXX')),20,'0');
 h2 := lpad(trim(to_char(34585,'XXXXXXXXXXXXXXXXXXXX')),20,'0');
 res := to_number(utl_raw.bit_and(h1,h2),'XXXXXXXXXXXXXXXXXXXX');
dbms_output.put_line('result:'||res);
end;
/




Cheers, Stefan


PS: up to 32 bits following also works with the built in BITAND function:

set serveroutput on
begin
  dbms_output.put_line('result:'|| BITAND(3490680,34585) );
end;
/


0
 

Author Comment

by:scope_creep
ID: 8166249

Stemu,

I've tested your solution, and it works well.
Have some points

regards
Bob.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

801 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