Dave Webster's Blog

Tinkering with Oracle

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?

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: