oracle - PLSQL Function to convert IPV4 to IPV6

Question

Looking for plsql (Oracle env.) conversion function which excepts a string as an input (IPV4 format) and returns IPV6 format , for example :

Send - 10.85.79.96 will return 0:0:0:0:0:ffff:a55:4f60

Thanks for the help.

Answer 1

You can use this set of functions:

FUNCTION UncompressIpV6(Ip IN VARCHAR2) RETURN VARCHAR2 DETERMINISTIC IS
    IpFull VARCHAR2(40);
    len INTEGER := 7;
BEGIN
    IF REGEXP_LIKE(Ip, '::') THEN
        IpFull := REGEXP_REPLACE(REGEXP_REPLACE(Ip, '^::', '0::'), '::$', '::0');
        IF REGEXP_LIKE(Ip, ':d+.d+.d+.d+$') THEN
            -- Mixed notation, e.g.: 0::FFFF:129.144.52.38
            len := 6;
        END IF;
        WHILE REGEXP_COUNT(IpFull, ':') <= len LOOP
            IpFull := REGEXP_REPLACE(IpFull, '::', ':0::');
        END LOOP;   
        RETURN REGEXP_REPLACE(IpFull, '::', ':');
    ELSE
        RETURN Ip;
    END IF;

END UncompressIpV6;



FUNCTION Ip2Decimal(IP IN VARCHAR2) RETURN NUMBER DETERMINISTIC IS
     DecimalIp NUMBER := 0;
BEGIN

    IF REGEXP_LIKE(IP, ':') THEN
        IF REGEXP_LIKE(IP, 'd+.d+.d+.d+$') THEN
            -- Mixed notation, e.g.: 0:0:0:0:0:FFFF:129.144.52.38
            SELECT SUM(TO_NUMBER(REGEXP_SUBSTR(UncompressIpV6(IP), '[[:xdigit:]]+', 1, LEVEL), 'XXXX') * POWER(65536, 8-LEVEL))
            INTO DecimalIp
            FROM dual 
            CONNECT BY LEVEL <= 6;

            SELECT DecimalIp + SUM(REGEXP_SUBSTR(REGEXP_SUBSTR(UncompressIpV6(IP), 'd+.d+.d+.d+$'), 'd+', 1, LEVEL) * POWER(256, 4-LEVEL))
            INTO DecimalIp
            FROM dual 
            CONNECT BY LEVEL <= 4;
            RETURN DecimalIp;       
        ELSE
            SELECT SUM(TO_NUMBER(REGEXP_SUBSTR(UncompressIpV6(IP), '[[:xdigit:]]+', 1, LEVEL), 'XXXX') * POWER(65536, 8-LEVEL))
            INTO DecimalIp
            FROM dual 
            CONNECT BY LEVEL <= 8;
            RETURN DecimalIp;
        END IF;
    ELSE
        SELECT SUM(REGEXP_SUBSTR(IP, 'd+', 1, LEVEL) * POWER(256, 4-LEVEL))
        INTO DecimalIp
        FROM dual 
        CONNECT BY LEVEL <= 4;
        RETURN DecimalIp;   
    END IF;

END Ip2Decimal;




FUNCTION Decimal2Ip(IpDecimal IN NUMBER) RETURN VARCHAR2 DETERMINISTIC IS

    IP VARCHAR2(16);
    Octet INTEGER;
    v_IpDecimal INTEGER := IpDecimal;

BEGIN
    IF IpDecimal IS NULL THEN 
        RETURN NULL; 
    END IF;
    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;



FUNCTION Decimal2IPv6(IpDecimal IN NUMBER) RETURN VARCHAR2 DETERMINISTIC IS

    IP VARCHAR2(40);
    Octet INTEGER;
    v_IpDecimal NUMBER := IpDecimal;

BEGIN
    IF IpDecimal IS NULL THEN 
        RETURN NULL; 
    END IF;
    IF IpDecimal > 2**128 - 1 THEN
        RAISE NUMERIC_OVERFLOW;
    END IF;

    FOR i IN 1..8 LOOP
        Octet := TRUNC(v_IpDecimal/65536**(8-i));
        v_IpDecimal := v_IpDecimal - Octet * 65536**(8-i);
        IP := IP ||':'||TO_CHAR(Octet, 'fmXXXX');
    END LOOP;
    RETURN LOWER(SUBSTR(IP, 2));

END Decimal2IPv6;

Example:

SELECT REGEXP_REPLACE(Decimal2IPv6(Ip2Decimal('10.85.79.96')), ':0:', ':ffff:', 1, 3) 
FROM dual;

0:0:0:0:0:ffff:a55:4f60