Often in different tasks I need array of integers which does not have any "holes" (for example 1, 2, 3, 4, etc.). Or part of calendar for some period as array of dates.
Usually tables with such arrays are created in database and are used in queries.
But Oracle provides an opportunity to get uninterrupted arrays "on the fly" using hierarchical queries.
To get uninterrupted array of integer, for example, from 1 to 10 you may use the following
SELECT LEVEL FROM dual CONNECT BY LEVEL <= 10;
To get part of calendar, for example, from 10 Jan 2012 to 5 Feb 2012 you may use the following
SELECT (DATE '2012-01-10' + LEVEL - 1) AS dt FROM dual CONNECT BY (DATE '2012-01-10' + LEVEL - 1) <= DATE '2012-02-05';
Code snippets have been tested on Oracle Database 11g Release 22.214.171.124.0