Convert long hex into binary and sum 1's

[Rem-8]

Hello all oracle masters :)

I got a dillema. In an SNMP object called vlanportislvlansallowed
there's a VARCHAR2 (128) value of hex digits representation of all
Vlans on a network router/switch. Basically this means there could be
1024 vlans. The output of SNMP in Oracle can be like this:

00F8 0082 0000 0000 0000 0006 0004 0001 0080 0068 0033 00D1 0019 0050
0038 0068 0050 0034 005D 00C6 0001 00E0 0010 0090 001C 0080.........
etc. up to 128 bytes long.

Now the clue is to convert this whole hex values into binary ones and
count all 1's and return the sum of whole line. I really don't know
why oracle puts 00 before each hex...

[sonty]

Write a function which will convert the string and then will count the
number of ones and return the sum....

Sonty

[Rob Wolfe]

Not to rain on the parade Sonty but I think that he was hoping that
Oracle had some builtins to do just that. There are some basic bit
twiddling stuff in utl_raw (I think that is the package... you would
have to check) and parsing those hex values out would be pretty
straightforward. I have wracked my brain though to find a hex to
binary format converter and dont know of one in PL/SQL. Someone else
might ...David you out there?

Sonty is right though, it is pretty simple to write a quick function
to convert hex to a bitstring. you can do a simpleminded version
(usually my choice with low data volumes) with merely a loop and a
case statement. you could then either actually count the 1's or you
could remove the 0's and just check the length of the resulting
string. Not sure which would be faster.

for instance something like

