CLEAR
#DEFINE RANGESTART 	  1
#DEFINE RANGEEND 	100

CREATE CURSOR TWITHHOLES (F1 INT NULL)

INSERT INTO TWITHHOLES VALUES (3)
INSERT INTO TWITHHOLES VALUES (4)
INSERT INTO TWITHHOLES VALUES (5)
INSERT INTO TWITHHOLES VALUES (8)
INSERT INTO TWITHHOLES VALUES (9)
INSERT INTO TWITHHOLES VALUES (11)
INSERT INTO TWITHHOLES VALUES (15)
INSERT INTO TWITHHOLES VALUES (NULL)

findHoles()
BROWSE

INSERT INTO TWITHHOLES VALUES (1)
INSERT INTO TWITHHOLES VALUES (100)

findHoles()

BROWSE

FUNCTION findHoles

	SELECT	NVL(T1.F1+1	,RANGESTART)	holeBegin		;
	,	NVL(MIN(T2.F1)-1,RANGEEND)	holeEnd			;
	INTO CURSOR THOLES						;
	FROM	(TWITHHOLES T1 LEFT JOIN TWITHHOLES T3 ON T3.F1=T1.F1+1);
		FULL	JOIN						;
		(TWITHHOLES T2 LEFT JOIN TWITHHOLES T4 ON T4.F1=T2.F1-1);
		ON T2.F1>T1.F1+1					;
	WHERE T3.F1 IS NULL AND T4.F1 IS NULL				;
	GROUP BY T1.F1							;
	HAVING NVL(T1.F1+1,RANGESTART)<=holeEnd