Cute ROWNUM trick


Can't remember where I saw this (so if you're responsible please let me know and I'll post an acknowledgement).

If you need an arbritrary number of rows for generating test data, you can (believe it or not) just use DUAL

SELECT ROWNUM
FROM DUAL
CONNECT BY ROWNUM<=1000000;

or if you need random numbers, just simply use

SELECT DBMS_RANDOM.VALUE
FROM DUAL
CONNECT BY ROWNUM<=1000000;

Update

Numerous people mailed in to say that Mikito Harakiri deserves the credit on this one. Also, thanks to Di Luo for pointing out that under some versions the SQL will not work - you need to wrap within an inline view due to a bug in Oracle.

select r from (
SELECT ROWNUM r
FROM DUAL
CONNECT BY ROWNUM<=1000000 )