Here is a code that shows that holes using as example a table named my_table and a column id
SELECT id,
next_id,
DECODE (next_id, id + 1, '',
DECODE (id + 1, next_id - 1, TO_CHAR (id + 1),
TO_CHAR (id + 1)
|| '-'
|| TO_CHAR (next_id - 1)))
missing_values
FROM (SELECT id,
LEAD (id, 1) over (ORDER BY id) AS next_id
FROM my_table)
WHERE next_id IS NOT NULL
AND DECODE (next_id, id + 1, '',
DECODE (id + 1, next_id - 1, TO_CHAR (id + 1),
TO_CHAR (id + 1)
|| '-'
|| TO_CHAR (next_id - 1))) IS NOT NULLAn example of the output:
22 42 23-41 42 44 43 44 46 45 46 62 47-61 62 64 63 64 68 65-67
