Skip to content

Instantly share code, notes, and snippets.

@kyip-dev
Created October 22, 2015 08:50
Show Gist options
  • Save kyip-dev/d38cd711eae0def4105e to your computer and use it in GitHub Desktop.
Save kyip-dev/d38cd711eae0def4105e to your computer and use it in GitHub Desktop.
SQL-selectInsertMissingRangeRecord
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")
)
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
;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment