SQL> create or replace
  2  package ip_util is
  3
  4  function ip_num_from_str(p_ip_str varchar2) return number deterministic;
  5  function ip_str_from_num(p_ipnum number) return varchar2 deterministic;
  6  function to_num( p_bin in varchar2 ) return number deterministic;
  7  function to_bin( p_dec in number ) return varchar2;
  8
  9  end;
 10  /

Package created.

SQL>
SQL> create or replace
  2  package body ip_util is
  3
  4    l_ip41 constant number(12)  := 256;        -- power(256,1);
  5    l_ip42 constant number(12)  := 65536;      -- power(256,2);
  6    l_ip43 constant number(12)  := 16777216;   -- power(256,3);
  7    l_ip44 constant number(12)  := 4294967296; -- power(256,4);
  8
  9  function ip_num_from_str(p_ip_str varchar2) return number deterministic is
 10    l_ip_num     number;
 11    l_dot1       pls_integer;
 12    l_dot2       pls_integer;
 13    l_dot3       pls_integer;
 14    l_dot4       pls_integer;
 15
 16    l_colon      pls_integer;
 17    l_colon_cnt  pls_integer;
 18    l_hex        varchar2(32);
 19    l_ip_str     varchar2(64);
 20  begin
 21      l_dot1   := instr(p_ip_str,'.');
 22      l_dot2   := instr(p_ip_str,'.',l_dot1+1);
 23      l_dot3   := instr(p_ip_str,'.',l_dot2+1);
 24      l_dot4   := instr(p_ip_str,'.',l_dot3+1);
 25
 26      l_ip_num :=  l_ip43*to_number(substr(p_ip_str,1,l_dot1-1)) +
 27                   l_ip42*to_number(substr(p_ip_str,l_dot1+1,l_dot2-l_dot1-1)) +
 28                   l_ip41*to_number(substr(p_ip_str,l_dot2+1,l_dot3-l_dot2-1)) +
 29                   to_number(substr(p_ip_str,l_dot3+1));
 30
 31    return l_ip_num;
 32  end;
 33
 34  function ip_str_from_num(p_ipnum number) return varchar2 deterministic is
 35  begin
 36      return  mod(trunc(p_ipnum/l_ip43),l_ip41) ||'.'||
 37              mod(trunc(p_ipnum/l_ip42),l_ip41) ||'.'||
 38              mod(trunc(p_ipnum/l_ip41),l_ip41) ||'.'||
 39              mod(p_ipnum,l_ip41);
 40
 41  end;
 42
 43  function to_num( p_bin in varchar2 ) return number is
 44    l_num int := 0;
 45    l_power int := 1;
 46  begin
 47    for i in reverse 1 .. length(p_bin)  loop
 48      l_num := l_num + to_number(substr(p_bin,i,1))*l_power;
 49      l_power := l_power*2;
 50    end loop;
 51    return l_num;
 52  end;
 53
 54  function to_bin( p_dec in number ) return varchar2
 55  is
 56     l_str varchar2(255) default NULL;
 57     l_num number default p_dec;
 58     l_bit varchar2(16) default '01';
 59  begin
 60     loop
 61      l_str := substr( l_bit, mod(l_num,2)+1, 1 ) || l_str;
 62      l_num := trunc( l_num/2 );
 63      exit when ( l_num = 0 );
 64     end loop;
 65     return l_str;
 66  end;
 67
 68  end;
 69  /

Package body created.

SQL>
SQL>
SQL> with t as
  2   ( select '10.188.57.193' ip, 26 subnet from dual )
  3  select
  4    ip_util.ip_str_from_num(
  5       bitand(
  6         ip_util.ip_num_from_str(ip) ,
  7         ip_util.to_num(substr(rpad('1',subnet,'1')||rpad('0',32,'0'),1,32))
  8       )
  9     ) i1,
 10    ip_util.ip_str_from_num(
 11       ip_util.to_num(
 12         substr(
 13           substr(
 14             lpad(ip_util.to_bin(ip_util.ip_num_from_str(ip)),32,'0'),1,subnet)
 15               ||rpad('1',32,'1'),1,32)
 16          )
 17    ) i2
 18  from t;

I1                             I2
------------------------------ ------------------------------
10.188.57.192                  10.188.57.255

1 row selected.

SQL>