Get IP Address range from xxx.xxx.xx.0/16

Question

Good day All,

Is there a way to get the range of an IP addresses from the format 66.54.119.192/29

in Oracle pl/sql to something like 66.54.119.192 - 66.54.119.198

Thanks for any assistance offered.

Answer 1

There is not direct way to get it. First you need to split IP into 4 octets, then you have to convert from Binary -> Decimal and vice versa.

Some time ago I created a Package for such task. Below you see just the package body, it should provide everything to solve your problem.

CREATE OR REPLACE TYPE NUMBER_TABLE_TYPE AS TABLE OF NUMBER;


CREATE OR REPLACE PACKAGE BODY IP_Utility AS

    BASE_BIN CONSTANT PLS_INTEGER := 2;
    BASE_OCT CONSTANT PLS_INTEGER := 8;
    BASE_DEC CONSTANT PLS_INTEGER := 10;
    BASE_HEX CONSTANT PLS_INTEGER := 16;

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

/**
* Function translate a array to PL/SQL Table
* @param Sperator String that separates elements, e.g. ';'
* @return Table of elements (NUMBER)
*/
FUNCTION SplitNumber(LIST IN VARCHAR2, Separator IN VARCHAR2) RETURN NUMBER_TABLE_TYPE IS
    OutTable NUMBER_TABLE_TYPE; 
BEGIN    
    IF LIST IS NULL THEN
        RETURN NULL;
    ELSE
        SELECT TRIM(REGEXP_SUBSTR(LIST, '[^'||Separator||']+', 1, LEVEL)) 
        BULK COLLECT INTO OutTable
        FROM dual
        CONNECT BY REGEXP_SUBSTR(LIST, '[^'||Separator||']+', 1, LEVEL) IS NOT NULL;
    END IF;

    IF OutTable.COUNT > 0 THEN
        RETURN OutTable;
    ELSE
        RETURN NULL;
    END IF;     
END SplitNumber;



/**
* Convert a decimal nubmer into a binary/octal/hex string 
* @param DecN Integer decimal number
* @param Base The binary base (BASE_BIN, BASE_OCT, BASE_HEX)  
* @return The binary/octal/hex string
*/  
FUNCTION Dec2Base(DecN IN INTEGER, Base IN PLS_INTEGER DEFAULT BASE_HEX) RETURN VARCHAR2 DETERMINISTIC IS
    HexString CONSTANT CHAR(16) := '0123456789ABCDEF';
    DecNumber INTEGER := DecN;
    BaseString VARCHAR2(128) := NULL;
BEGIN
    IF DecN IS NULL THEN
        RETURN NULL;
    END IF;
    IF Base > 16 THEN 
        RAISE NUMERIC_OVERFLOW;
    END IF;
    LOOP
        BaseString := SUBSTR(HexString, MOD(DecNumber, Base) + 1, 1 ) || BaseString;
        DecNumber := TRUNC(DecNumber / Base);
        EXIT WHEN DecNumber = 0;
    END LOOP;
    RETURN BaseString;
END Dec2Base;


/**
* Convert a binary/octal/hex number into a decimal value 
* @param BaseString The binary/octal/hex string
* @param Base The binary base (BASE_BIN, BASE_OCT, BASE_HEX)  
* @return The decimal number    
*/
FUNCTION Base2Dec(BaseString IN VARCHAR2, Base IN PLS_INTEGER DEFAULT BASE_HEX) RETURN INTEGER DETERMINISTIC IS
    BaseNumber INTEGER := 0;
    HexString CONSTANT CHAR(16) := '0123456789ABCDEF';
BEGIN
    IF Base > 16 THEN 
        RAISE NUMERIC_OVERFLOW;
    END IF;
    IF BaseString IS NULL THEN
        RETURN NULL;
    END IF;
    FOR i IN 1..LENGTH(BaseString) LOOP
        BaseNumber := BaseNumber * Base + INSTR(HexString, UPPER(SUBSTR(BaseString, i, 1))) - 1;
    END LOOP;
    RETURN BaseNumber;
END Base2Dec;



/**
* Returns SubnetMask of given IP Address
* @param Ip IP-Address with CIDR, e.g. '10.152.10.17/27'
* @return SubnetMask The Subnet Mask in IPv4 Format, e.g. '255.255.255.224'
*/
FUNCTION GetSubnetMask(Ip IN VARCHAR2) RETURN VARCHAR2 DETERMINISTIC IS
    SubnetMask VARCHAR2(16);
    MaskBin VARCHAR2(32);
BEGIN
    IF Ip IS NULL OR NOT REGEXP_LIKE(Ip, '/\d+$') THEN
        RETURN NULL;
    END IF;
    FOR i IN 1..REGEXP_REPLACE(Ip, '.+/') LOOP
        MaskBin := MaskBin || '1';
    END LOOP;
    MaskBin := RPAD(MaskBin, 32, '0');
    FOR i IN 1..4 LOOP
        SubnetMask := SubnetMask ||'.'||Base2Dec(SUBSTR(MaskBin, 8*(i-1)+1, 8 ), BASE_BIN);
    END LOOP;
    SubnetMask := SUBSTR(SubnetMask, 2);
    RETURN SubnetMask;  
END GetSubnetMask;



/**
* Returns Subnet and Broadcast-IP of given IP Address  
* @param Ip IP-Address, e.g. 10.152.10.17
* @param SubnetMask The SubnetMask, e.g. 255.255.255.240  
* @param Subnet Subnet-IP: e.g. 10.152.10.16  
* @param BroadcastIp Broadcast-IP: e.g. 10.152.10.31
*/
PROCEDURE GetIpSubnet(Ip IN VARCHAR2, SubnetMask IN VARCHAR2, Subnet OUT VARCHAR2, BroadcastIp OUT VARCHAR2) IS
    SubnetBin VARCHAR2(8);
    BroadcastBin VARCHAR2(8);
    Ip_Array NUMBER_TABLE_TYPE;
    Mask_Array NUMBER_TABLE_TYPE;
BEGIN
    IF SubnetMask IS NULL OR Ip IS NULL THEN
        RETURN;
    END IF;
    Ip_Array := SplitNumber(Ip, '.');
    Mask_Array := SplitNumber(SubnetMask, '.');
    FOR i IN 1..4 LOOP
        SubnetBin := NULL;
        BroadcastBin := NULL;
        FOR m IN 1..8 LOOP
            IF SUBSTR(LPAD(Dec2Base(Ip_Array(i), BASE_BIN), 8, '0'), m, 1) = 1 
                AND SUBSTR(LPAD(Dec2Base(Mask_Array(i), BASE_BIN), 8, '0'), m, 1) = 1 THEN
                SubnetBin := SubnetBin ||'1';
            ELSE
                SubnetBin := SubnetBin ||'0';
            END IF;         
            IF SUBSTR(LPAD(Dec2Base(Mask_Array(i), BASE_BIN), 8, '0'), m, 1) = 1 THEN
                BroadcastBin := SubnetBin;
            ELSE
                BroadcastBin := BroadcastBin ||'1';
            END IF;         
        END LOOP;
        Subnet := Subnet ||'.'||Base2Dec(SubnetBin, BASE_BIN);
        BroadcastIp := BroadcastIp ||'.'||Base2Dec(BroadcastBin, BASE_BIN);
    END LOOP;
    Subnet := SUBSTR(Subnet, 2);
    BroadcastIp := SUBSTR(BroadcastIp, 2);  
END GetIpSubnet;




END IP_Utility;
/

Or see an advanced version of this package at oracle PL/SQL how to calculate range ip for IPv6 cidr