Representing IPv4/IPv6 addresses in Oracle
In Oracle, what is the appropriate
data type or technique for
representing network addresses, which
addresses may be IPv4 or IPv6
There are two approaches :
- storing only.
- storing the conventional representation
For storing only. An IPV4 address should be an integer (32bits are enough). For IP V6, 128 bits, INTEGER (which is similar to Number(38)) will do. Of course, that's storing. That approach takes the view that the representation is a matter for the application.
If one take the opposite strategy, of storing the conventional representation, one needs to make sure that IP V4 and IPV6 addresses have only one conventional (string) representation. It's well-known for ipV4. As for IPV6, there is also a standard format.
My preference goes to the first strategy. In the worst case, you can adopt an hybrid approach (non acid though) and store both the binary and the ascii representation side by side with "priority" to the binary value.
No row contains both v4 and v6
addresses, however.
The standard representation of a IPV4 address in IPV6 format is : ::ffff:192.0.2.128.
I don't know the context but I would however reserve 2 columns, one for IPV4 and the other for a distinct ipV6 address.
Update
Following a good comment by @sleepyMonad's, I'd like to point out that instead of the Number data type it is preferable to use the INTEGER data type, which will happily accommodate the highest possible value that can be expressed with a 128 bits integer 'ff...ff' (which would need 39 decimal digits). 38 is the highest power of ten ranging from 0 to 9 that can be encoded on 128 bits but one can still insert the maximum unsigned value for 2**128 - 1 (decimal 340282366920938463463374607431768211455). Here is a small test to illustrate this possibility.
create table test (
id integer primary key,
ipv6_address_bin INTEGER );
-- Let's enter 2**128 - 1 in the nueric field
insert into test (id, ipv6_address_bin) values ( 1, to_number ( 'FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF', 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX') ) ;
-- retrieve it to make sure it's not "truncated".
select to_char ( ipv6_address_bin, 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX' ) from test where id = 1 ;
-- yields 'FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF'
select to_char ( ipv6_address_bin ) from test where id = 1 ;
-- yields 340282366920938463463374607431768211455
select LOG(2, ipv6_address_bin) from test where id = 1 ;
-- yields 128
select LOG(10, ipv6_address_bin) from test where id = 1 ;
-- yields > 38
Split and Sequence String - Oracle SQL
Sure. Here's the simple version, which gets you col1. This works a lot better if you use the splitting regexp substr from this question.
with t as (select 'PPID|1||123456789^^^^VV||PIZZA^KEVIN^^^^^L||98765432||' as str from dual)
select regexp_substr(t.str,'(.*?)(\||\^|$)', 1, level, null, 1) col1
from t
connect by level <= regexp_count(t.str, '(.*?)(\||\^|$)');
Adding your second column creates some significant complexity. There's probably a graceful way to do it by joining two hierarchical queries, but I can't do that well, so I just used some analytic functions.
with t as (select 'PPID|1||123456789^^^^VV||PIZZA^KEVIN^^^^^L||98765432||' as str from dual)
select col1,
'PID'
-- count pipes seen so far
|| trim(to_char(nvl(sum(case when sep = '|' then 1 else 0 end)
over (order by lev rows between unbounded preceding and 1 preceding)
,0)
,'00'))
-- count hats (within a partition defined by the number of pipes seen so far)
|| CASE when sep = '^' or lag(sep) over (order by lev) = '^' THEN
'-' || trim(to_char(row_number() over (partition by regexp_count(seen, '\|')
order by lev) - 1, '00'))
ELSE null end as col2
from (
select regexp_substr(t.str,'(.*?)(\||\^|$)', 1, level, null, 1) col1,
regexp_substr(t.str,'(.*?)(\||\^|$)', 1, level, null, 2) sep,
level as lev,
substr(t.str,1,regexp_instr(t.str,'(.*?)(\||\^|$)', 1, level, 0)) as seen
from t
connect by level <= regexp_count(t.str, '(.*?)(\||\^|$)')
) s
;
Output:
col1 col2
PPID PID00
1 PID01
PID02
123456789 PID03-01
PID03-02
PID03-03
PID03-04
VV PID03-05
PID04
PIZZA PID05-01
KEVIN PID05-02
PID05-03
PID05-04
PID05-05
PID05-06
L PID05-07
PID06
98765432 PID07
PID08
PID09
Let me know if you have any questions.
EDIT: Well, regexp_substr and hierarchical queries are both pretty slow. I rewrote it using MT0's recursive CTE no-regex answer on this question. It's still pretty sloppy, I'm sure it could be cleaned up.
WITH ex as (select 'PPID|1||123456789^^^^VV||PIZZA^KEVIN^^^^^L||98765432||' as str from dual),
t ( str, start_pos, end_pos ) AS
( SELECT str, 1, LEAST(INSTR(str, '|'),INSTR(str, '^')) FROM ex
UNION ALL
SELECT str,
end_pos + 1,
CASE WHEN INSTR(str, '|', end_pos + 1) > 0 and INSTR(str, '^', end_pos + 1) > 0 THEN
LEAST(INSTR(str, '|', end_pos + 1),INSTR(str, '^', end_pos + 1))
ELSE GREATEST(INSTR(str, '|', end_pos + 1),INSTR(str, '^', end_pos + 1)) END
FROM t
WHERE end_pos > 0
)
select col1,
'PID'
-- count pipes
|| trim(to_char(nvl(sum(case when rsep = '|' then 1 else 0 end)
over (order by start_pos rows between unbounded preceding and 1 preceding)
,0)
,'00'))
-- count hats
|| CASE when '^' in (lsep,rsep) THEN
'-' || trim(to_char(row_number() over (partition by (length(seen)-length(replace(seen, '|')))
order by start_pos), '00'))
ELSE null end
as col_seq
from (
SELECT str, start_pos, end_pos,
SUBSTR( str, start_pos, DECODE( end_pos, 0, LENGTH(str) + 1, end_pos ) - start_pos ) AS col1,
SUBSTR( str, start_pos-1, 1) as lsep, SUBSTR(str, DECODE( end_pos, 0, LENGTH(str) + 1, end_pos ), 1) as rsep,
SUBSTR( str, 1, DECODE( end_pos, 0, LENGTH(str) + 1, end_pos )-1 ) as seen
FROM t) s
order by start_pos;
How to split comma-separated values into multiple rows in Oracle table
Step 1 : "How to blow up a database"
From :
SQL Fiddle
Oracle 11g R2 Schema Setup:
Query 1:
select * from films11
Results:
| YEAR | DIRECTOR | MOVIETITLE | ACTORNAME |
|------|----------|------------|----------------|
| 2000 | dir1 | title1 | act1,act2 |
| 2001 | dir2 | title2 | act1,act2,act3 |
| 2002 | dir1 | title3 | act4 |
Query 2:
select YT.year, YT.movietitle,
REPLACE(REGEXP_SUBSTR(YT.actorname||',','.*?,',1,lvl.lvl),',','') AS actorname
from films11 YT
join (select level as lvl
from dual
connect by level <= (select max(regexp_count(actorname,',')+1) from films11)
) lvl on lvl.lvl <= regexp_count(YT.actorname,',')+1
order by YT.year, YT.movietitle, actorname
With a nice Cartesian product :
Results:
| YEAR | MOVIETITLE | ACTORNAME |
|------|------------|-----------|
| 2000 | title1 | act1 |
| 2000 | title1 | act2 |
| 2001 | title2 | act1 |
| 2001 | title2 | act2 |
| 2001 | title2 | act3 |
| 2002 | title3 | act4 |
You run it ONCE and use it to move everything to a normalized DB
Here is the full script to change your schema to something more convenient...
CREATE TABLE actors(
id_actor NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY,
act_name VARCHAR2(100)
)
;
CREATE TABLE directors(
id_director NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY,
dir_name VARCHAR2(100)
)
;
CREATE TABLE movies(
id_movie NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY,
mov_year NUMBER,
mov_name VARCHAR2(100),
director_id NUMBER
)
;
CREATE TABLE playedby(
movie_id NUMBER,
actor_id NUMBER
)
;
INSERT INTO directors (dir_name)
SELECT DISTINCT director dir_name
FROM films11
;
INSERT INTO movies (mov_year, mov_name, director_id)
SELECT year mov_year, movietitle mov_name, directors.id_director director_id
FROM films11
INNER JOIN directors ON directors.dir_name = films11.director
;
INSERT INTO actors (act_name)
SELECT DISTINCT t.actorname act_name
FROM (
SELECT YT.year, YT.movietitle,
REPLACE(REGEXP_SUBSTR(YT.actorname||',','.*?,',1,lvl.lvl),',','') AS actorname
FROM films11 YT
JOIN (SELECT level AS lvl
FROM dual
CONNECT BY level <= (SELECT MAX(REGEXP_COUNT(actorname,',')+1) FROM films11)
) lvl ON lvl.lvl <= REGEXP_COUNT(YT.actorname,',')+1
) t
;
INSERT INTO playedby (movie_id, actor_id)
SELECT movies.id_movie movie_id, actors.id_actor actor_id
FROM (
SELECT YT.year, YT.movietitle,
REPLACE(REGEXP_SUBSTR(YT.actorname||',','.*?,',1,lvl.lvl),',','') AS actorname
FROM films11 YT
JOIN (SELECT level AS lvl
FROM dual
CONNECT BY level <= (SELECT MAX(REGEXP_COUNT(actorname,',')+1) FROM films11)
) lvl ON lvl.lvl <= REGEXP_COUNT(YT.actorname,',')+1
) t
INNER JOIN actors ON t.actorname = actors.act_name
INNER JOIN movies ON t.year = movies.mov_year AND t.movietitle = movies.mov_name
;
After that you can just make a select like that :
Query 3:
SELECT mov_year, mov_name, dir_name, act_name
FROM movies
INNER JOIN directors ON directors.id_director = movies.director_id
INNER JOIN playedby ON movies.id_movie = playedby.movie_id
INNER JOIN actors ON playedby.actor_id = actors.id_actor
WHERE act_name like '%act2%'
order by mov_year asc
Results:
| MOV_YEAR | MOV_NAME | DIR_NAME | ACT_NAME |
|----------|----------|----------|----------|
| 2000 | title1 | dir1 | act2 |
| 2001 | title2 | dir2 | act2 |
Get IP Address range from xxx.xxx.xx.0/16
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
oracle PL/SQL how to calculate range ip for IPv6 cidr
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, 'fmXXXXXXXX'),8 , '0'))), 4);
ELSIF ver = 6 THEN
IF CIDR NOT BETWEEN 0 AND 128 THEN
RAISE VALUE_ERROR;
END IF;
RETURN RAW2IP(UTL_RAW.BIT_COMPLEMENT(HEXTORAW(LPAD(TO_CHAR(2**(128-cidr)-1, 'fmXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'),32 , '0'))), 6);
ELSE
RAISE VALUE_ERROR;
END IF;
END CIDR2SubnetMask;
FUNCTION UncompressIpV6(Ip IN VARCHAR2, leadingZero IN BOOLEAN DEFAULT TRUE) RETURN VARCHAR2 DETERMINISTIC IS
IpFull VARCHAR2(50);
len INTEGER := 7;
TYPE VARCHAR_TABLE_TYPE IS TABLE OF VARCHAR2(10);
BitFields VARCHAR_TABLE_TYPE;
cidr VARCHAR2(5);
BEGIN
IF NOT REGEXP_LIKE(Ip, ':') THEN
RETURN Ip;
END IF;
cidr := REGEXP_SUBSTR(Ip, '/\d{1,3}$');
IpFull := REGEXP_REPLACE(Ip, '/\d{1,3}$');
IF REGEXP_LIKE(IpFull, '::') THEN
IpFull := REGEXP_REPLACE(REGEXP_REPLACE(IpFull, '^::', '0::'), '::$', '::0');
IF REGEXP_LIKE(IpFull, ':\d+\.\d+\.\d+\.\d+') THEN
-- Mixed notation, e.g.: 2002::FFFF:129.144.52.38
len := 6;
END IF;
WHILE REGEXP_COUNT(IpFull, ':') <= len LOOP
IpFull := REGEXP_REPLACE(IpFull, '::', ':0::');
END LOOP;
IpFull := REGEXP_REPLACE(IpFull, '::', ':');
END IF;
IF NOT leadingZero THEN
RETURN LOWER(IpFull||cidr);
END IF;
SELECT REGEXP_SUBSTR(IpFull, '[^:]+', 1, LEVEL)
BULK COLLECT INTO BitFields
FROM dual
CONNECT BY REGEXP_SUBSTR(IpFull, '[^:]+', 1, LEVEL) IS NOT NULL;
IpFull := LPAD(BitFields(1), 4, '0');
FOR i IN 2..BitFields.COUNT LOOP
IF REGEXP_LIKE(BitFields(i), '\d+\.\d+\.\d+\.\d+') THEN
IpFull := IpFull ||':'||BitFields(i);
ELSE
IpFull := IpFull ||':'||LPAD(BitFields(i), 4, '0');
END IF;
END LOOP;
RETURN LOWER(IpFull)||cidr;
END UncompressIpV6;
FUNCTION Canonical_IPv6(IPv6 IN VARCHAR2) RETURN VARCHAR2 DETERMINISTIC IS
res VARCHAR2(50);
cidr VARCHAR2(5);
BEGIN
IF NOT REGEXP_LIKE(IPv6, ':') THEN
RETURN IPv6;
ELSIF REGEXP_LIKE(IPv6, '::') THEN
-- Do not shorten twice
res := UncompressIpV6(IPv6, FALSE);
ELSE
-- RFC 5952 section-4.3
res := LOWER(IPv6);
END IF;
Related Topics
Getting Unavailable Dates for Renting a Product That Has Stocks
Bulk Update Multiple Rows in Same Query Using Postgresql
Split Ipv4 Address into 4 Numbers in Oracle SQL
SQL Select Where Column Begins with \
Sql: Filter Rows with Max Value
Count Number of Not Null Fields Within a Row
Get Count of Items and Their Values in One Column
SQL Trigger Cannot Do Instead of Delete But Is Required for Ntext, Image Columns
Deleting Value Using SQLite While Doing an Inner Join
SQL 2 Counts with Different Filter
Run Multiple Commands in SQLite Manager
Select First Record If None Match
Mysql: Union of a Left Join with a Right Join
Compare Deleted and Inserted Table in SQL Server 2008