On Jan 6, 3:01 am, Rem-8 <[email protected]> wrote:
> 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...
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
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
-~----------~----~----~----~------~----~------~--~---
