How to convert decimal number to IP address

oracleoracle-11g-r2

I need a function or query to convert a decimal number to ann IP address.

Presently my ip_address column looks like this:

select  ip_address from test_ip;

    ip_address

     173015430
     173015318
     927815779

I need the result like this:

         ip_address
        10.80.1.134
        10.80.1.22
        55.77.88.99

I can do this in MySQL by using INET_NTOA function. How can I accomplish this in Oracle?

Best Answer

I am using these two functions in my application:

CREATE OR REPLACE FUNCTION Ip2Decimal(IP IN VARCHAR2) RETURN INTEGER DETERMINISTIC IS
    DecimalIp INTEGER;
BEGIN   
    SELECT SUM(REGEXP_SUBSTR(IP, '\d+', 1, LEVEL) * POWER(256, 4-LEVEL))
    INTO DecimalIp
    FROM dual 
    CONNECT BY LEVEL <= 4;
    RETURN DecimalIp;
END Ip2Decimal;

and

CREATE OR REPLACE FUNCTION Decimal2Ip(IpDecimal IN INTEGER) RETURN VARCHAR2 DETERMINISTIC IS        

    NUMERIC_OVERFLOW EXCEPTION;
    PRAGMA EXCEPTION_INIT(NUMERIC_OVERFLOW, -1426);

    IP VARCHAR2(16);
    Octet INTEGER;
    v_IpDecimal INTEGER := IpDecimal;       
BEGIN
    IF IpDecimal > 2**32 -1 THEN
        RAISE NUMERIC_OVERFLOW;
    END IF;    
    FOR i IN 1..4 LOOP
        Octet := TRUNC(v_IpDecimal/256**(4-i));
        v_IpDecimal := v_IpDecimal - Octet * 256**(4-i);
        IP := IP ||'.'||Octet;
    END LOOP;
    RETURN SUBSTR(IP, 2);    
END Decimal2Ip;
Related Question