misc-scripts/ip_util_pkg.sql at master · connormcd/misc-scripts · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
REM
REM Standard disclaimer - anything in here can be used at your own risk.
REM
REM It is possible you'll need to edit the script for correct usernames/passwords, missing information etc.
REM
REM No warranty or liability etc etc etc. See the license file in the git repo root
REM
REM *** USE AT YOUR OWN RISK ***
REM


REM
REM
REM Simple package to convert IP addresses from string to numbers and back
REM
REM
REM Sample output
REM
REM select ip_util.ip_num_from_str('192.168.1.2') from dual;
REM
REM IL.IP_NUM_FROM_STR('192.168.1.2')
REM ---------------------------------
REM                        3232235778
REM
REM select ip_util.ip_str_from_num(3232235778) from dual;
REM
REM IL.IP_STR_FROM_NUM(3232235778)
REM ------------------------------------------------------------------------------------------------------------
REM 68.1.2
REM
REM select ip_util.ip_num_from_str('2001:db8:0:0:0:ff00:42:8329') ip from dual;
REM
REM                                      IP
REM ---------------------------------------
REM  42540766411282592856904265327123268393
REM
REM select ip_util.ip_str_from_num(42540766411282592856904265327123268393) from dual;
REM
REM IL.IP_STR_FROM_NUM(42540766411282592856904265327123268393)
REM ------------------------------------------------------------------------------------------------------------
REM db8:0:0:0:ff00:42:8329
REM


create or replace
package ip_util is

function ip_num_from_str(p_ip_str varchar2) return number deterministic;
function ip_str_from_num(p_ipnum number) return varchar2 deterministic;

end;
/

create or replace
package body ip_util is

  --
  -- constants need to be fixed, not expressions if you want to avoid ora-4068
  --
  l_ip41 constant number(12)  := 256;        -- power(256,1);
  l_ip42 constant number(12)  := 65536;      -- power(256,2);
  l_ip43 constant number(12)  := 16777216;   -- power(256,3);
  l_ip44 constant number(12)  := 4294967296; -- power(256,4);

  l_ip61 constant number(38)  := 65536;                              --power(65536,1);
  l_ip62 constant number(38)  := 4294967296;                         --power(65536,2);
  l_ip63 constant number(38)  := 281474976710656;                    --power(65536,3);
  l_ip64 constant number(38)  := 18446744073709551616;               --power(65536,4);
  l_ip65 constant number(38)  := 1208925819614629174706176;          --power(65536,5);
  l_ip66 constant number(38)  := 79228162514264337593543950336;      --power(65536,6);
  l_ip67 constant number(38)  := 5192296858534827628530496329220096; --power(65536,7);


function ip_num_from_str(p_ip_str varchar2) return number deterministic is
  l_ip_num     number;
  l_dot1       pls_integer;
  l_dot2       pls_integer;
  l_dot3       pls_integer;
  l_dot4       pls_integer;

  l_colon      pls_integer;
  l_colon_cnt  pls_integer;
  l_hex        varchar2(32);
  l_ip_str     varchar2(64);
begin
  if p_ip_str like '%.%' then
    l_dot1   := instr(p_ip_str,'.');
    l_dot2   := instr(p_ip_str,'.',l_dot1+1);
    l_dot3   := instr(p_ip_str,'.',l_dot2+1);
    l_dot4   := instr(p_ip_str,'.',l_dot3+1);
    if l_dot4 > 0 then
       raise_application_error(-20000,'Cannot be resolved to an IP4 address');
    end if;

    l_ip_num :=  l_ip43*to_number(substr(p_ip_str,1,l_dot1-1)) +
                 l_ip42*to_number(substr(p_ip_str,l_dot1+1,l_dot2-l_dot1-1)) +
                 l_ip41*to_number(substr(p_ip_str,l_dot2+1,l_dot3-l_dot2-1)) +
                 to_number(substr(p_ip_str,l_dot3+1));

  elsif p_ip_str like '%:%' then
    --
    -- Note: The abbreviation of "Consecutive sections of zeroes are replaced with a double colon (::)" is not implemented.
    --
    l_colon_cnt := length(p_ip_str)-length(replace(p_ip_str,':'));
    if l_colon_cnt != 7 then
       raise_application_error(-20000,'Cannot be resolved to an IP6 address');
    end if;

    l_ip_str := p_ip_str||':';
    loop
      l_colon := instr(l_ip_str,':');
      l_hex := l_hex || lpad(substr(l_ip_str,1,l_colon-1),4,'0');
      l_ip_str := substr(l_ip_str,l_colon+1);
      exit when l_ip_str is null;
    end loop;
    l_ip_num := to_number(l_hex,'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx');
  end if;

  return l_ip_num;
end;


function ip_str_from_num(p_ipnum number) return varchar2 deterministic is
begin
  if p_ipnum < l_ip44 then
    return  mod(trunc(p_ipnum/l_ip43),l_ip41) ||'.'||
            mod(trunc(p_ipnum/l_ip42),l_ip41) ||'.'||
            mod(trunc(p_ipnum/l_ip41),l_ip41) ||'.'||
            mod(p_ipnum,l_ip41);
  else
    --
    -- Note: The abbreviation of "Consecutive sections of zeroes are replaced with a double colon (::)" is not implemented.
    --
    return  to_char(mod(trunc(p_ipnum/l_ip67),l_ip61),'fmxxxx') ||':'||
            to_char(mod(trunc(p_ipnum/l_ip66),l_ip61),'fmxxxx') ||':'||
            to_char(mod(trunc(p_ipnum/l_ip65),l_ip61),'fmxxxx') ||':'||
            to_char(mod(trunc(p_ipnum/l_ip64),l_ip61),'fmxxxx') ||':'||
            to_char(mod(trunc(p_ipnum/l_ip63),l_ip61),'fmxxxx') ||':'||
            to_char(mod(trunc(p_ipnum/l_ip62),l_ip61),'fmxxxx') ||':'||
            to_char(mod(trunc(p_ipnum/l_ip61),l_ip61),'fmxxxx') ||':'||
            to_char(mod(p_ipnum,l_ip61),'fmxxxx');
  end if;
end;

end;
/