How to get uninterrupted array of integers or dates using Oracle hierarchical queries

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 11.2.0.1.0