IP address stored as decimal - PL/SQL to display a

Question

We have an Oracle database that contains IP addresses stored as decimal integers - this is incredibly painful when manipulating the data by hand instead of via the web interface, yet hand manipulation is really handy as the network guys continually ask us to do strange things that the web interface authors did not anticipate.

Could someone provide me with the PL/SQL or other method to display these decimal IPs as dotted decimal i.e. 123.123.123.123 format?

I.e. I'd like to be able to run a query such as :

select hostname, inttoip(ip_address) from host;

and have the inttoip() procedure display ip_address as 203.30.237.2 instead of as 3407801602.

Ideally I'd like a procedure which provides the inverse function too, e.g.

insert into host (hostname,ip_address) values ('some-hostname', iptoint('203.30.237.2'));

I have perl to do this, but my PL/SQL/Oracle knowledge is not good enough to port it into PL/SQL.

Alternatively a way to run the perl as the procedural language within the oracle context analogous to the following in postgres:

CREATE FUNCTION perl_func (integer) RETURNS integer AS $$
 <some perl>
$$ LANGUAGE plperl;

Would be great - if possible - probably even better as I could then do lots of procedural stuff within Oracle in a language I am familiar with.

Answer 1

    -- INET ATON en INET NTOA and helper function GET TOKEN

        CREATE OR REPLACE function inet_ntoa (ip integer) return varchar2
    is
       ip1 integer;
       ip2 integer;
       ip3 integer;
       ip4 integer;
       ipi integer := ip;
    begin
       ip1 := floor(ipi/power(2,24));
       ipi := ipi - (ip1*power(2,24));
       ip2 := floor(ipi/power(2,16));
       ipi := ipi - (ip2*power(2,16));
       ip3 := floor(ipi/power(2,8));
       ipi := ipi - (ip3*power(2,8));
       ip4 := ipi;
       return ip1||'.'||ip2||'.'||ip3||'.'||ip4;

    end;
    /    

CREATE OR REPLACE FUNCTION get_token (the_list VARCHAR2,the_index NUMBER, delim VARCHAR2 := '.') RETURN VARCHAR2
        IS
           start_pos   INTEGER;
           end_pos     INTEGER;
        BEGIN
           IF the_index = 1 THEN
              start_pos := 1;
           ELSE
              start_pos := INSTR (the_list, delim, 1, the_index - 1);
              IF start_pos = 0 THEN
                 RETURN NULL;
              ELSE
                 start_pos := start_pos + LENGTH (delim);
              END IF;
           END IF;
           end_pos := INSTR (the_list, delim, start_pos, 1);
           IF end_pos = 0 THEN
              RETURN SUBSTR (the_list, start_pos);
           ELSE
              RETURN SUBSTR (the_list, start_pos, end_pos - start_pos);
           END IF;
        END get_token;
        /




    CREATE OR REPLACE function inet_aton (ip varchar2) return integer
    is
       invalid_ip_adres exception;
       pragma exception_init(invalid_ip_adres,-6502);
       ipi integer;
    begin
       ipi := get_token(ip,4)
            +(get_token(ip,3)*power(2,8))
                    +(get_token(ip,2)*power(2,16))
                    +(get_token(ip,1)*power(2,24));
       return ipi;
    exception
       when invalid_ip_adres
       then 
          return null;
    end;
    /

Answer 2

This is the function you need:

create or replace
function inttoip(ip_address integer) return varchar2
deterministic
is
begin
    return to_char(mod(trunc(ip_address/256/256/256),256))
           ||'.'||to_char(mod(trunc(ip_address/256/256),256))
           ||'.'||to_char(mod(trunc(ip_address/256),256))
           ||'.'||to_char(mod(ip_address,256));
end;

(Comments about making function deterministic and using to_char taken on board - thanks).

In Oracle 11G you could make the formatted IP address a virtual column on the host table:

alter table host
add formatted_ip_address varchar2(15)
generated always as
( to_char(mod(trunc(ip_address/256/256/256),256))
          ||'.'||to_char(mod(trunc(ip_address/256/256),256))
          ||'.'||to_char(mod(trunc(ip_address/256),256))
          ||'.'||to_char(mod(ip_address,256))
) virtual;

This column could then be indexed for queries if required.

Your query becomes:

select hostname, formatted_ip_address from host;

Answer 3

CREATE OR REPLACE
FUNCTION inttoip(ip_address IN INTEGER) RETURN VARCHAR2 IS
  v8 VARCHAR2(8);
BEGIN
  -- 1. convert the integer into hexadecimal representation
  v8 := TO_CHAR(ip_address, 'FMXXXXXXXX');
  -- 2. convert each XX portion back into decimal
  RETURN to_number(substr(v8,1,2),'XX')
       || '.' || to_number(substr(v8,3,2),'XX')
       || '.' || to_number(substr(v8,5,2),'XX')
       || '.' || to_number(substr(v8,7,2),'XX');
END inttoip;

CREATE OR REPLACE
FUNCTION iptoint(ip_string IN VARCHAR2) RETURN INTEGER IS
  d1 INTEGER;
  d2 INTEGER;
  d3 INTEGER;
  q1 VARCHAR2(3);
  q2 VARCHAR2(3);
  q3 VARCHAR2(3);
  q4 VARCHAR2(3);
  v8 VARCHAR2(8);
BEGIN
  -- 1. parse the input, e.g. '203.30.237.2'
  d1 := INSTR(ip_string,'.');     -- first dot
  d2 := INSTR(ip_string,'.',1,2); -- second dot
  d3 := INSTR(ip_string,'.',1,3); -- third dot
  q1 := SUBSTR(ip_string, 1, d1 - 1);           -- e.g. '203'
  q2 := SUBSTR(ip_string, d1 + 1, d2 - d1 - 1); -- e.g. '30'
  q3 := SUBSTR(ip_string, d2 + 1, d3 - d2 - 1); -- e.g. '237'
  q4 := SUBSTR(ip_string, d3 + 1);              -- e.g. '2'
  -- 2. convert to a hexadecimal string
  v8 := LPAD(TO_CHAR(TO_NUMBER(q1),'FMXX'),2,'0')
     || LPAD(TO_CHAR(TO_NUMBER(q2),'FMXX'),2,'0')
     || LPAD(TO_CHAR(TO_NUMBER(q3),'FMXX'),2,'0')
     || LPAD(TO_CHAR(TO_NUMBER(q4),'FMXX'),2,'0');
  -- 3. convert to a decimal number
  RETURN TO_NUMBER(v8, 'FMXXXXXXXX');
END iptoint;