SQL-selectInsertMissingRangeRecord

create-ip-to-location.sql

CREATE TABLE IP_TO_LOCATION (
	"IP_FROM" NUMBER NOT NULL,
	"IP_TO" NUMBER NOT NULL,
	"COUNTRY_CODE" VARCHAR2(3 CHAR) NOT NULL,
	"COUNTRY_NAME" VARCHAR2(64 CHAR) NOT NULL,
	"TYPE" VARCHAR2(2 CHAR),
	"CHANGE_TS" TIMESTAMP (6) DEFAULT systimestamp,
	
	CONSTRAINT IP_TO_LOCATION_PK PRIMARY KEY ("IP_FROM", "IP_TO", "TYPE") 
)

insertMissingRange.sql

SELECT 'INSERT INTO IP_TO_LOCATION (IP_FROM, IP_TO, COUNTRY_CODE, COUNTRY_NAME, TYPE, CHANGE_TS) VALUES (''' || FROM_IP || ''', ''' || TO_IP || ''', ''GB'', ''DUMMY UK'', ''v4'', SYSTIMESTAMP);' FROM (
	SELECT IP_FROM, IP_TO,
	IP_TO + 1 AS FROM_IP, NVL((SELECT MIN(IP_FROM) - 1 FROM IP_TO_LOCATION t3 WHERE TYPE = 'v4' AND t1.IP_TO < t3.IP_FROM), 4294967295) AS TO_IP
	FROM IP_TO_LOCATION t1
	WHERE TYPE = 'v4'
	AND NOT EXISTS (
		SELECT 1
		FROM IP_TO_LOCATION t2
		WHERE TYPE = 'v4'
		AND t2.IP_FROM = t1.IP_TO + 1
	)
)
ORDER BY FROM_IP
;