René Nyffenegger's collection of things on the web  
René Nyffenegger on Oracle  Most wanted  Feedback
 Follow @renenyffenegger

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 withselect 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.
