REM
/*
 * RowsourceGenerator.Sql (c) Tim Onions 2008
 * Please check this script works as expected on a test system before using on a production database.
 * The author can accept no liability or responsibility for any ill-effect it may have.
 * You are free to use this script on one condition - use it to improve the performance of your database!
 */

UNDEF NumberofRowsRequired
SELECT sysdate+level AS rown
   FROM dual
 CONNECT BY level<=&NumberofRowsRequired
/
UNDEF NumberofRowsRequired
WITH rowsource AS
(SELECT level AS rown
   FROM dual
 CONNECT BY level<=&NumberofRowsRequired
)
SELECT sysdate+rown
  FROM rowsource
/

/* Rowsource generator - there are occasions where in SQL you wish to generate nn rows (for instance you want to list the dates of the next 28 days).
   It is easy in PL/SQL as you can just set-up a loop. In SQL traditionally coders have used large tables, such as ALL_OBJECTS, and
   limited the amount of data via restricting on ROWNUM. eg SELECT rownum FROM all_objects WHERE rownum<=nn;

   However, you can achieve the same result much more efficiently by using the smallest table there is, DUAL, even though it has just one row!
   The trick is to perform a CONNECT BY loop bounded only by LEVEL i.e.:

 SELECT level AS rown
   FROM dual
CONNECT BY level<=&NumberofRowsRequired;

   Sometimes, however, SQL*Plus gets too clever and works out that you are querying the DUAL table and that the table only has one row and so
   ignores the CONNECT BY and assumes only one row would be returned. To overcome this it is safest to put the rowsource generator in a WITH statement e.g.:
   
WITH rowsource AS
(SELECT level AS rown
   FROM dual
CONNECT BY level<=&NumberofRowsRequired
)
SELECT rown
  FROM rowsource;

   Sample output for each type of query might look like:

SCOTT@RACDB> start rowsourcegenerator
Enter value for numberofrowsrequired: 5

     ROWN
---------
        1
        2
        3
        4
        5

Enter value for numberofrowsrequired: 6

     ROWN
---------
        1
        2
        3
        4
        5
        6

6 rows selected.

*/

REM