Friday, January 30, 2009

Create some dynamic data

When you are developing an application with a new database design (or an existing database design, but you didn't get any data), it can be handy to quickly create some test data in your tables.

Sure you can create some data manual to test your input forms, but what if you need more data? Or you want to test the performance with a large amounts of records.

Here's a simple select statement that creates you 25.000 random numbers and strings (you can change the number to whatever number you like):

SELECT rownum RNUM
, ABS(MOD(dbms_random.random,100000)) RANDOM_NBR
, dbms_random.string('A', 20) RANDOM_STR
FROM dual
CONNECT BY level <= 25000

You could also create a little procedure that populates your tables with it as you probably have some relationships going on as well (PK-FK). I just needed a select that generates me huge amount of records to test some charts in APEX with.

Update (thanks to Rob - see comments): dbms_random should not be used anymore, see the 10g and 11g documentation. So the select statement from 10g onwards would be:

SELECT rownum RNUM
, trunc(dbms_random.value(1,100000)) RANDOM_NBR
, dbms_random.string('A', 20) RANDOM_STR
FROM dual
CONNECT BY level <= 25000

3 comments:

Rob van Wijk said...

Hi Dimitri,

From the 10.2 documentation of dbms_random.random:

"Usage Notes

This procedure is obsolete and, although it is currently supported, it should not be used."

An alternative is using trunc(dbms_random.value(1,100000))

Regards,
Rob.

Dimitri Gielis said...

Rob thank you for that.
I updated the post with your comment.

Dimitri

Byte64 said...

Hi Dimitri,
thanks for the tip!
See also this old posting of mine about a curious bug involving the CONNECT BY clause in conjuction with DUAL on Oracle RDBMS prior to 10g.

Cheers!
Flavio