select length(replace(hextobitstring(replace(vlanportislvlansallowed,'
',''),'0','') from sometable

where you write hextobitstring() to do the conversion from hex to a
bitstring.

Hope this helps a little

Rob

> > why oracle puts 00 before each hex...- Hide quoted text -
>
> - Show quoted text -

[ddf]

Possibly this will help:

SQL> create or replace function sum_ones(p_snmp in varchar2)
2 return number
3 as
4
5 base number:=16;
6 start_pos number:=3;
7 curr_pos number;
8 strng_len number:=2;
9 hex_val varchar2(2);
10 ones_ct number:=0;
11 ttl_ct number:=0;
12
13 begin
14 hex_val := substr(p_snmp, start_pos, strng_len);
15
16 if hex_val = '00' then
17 ones_ct := 0;
18 else
19 SELECT length(replace(utl_raw.cast_to_varchar2(utl_raw.reverse
(utl_raw.cast_to_raw(to_char(str)))), '0', null))
20 into ones_ct
21 FROM
22 (WITH DATA AS
23 (SELECT SUM( num * POWER(base, rn -1) ) base_10_num
24 FROM
25 (SELECT instr(num_str, upper(doc.extract('/X/text
()').getStringVal()))-1
26 as num, rownum rn, a.base
27 FROM
28 (SELECT '0123456789ABCDEF' as num_str, base as base,
29 utl_raw.cast_to_varchar2(utl_raw.reverse
(utl_raw.cast_to_raw(to_char(hex_val))))
30 as input FROM DUAL) a,
31 TABLE(xmlSequence(extract(XMLType('<DOC>'||
32 REGEXP_REPLACE(a.input,'(.)','<X>\1</X>')||'</DOC>'),'/DOC/
X'))) doc
33 )
34 )
35 SELECT max(replace(sys_connect_by_path(sign(bitand
(base_10_num, power(2,LEVEL-1))),','),','))
36 AS str
37 FROM (SELECT base_10_num FROM DATA) a
38 CONNECT BY power(2,LEVEL - 1)<= base_10_num
39 );
40 end if;
41
42 curr_pos := start_pos;
43 start_pos := instr(p_snmp, ' ', curr_pos) + 3;
44
45
46 while start_pos > 3 loop
47 ttl_ct := ttl_ct + ones_ct;
48 hex_val := substr(p_snmp, start_pos, strng_len);
49
50 if hex_val = '00' then
51 ones_ct := 0;
52 else
53 SELECT length(replace(utl_raw.cast_to_varchar2(utl_raw.reverse
(utl_raw.cast_to_raw(to_char(str)))), '0', null))
54 into ones_ct
55 FROM
56 (WITH DATA AS
57 (SELECT SUM( num * POWER(base, rn -1) ) base_10_num
58 FROM
59 (SELECT instr(num_str, upper(doc.extract('/X/text
()').getStringVal()))-1
60 as num, rownum rn, a.base
61 FROM
62 (SELECT '0123456789ABCDEF' as num_str, base as base,
63 utl_raw.cast_to_varchar2(utl_raw.reverse
(utl_raw.cast_to_raw(to_char(hex_val))))
64 as input FROM DUAL) a,
65 TABLE(xmlSequence(extract(XMLType('<DOC>'||
66 REGEXP_REPLACE(a.input,'(.)','<X>\1</X>')||'</DOC>'),'/DOC/
X'))) doc
67 )
68 )
69 SELECT max(replace(sys_connect_by_path(sign(bitand
(base_10_num, power(2,LEVEL-1))),','),','))
70 AS str
71 FROM (SELECT base_10_num FROM DATA) a
72 CONNECT BY power(2,LEVEL - 1)<= base_10_num
73 );
74 end if;
75 curr_pos := start_pos;
76 start_pos := instr(p_snmp, ' ', curr_pos) + 3;
77
78 end loop;
79
80 ttl_ct := ttl_ct + ones_ct;
81
82 return ttl_ct;
83
84 end;
85 /

Function created.

SQL>
SQL> select sum_ones('00FF 00FF 00FF 00FF 00FF 00FF 00FF 00FF 00FF
00FF 00FF 00FF 00FF 00FF 00FF 00FF 00FF 00FF 00FF 00FF 00FF 00FF 00FF
00FF 00FF 00F
F 00FF 00FF 00FF 00FF 00FF 00FF 00FF 00FF 00FF 00FF 00FF 00FF 00FF
00FF 00FF 00FF 00FF 00FF 00FF 00FF 00FF 00FF 00FF 00FF 00FF 00FF 00FF
00FF 00FF 00F
F 00FF 00FF 00FF 00FF 00FF 00FF 00FF 00FF 00FF 00FF 00FF 00FF 00FF
00FF 00FF 00FF 00FF 00FF 00FF 00FF 00FF 00FF 00FF 00FF 00FF 00FF 00FF
00FF 00FF 00F
F 00FF 00FF 00FF 00FF 00FF 00FF 00FF 00FF 00FF 00FF 00FF 00FF 00FF
00FF 00FF 00FF 00FF 00FF 00FF 00FF 00FF 00FF 00FF 00FF 00FF 00FF 00FF
00FF 00FF 00F
F 00FF 00FF 00FF 00FF 00FF 00FF 00FF 00FF 00FF 00FF 00FF 00FF')
2 from dual;

SUM_ONES
('00FF00FF00FF00FF00FF00FF00FF00FF00FF00FF00FF00FF00FF00FF00FF00FF00FF00
--------------------------------------------------------------------------------

1024

SQL>


David Fitzjarrell

[Rob Wolfe]

Note that David's solution only works in 10g and above since the regular
expression functionality didnt appear until then. Poor shlubs like me that
are still stuck on an older version must still muddle along without them
:(

I also note that David has caught Mike's XML illness, another good man
goes over to the dark side... :-)

Very nice solution David, as per usual!

Rob

[Michael Moore]

DECLARE
   mystr    VARCHAR2 (1000) := '00AF 00F3 0019';
   mychar   CHAR (1);
   total    NUMBER (10)     := 0;
BEGIN
   FOR i IN 1 .. LENGTH (mystr)
   LOOP
      mychar := SUBSTR (mystr, i, 1);

      CASE mychar
         WHEN '1'
         THEN
            total := total + 1;
         WHEN '2'
         THEN
            total := total + 1;
         WHEN '3'
         THEN
            total := total + 2;
         WHEN '4'
         THEN
            total := total + 1;
         WHEN '5'
         THEN
            total := total + 2;
         WHEN '6'
         THEN
            total := total + 2;
         WHEN '7'
         THEN
            total := total + 3;
         WHEN '8'
         THEN
            total := total + 1;
         WHEN '9'
         THEN
            total := total + 2;
         WHEN 'A'
         THEN
            total := total + 2;
         WHEN 'B'
         THEN
            total := total + 3;
         WHEN 'C'
         THEN
            total := total + 2;
         WHEN 'D'
         THEN
            total := total + 3;
         WHEN 'E'
         THEN
            total := total + 3;
         WHEN 'F'
         THEN
            total := total + 4;
         ELSE
            NULL;
      END CASE;
   END LOOP;
   dbms_output.put_line(to_char(total));
END;

[rob wolfe]

now that is thinking outside the box. I like it

[Michael Moore]

we could shorten it a little

DECLARE
   mystr    VARCHAR2 (1000) := '00AF 00F3';

   mychar   CHAR (1);
   total    NUMBER (10)     := 0;
BEGIN
   FOR i IN 1 .. LENGTH (mystr)
   LOOP
      mychar := SUBSTR (mystr, i, 1);

      CASE

         when instr('1248',mychar)   !=0 then total := total +1;
         when instr('3569AC',mychar) !=0 then total := total +2;
         when instr('7BDE',mychar)   !=0 then total := total +3;
         when instr('F',mychar)      !=0 then total := total +4;        

[sonty]

Nice solutions guys but I think you are solving the wrong problem or
atleast half of the actual problem.
I have the complete solution but it does not satisfy my instinct to be
a real programmer.

On the other hand, with David's Solution I feel that I need to go back
to the start all over again to reach out all the available ways to do
things.... :-D

--Sonty