Getting Started With Oracle Week: Generating Test Data

Bake your own cake

Pre-cooked example databases are cool for a lot of things. The first being that everyone can have access to them, so they can follow along with your demos without building tables or inserting a bunch of data. If you mess something up, it’s easy to restore a copy. The main problem is that they were usually designed by someone who didn’t have your issues.

Most of the time it only takes a table or two to prove your point, you just need to cook up some data that doesn’t have anyone’s real information in there. With Oracle, you have a couple different options.

SQL Server-ish

Baked into every Oracle table I’ve queried thus far, are two intrinsic columns: ROWID and ROWNUM. The ROWNUM column, at least, gives us the ability to skip over generating sequential numbers with the ROW_NUMBER function. The ROWID column is a confusing string of nonsense.

To die for

Have you ever wished you could create a table… Let’s say a demo table! And it since it’s of no consequence, SQL just wouldn’t log any changes to it? Or even that it ever existed? I mean, we have minimal logging, but what if we wanted no logging at all? Minimal logging doesn’t always work, and requires a few pre-requisites, and, well, you get the idea. If it’s high tide on St. Patrick’s Day and Jesus is eating Cobb Salad with a T-Rex in a rowboat, your inserts will be minimally logged.

Oracle can do that, with the magic of NO LOGGING!

I tried to make the rest of the code as close to the usual demo table SELECT INTO stuff I normally do.

• ID is an incrementing integer
• ORDER_ID is a random number between 1 and 10,000
• SALES_REP_ID is a random number between 1 and 100
• CUSTOMER_ID is a random number between 1 and 1 million
• The three date columns use the ROWNUM to subtract a span of days, and then a static number of days are added to put a little distance between each activity
• The two name columns are based on substrings of GUIDs
• CUST_PHONE is a random 9 digit number
• IS_SOMETHING is a random 1 or 0 bit column

Easy enough! And quick. 10,000 rows get inserted on my woeful VM in 0.297 ms. That’s about as long as it just took you to blink.

Of course, there are some built in Oracle goodies to generate data a little differently, but they’re (in my mind, anyway) a bit more complicated. They rely on the DMBS_RANDOM functions. There’s a lot you can do with them! The documentation is right over this way. In particular, the STRING subprogram can give you all sorts of nice junk data.

Here’s a quick example using DBMS_RANDOM.

Quick example, he said! Alright then! The date stuff in here took me quite a while to get right. If you follow along, you have to: cast the truncated value from a range between the current system date cast as a string in Julian date format as a Julian date and… I think there’s more? I forgot this as soon as I went to bed.

But the number and string stuff is really easy! Feeding in a range of numbers is super simple. The string stuff is just one upper case character with a random-length string appended to it. These look more like names that GUID substrings, but are probably only useful to anyone trying to come up with names for an entire colony of aliens.

This insert took 1.466 seconds, plus who knows how long getting date ranges figured out. Julian! JULIAN! Why I never.

So now we have some tables

We should probably add some indexes, and figure out how to join them, huh?

Those sound like good future blog post topics.

Thanks for reading!