n rows please
Posted by anotherdavewebster on August 9, 2010
Really nice and easy one to get us started. Say you want to generate ten thousand rows of data. The way I normally see this kind of thing done is by selecting values from dba_objects where rownum <= 10000. There’s nothing wrong with this approach of course but what if you need more rows than you have objects in the database? You can do some sort of cartesian product thing but even suggesting that is making me feel a little unclean. I prefer:
SELECT NULL FROM DUAL CONNECT BY ROWNUM <= 10000;
It’s simple, unlimited and even a little cheaper to boot.
The first time I actually needed this, I was trying to generate all of the seconds between two times:
SELECT to_date('01/08/2010 14:26:09','DD/MM/YYYY HH24:MI:SS') /* low */ + ( rownum / 86400 ) the_date FROM DUAL CONNECT BY ROWNUM <= ( to_date('01/08/2010 14:28:29','DD/MM/YYYY HH24:MI:SS') /* high */ - to_date('01/08/2010 14:26:09','DD/MM/YYYY HH24:MI:SS') /* low */ ) * 86400
First one down. That wasn’t too hard was it?