Tuesday, April 08, 2008

I'm an advocate of testing things - it can be a great way to discover different behaviors and learn how things work (or perhaps why they didn't work the way you thought they might!). When it comes to testing database applications, having some test data is certainly helpful. Ideally, I like to have a fully-loaded production database from which to draw this data into a test environment. Sometimes, however, this is not possible. In cases like these I use the "trick" described below to create my own test data.

When I need to generate test data I frequently call upon a seemingly little-known PL/SQL Package supplied with Oracle Database called "dbms_random". As you may guess this package can be used to generated random data. Rather than explain the package details (they are short and you can read them using the link), I'll just present a quick way to generate some data using SQL*Plus and the dbms_random package.

As you can see, this table is nothing to write home about, but it does mirror what a real-world table would look like in a lot of cases.

When I am creating test data I generally prefer the data to be reproducible. That is, if I execute the process more than once I like to get the same data each time. To accomplish this with the dbms_random package I call the "seed" procedure and provide an initial seed value:

exec dbms_random.seed(42);

Let's say I wanted to populate this table with 10,000 rows of data. I use the following to do this:

Starting at the bottom of the SQL text is a neat trick using "dual" and "connect by" to essentially create an "unlimited dual" for generating lots of rows. This trick was (I believe) originally put forth by Mikito Harakiri and I discovered it via an Ask Tom thread.

The remainder of the text is fairly straight-forward in its use of the dbms_random package. I use the "string" function to generate a random, lowercase value (which is subsequently passed to "initcap" to capitalize the first character of each string), the "value" function is used to create a random numeric value (which is passed to "round" to make it look like a purchase amount), and then I use a fixed-date to which I add (possibly a negative value) to create a set of valid dates within a range.

The first 10 rows of this data when selected from the table look like:

Can this technique always be used? No, probably not. For example, the names are not exactly what you might call "proper" names. However, I do find that this technique can be useful when I need to create some data to perform some testing with. Perhaps it will be helpful to you too if you experiment with it and find the right combination of values to use in your tests.

About Me

I'm an Oracle Software Developer with DBA experience. I authored the ODP.NET column in Oracle Magazine and the Apress book "Pro .NET Oracle Programming". I'm also a former Oracle ACE Director which means I tend to advocate Oracle and try to help people with it. This site may or may not have (meaningful) content. Sometimes I am a perfectionist. I reserve the right to edit and/or delete comments. Personal blog that does not represent any company. I bet you knew that. Married to a serial tea drinker.

Disclaimer

The content expressed here is my own and does not necessarily reflect that of Oracle Corporation,
its affliates or clients, or anyone else for that matter. The content expressed by
visitors is their own. The content on this site is not guaranteed to be fit for any purpose
and no warranty of any kind is expressed or implied. If you choose to use any content from
this site, it is at your own risk.

Always use a non-production system to assess the
suitability of any content from this site in your environment.