On Mon, January 12, 2009 11:44, ddf wrote:
>
>
>
> On Jan 6, 3:01 am, Rem-8 <lukasz.r...@gmail.com> 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
> >
>
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
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to Oracle-PLSQL@googlegroups.com
To unsubscribe from this group, send email to
oracle-plsql-unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
-~----------~----~----~----~------~----~------~--~---
