René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

Generating an arbitrary amount of rows with Oracle SQL

With a cross join

I find 52176 rows in my all_objects: 
select count(*) from all_objects;

52176
So, I can generate up to 52176 rows using all_objects: 
select count(*), count(distinct r), max(r), min(r) from (
  select rownum r
    from all_objects 
   where rownum <= 50000
);

  COUNT(*) COUNT(DISTINCTR)     MAX(R)     MIN(R)
---------- ---------------- ---------- ----------
     50000            50000      50000          1

select count(*), count(distinct r), max(r), min(r) from (
  select rownum r
    from all_objects 
   where rownum <= 60000
);

  COUNT(*) COUNT(DISTINCTR)     MAX(R)     MIN(R)
---------- ---------------- ---------- ----------
     52176            52176      52176          1
If I need more than 52176 (this figure varies depending on how many objects were created in Oracle), a cross join helps. With such a cross join, I can now generate up to 52176*52176 (=2722334976) rows: 
select count(*), count(distinct r), max(r), min(r) from (
  select rownum r from 
    all_objects a cross join all_objects b
  where rownum <= 1000000
);

  COUNT(*) COUNT(DISTINCTR)     MAX(R)     MIN(R)
---------- ---------------- ---------- ----------
   1000000          1000000    1000000          1

With connect by without start with

select rownum
  from dual
  connect by rownum <= 14;

    ROWNUM
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10
        11
        12
        13
        14
BTW, Laurent Schneider does not like this approach while Tom Kyte does.

With the model clause

It is also possible to generate rows with the model clause. See generating dates with the model clause.