Wednesday, January 07, 2015

Generating sample data for your APEX application

You need some sample data sometimes too? When I'm showing some new concepts at a customer or when I'm doing some training I just want some "random" data. Well, it's not really random data, it's a specific type of data I need depending the column and it should be a text that is somewhat meaningful and not hard to read like "1RT3HFIY".

When my wife is doing design and lay-out and she needs text, she's using Lorem Ipsum. In fact it's build in the Adobe tools she's using and the text looks readable (although it isn't). 
It would be so cool if for example SQL Developer had that feature "populate my table(s) with sample data" (even keeping relationships into account).

Before, I used data from all_objects or generated data with dbms_random and a connect by clause for the amount of records I wanted, but it wasn't ideal. I also looked at scrambling my data, which is nice because existing relations keep intact, but for me it didn't really work nicely if I needed to scramble a lot of columns. There're some companies having solutions for generating/scrambling data too, but below I want to share what I'm currently doing.

Go to generatedata.com and enter the definition of your table and which kind of data you want per column.


Once the definition is there you can define how you want to receive the data. I found the SQL tab didn't really work well, so I use CSV as output.

Next in Oracle SQL Developer I right click on my table and say "Import data" and select the csv.
It automatically knows the format etc. and maps it correctly to my table. Hit Next and you have your sample data available :) 


You can also load the data straight from the Data Workshop in APEX.


2 comments:

Morten Braten said...

Hi Dimitri, and happy new year :-)

You could also use my PL/SQL utility package for generating random data, as described here:

http://ora-00001.blogspot.com/2011/02/generating-test-data-using-plsql.html

Using this package, you can populate your tables using simple "insert ... select" statements.

- Morten

pat said...

Hi, Dimitri, Morten,
Very helpful. Great tools. I teach a class on DB concepts and use SQL Developer and Apex and have wanted to use some new data (vs. the data I keep re-using) for student projects! This is just what I need.

Pat