Once I wrote a general PL/SQL Package where you can do such conversions. It works for both, IPv4 and IPv6.
CREATE OR REPLACE PACKAGE IP_Util AS
/**
* Convert an IP-Address into decimal value.
* @param IP The IP-Address, e.g. '10.151.20.224' or '1080::8:800:200C:417A'.
* Supports also mixed notation like '0:0:0:0:0:FFFF:129.144.52.38'. CIDR value (e.g. '1080::8:800:200C:417A/80') is ignored.
* @return The decimal equivalent
*/
FUNCTION IP2Decimal(IP IN VARCHAR2) RETURN NUMBER DETERMINISTIC;
/**
* Convert an IP-Address into RWA value.
* @param IP The IP-Address, e.g. '10.151.20.224' or '1080::8:800:200C:417A'.
* Supports also mixed notation like '0:0:0:0:0:FFFF:129.144.52.38'. CIDR value (e.g. '1080::8:800:200C:417A/80') is ignored.
* @param ver IP version, either 4 or 6. If NULL then function determines the IP version.
* @return The RAW equivalent
*/
FUNCTION IP2RAW(IP IN VARCHAR2, ver IN INTEGER DEFAULT NULL) RETURN RAW DETERMINISTIC;
/**
* Convert an IP-Address from decimal value into IPv4 or IPv6 format.
* @param ip Decimal IP-Address, 0..(2**32)-1 or 0..(2**128)-1
* @param ver IP version, either 4 or 6
* @return The IP in IPv4 or IPv6 format
*/
FUNCTION Decimal2IP(ip IN NUMBER, ver IN INTEGER) RETURN VARCHAR2 DETERMINISTIC;
/**
* Convert an IP-Address from RAW value into IPv4 or IPv6 format.
* @param ip RAW value of IP-Address, 0..FFFFFFFF or 0..FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF
* @param ver IP version, either 4 or 6
* @return The IP in IPv4 or IPv6 format
*/
FUNCTION RAW2IP(ip IN RAW, ver IN INTEGER) RETURN VARCHAR2 DETERMINISTIC;
/**
* Returns SubnetMask of given IP-Subnet in CIDR notation.
* @param Ip Subnet IP-Address with CIDR notation, e.g. '10.152.10.17/24' or '1080::8:800:200C:417A/60'
* @return SubnetMask Subnet mask of IP-Subnet, e.g. '255.255.255.0' or 'ffff:ffff:ffff::'
*/
FUNCTION SubnetMask(Ip IN VARCHAR2) RETURN VARCHAR2 DETERMINISTIC;
/**
* Returns Network prefix of given IP-Subnet. In IPv4 this address was called subnet address.
* @param Ip IP-Address of subnet, e.g. '10.152.10.17' or '1080:0:100:8:800:200C:FFFF:417A'
* @param SubnetMask Subnet mask of subnet, e.g. '255.255.0.0' or 'FFFF:FFFF:FFFF::'
* @return Network prefix, i.e. the first address from subnet, for example '10.152.0.0' or '1080:0:100:8:800:200C:FFFF::'
*/
FUNCTION NetworkPrefix(Ip IN VARCHAR2, SubnetMask IN VARCHAR2) RETURN VARCHAR2 DETERMINISTIC;
/**
* Returns Network prefix of given IP-Subnet. In IPv4 this address was called subnet address.
* @param Ip IP-Subnet with CIDR notation, e.g. '10.152.10.17/24' or '1080:0:100:8:800:200C:FFFF:417A/60'
* @return Network prefix, i.e. the first address from subnet, for example '10.152.0.0' or '1080:0:100:8:800:200C:FFFF::'
*/
FUNCTION NetworkPrefix(Ip IN VARCHAR2) RETURN VARCHAR2 DETERMINISTIC;
/**
* Returns Broadcast address of given IP-Subnet.
* IPv6 does not provide Broadcast anymore. However, function supports IPv6 for internal purpose.
* @param Ip IP-Address of subnet, e.g. '10.152.10.17' or '1080:0:100:8:800:200C:FFFF:417A'
* @param SubnetMask Subnet mask of subnet, e.g. '255.255.0.0' or 'FFFF:FFFF:FFFF::'
* @return Broadcast address, i.e. the last address from subnet, for example '10.152.10.255' or '1080:0:100:8:800:ffff:ffff:ffff'
*/
FUNCTION BroadcastIp(Ip IN VARCHAR2, SubnetMask IN VARCHAR2) RETURN VARCHAR2 DETERMINISTIC;
/**
* Returns Broadcast address of given IP-Subnet.
* IPv6 does not provide Broadcast anymore. However, function supports IPv6 for internal purpose.
* @param Ip IP-Subnet with CIDR notation, e.g. '10.152.10.17/24' or '1080:0:100:8:800:200C:FFFF:417A/60'
* @return Broadcast address, i.e. the last address from subnet, for example '10.152.10.255' or '1080:0:100:8:800:ffff:ffff:ffff'
*/
FUNCTION BroadcastIp(Ip IN VARCHAR2) RETURN VARCHAR2 DETERMINISTIC;
/**
* Translate Subnet mask to CIDR.
* @param SubnetMask Subnet mask of subnet, e.g. '255.255.0.0' or 'FFFF:FFFF:FFFF::'
* @return CIDR value, e.g. 26
*/
FUNCTION SubnetMask2CIDR(SubnetMask VARCHAR2) RETURN INTEGER RESULT_CACHE DETERMINISTIC;
/**
* Translate CIDR to Subnet mask in IPv4 or IPv6 format.
* @param CIDR Length of network prefix
* @param ver IP version, either 4 or 6
* @return Subnet mask, e.g. '255.255.0.0' or 'FFFF:FFFF:FFFF::'
*/
FUNCTION CIDR2SubnetMask(CIDR IN INTEGER, ver IN INTEGER) RETURN VARCHAR2 RESULT_CACHE DETERMINISTIC;
/**
* Returns full uncompressed IPv6 Address. Mainly used for internal purpose like conversion, storage, comparison, etc.
* '::' is replaced by zero pads, leading '0' are inserted (if leadingZero = TRUE), converted to lower cases.
* @param Ip Compact IPv6-Address (with CIDR or without CIDR, e.g. 2620:0:2D0:A2A2::7)
* @param leadingZero If TRUE then bit fields are padded with '0' in order to have always 4 characters
* @return The full IPv6 Address with 8 x 16 bits, e.g. '2620:0000:02d0:a2a2:0000:0000:0000:0007'
*/
FUNCTION UncompressIpV6(Ip IN VARCHAR2, leadingZero IN BOOLEAN DEFAULT TRUE) RETURN VARCHAR2 DETERMINISTIC;
/**
* Makes an canonical IPv6 address according to RFC 5952, i.e. human readable.
* @param IPv6 IPv6-Address (with or without '::', with or without leading '0')
* @return Canonical IPv6 Address, e.g. 2620:0:2d0:200::7
*/
FUNCTION Canonical_IPv6(IPv6 IN VARCHAR2) RETURN VARCHAR2 DETERMINISTIC;
END IP_Util;
/
CREATE OR REPLACE PACKAGE BODY IP_Util AS
NUMERIC_OVERFLOW EXCEPTION;
PRAGMA EXCEPTION_INIT(NUMERIC_OVERFLOW, -1426);
FUNCTION IP2Decimal(IP IN VARCHAR2) RETURN NUMBER DETERMINISTIC IS
DecimalIp NUMBER; -- INTEGER does not cover (2**128)-1
BEGIN
IF REGEXP_LIKE(IP, ':') THEN
-- IPv6 Address
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
-- IPv4 Address
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 IP2RAW(IP IN VARCHAR2, ver IN INTEGER DEFAULT NULL) RETURN RAW DETERMINISTIC IS
BEGIN
IF ver IS NULL THEN
IF REGEXP_LIKE(IP, ':') THEN
RETURN IP2RAW(IP, 6);
ELSE
RETURN IP2RAW(IP, 4);
END IF;
ELSE
IF ver = 6 THEN
RETURN HEXTORAW(LPAD(TO_CHAR(IP2Decimal(ip), 'fmXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'), 32, '0'));
ELSIF ver = 4 THEN
RETURN HEXTORAW(LPAD(TO_CHAR(IP2Decimal(ip), 'fmXXXXXXXX'), 8, '0'));
ELSE
RAISE VALUE_ERROR;
END IF;
END IF;
END IP2RAW;
FUNCTION RAW2IP(ip IN RAW, ver IN INTEGER) RETURN VARCHAR2 DETERMINISTIC IS
res VARCHAR2(45);
BEGIN
-- Range check "TO_NUMBER(ip, 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX') < 2**32, resp 2**128" not needed, because RAW values are usually not based on error-prone user input
IF ver = 4 THEN
-- Take only last 32 bit from RAW value with UTL_RAW.SUBSTR(ip, -4)
SELECT LISTAGG(TO_NUMBER(SUBSTR(SUBSTR(LPAD(RAWTOHEX(UTL_RAW.SUBSTR(ip, -4)), 8, '0'), -8), 2*LEVEL-1, 2), 'XX'), '.') WITHIN GROUP (ORDER BY LEVEL)
INTO res
FROM DUAL
CONNECT BY LEVEL <= 4;
RETURN res;
ELSIF ver = 6 THEN
RETURN Canonical_IPv6(SUBSTR(REGEXP_REPLACE(LPAD(RAWTOHEX(ip), 32, '0'), '([[:xdigit:]]{4})', ':1'), 2));
ELSE
RAISE VALUE_ERROR;
END IF;
END RAW2IP;
FUNCTION Decimal2IP(ip IN NUMBER, ver IN INTEGER) RETURN VARCHAR2 DETERMINISTIC IS
res VARCHAR2(45);
BEGIN
IF ip IS NULL THEN
RETURN NULL;
END IF;
IF ver = 4 THEN
IF ip > 2**32 - 1 THEN
RAISE NUMERIC_OVERFLOW;
END IF;
SELECT LISTAGG(TO_NUMBER(SUBSTR(LPAD(TO_CHAR(ip, 'fmXXXXXXXX'), 8, '0'), 2*LEVEL-1, 2), 'XX'), '.') WITHIN GROUP (ORDER BY LEVEL)
INTO res
FROM dual
CONNECT BY LEVEL <= 4;
RETURN res;
ELSIF ver = 6 THEN
IF ip > 2**128 - 1 THEN
RAISE NUMERIC_OVERFLOW;
END IF;
res := LPAD(TO_CHAR(ip, 'fmxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'),32, '0');
RETURN Canonical_IPv6(SUBSTR(REGEXP_REPLACE(res, '([[:xdigit:]]{4})', ':1'), 2));
ELSE
RAISE VALUE_ERROR;
END IF;
END Decimal2IP;
FUNCTION SubnetMask(Ip IN VARCHAR2) RETURN VARCHAR2 DETERMINISTIC IS
BEGIN
IF Ip IS NULL OR NOT REGEXP_LIKE(Ip, '/d{1,3}$') THEN
RETURN NULL;
END IF;
IF REGEXP_LIKE(Ip, ':') THEN
RETURN CIDR2SubnetMask(REGEXP_SUBSTR(Ip, 'd{1,3}$'), 6);
ELSE
RETURN CIDR2SubnetMask(REGEXP_SUBSTR(Ip, 'd{1,2}$'), 4);
END IF;
END SubnetMask;
FUNCTION NetworkPrefix(Ip IN VARCHAR2, SubnetMask IN VARCHAR2) RETURN VARCHAR2 DETERMINISTIC IS
BEGIN
IF REGEXP_LIKE(ip, ':') THEN
RETURN RAW2IP(UTL_RAW.BIT_AND(Ip2RAW(Ip, 6), Ip2RAW(SubnetMask, 6)), 6);
ELSE
RETURN RAW2IP(UTL_RAW.BIT_AND(Ip2RAW(Ip, 4),Ip2RAW(SubnetMask, 4)), 4);
END IF;
END NetworkPrefix;
FUNCTION NetworkPrefix(Ip IN VARCHAR2) RETURN VARCHAR2 DETERMINISTIC IS
BEGIN
RETURN NetworkPrefix(REGEXP_REPLACE(Ip, '/d{1,3}$'), SubnetMask(Ip));
END NetworkPrefix;
FUNCTION BroadcastIp(Ip IN VARCHAR2, SubnetMask IN VARCHAR2) RETURN VARCHAR2 DETERMINISTIC IS
Subnet RAW(16);
SubnetInv RAW(16);
BEGIN
IF REGEXP_LIKE(ip, ':') THEN
Subnet := UTL_RAW.BIT_AND(Ip2RAW(Ip, 6), Ip2RAW(SubnetMask, 6));
SubnetInv := UTL_RAW.BIT_COMPLEMENT(Ip2RAW(SubnetMask, 6));
RETURN RAW2IP(UTL_RAW.BIT_OR(Subnet, SubnetInv), 6);
ELSE
Subnet := UTL_RAW.BIT_AND(Ip2RAW(Ip, 4), Ip2RAW(SubnetMask, 4));
SubnetInv := UTL_RAW.BIT_COMPLEMENT(Ip2RAW(SubnetMask, 4));
RETURN RAW2IP(UTL_RAW.BIT_OR(Subnet, SubnetInv), 4);
END IF;
END BroadcastIp;
FUNCTION BroadcastIp(Ip IN VARCHAR2) RETURN VARCHAR2 DETERMINISTIC IS
BEGIN
RETURN BroadcastIp(REGEXP_REPLACE(Ip, '/d{1,3}$'), SubnetMask(Ip));
END BroadcastIp;
FUNCTION SubnetMask2CIDR(SubnetMask VARCHAR2) RETURN INTEGER RESULT_CACHE DETERMINISTIC IS
ip RAW(16);
cidr INTEGER;
BEGIN
IF SubnetMask IS NULL THEN
RETURN NULL;
END IF;
IF REGEXP_LIKE(SubnetMask, ':') THEN
ip := IP2RAW(SubnetMask, 6);
cidr := 128-LOG(2, TO_NUMBER(UTL_RAW.BIT_COMPLEMENT(ip), 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX')+1);
ELSE
ip := IP2RAW(SubnetMask, 4);
cidr := 32-LOG(2, TO_NUMBER(UTL_RAW.BIT_COMPLEMENT(ip), 'XXXXXXXX')+1);
END IF;
RETURN cidr;
END SubnetMask2CIDR;
FUNCTION CIDR2SubnetMask(CIDR IN INTEGER, ver IN INTEGER) RETURN VARCHAR2 RESULT_CACHE DETERMINISTIC IS
BEGIN
IF CIDR IS NULL THEN
RETURN NULL;
END IF;
IF ver = 4 THEN
IF CIDR NOT BETWEEN 0 AND 32 THEN
RAISE VALUE_ERROR;
END IF;
RETURN RAW2IP(UTL_RAW.BIT_COMPLEMENT(HEXTORAW(LPAD(TO_CHAR(2**(32-cidr)-1,
