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

Question

We have an Oracle database that contains IP addresses stored as decimal integers - this is incredibly painful when manipulating data manually, and not through the web interface, however, manipulating the hands is really convenient, since the guys from the network constantly ask us to do strange things that web interface authors did not expect.

Can someone provide me with PL / SQL or another method for displaying these decimal IP addresses as dot decimal numbers, i.e. 123.123.123.123 format?

those. I would like to be able to run a query, for example:

select hostname, inttoip(ip_address) from host; 

and in the inttoip() procedure, inttoip() displayed as 203.30.237.2 instead of 3407801602.

Ideally, I need a procedure that also provides an inverse function, like

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

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

An alternative way to run perl as a procedural language in an oracle context is similar to the following in postgres:

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

It would be great - if possible - perhaps even better than I could do a lot of procedural things in Oracle in a language I am familiar with.

Answer 1

This is a necessary function:

 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 on how to make the function deterministic and use to_char on board - thanks).

In Oracle 11G, you can make the formatted IP address a virtual column in the hosts 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 can then be indexed for queries.

Your request will look like this:

 select hostname, formatted_ip_address from host; 

Answer 2

 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, eg '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); -- eg '203' q2 := SUBSTR(ip_string, d1 + 1, d2 - d1 - 1); -- eg '30' q3 := SUBSTR(ip_string, d2 + 1, d3 - d2 - 1); -- eg '237' q4 := SUBSTR(ip_string, d3 + 1); -- eg '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; 

Answer 3

  -- 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; /