One project every month - making stuff better ...

Predictable random test data using plsql

One of the problems in using random generated test data, is that it is random. Because sometimes what you need is test data that is the same every time you generate it. This entry will look at how you can achieve this using the seed functionality of the dbms_random package.

Not long ago on twitter Kris Rice polled people on what they were using for demo data.

I of course replied that people should be using my 2 packages random_ninja and testdata_ninja, because I think they solve a lot of the problems and difficulties people have when creating test data and demo data. One point raised by Kris though was that: So I do agree with that point. For a demo or a presentation you would prefer that the data you are using is the same every time. Luckily this is actually very easy to implement in my packages because at the core, all I am using is the dbms_random package. That package includes a seed procedure, which will basically make your random calls "predictable". So here is a short demonstration of it works when implemented in the testdata_ninja generator.

First we quickly create a generator based on an existing table.

SQL> begin
  2  testdata_ninja.generator_create(generator_name => 'my_emps_t', generator_table => 'EMP');
  3  end;
  4  /
PL/SQL procedure successfully completed.
So now we can use our generator to create test data very easily, like this:

SQL> select * from table(tdg_my_emps_t.my_emps_t(5));

EMPNO ENAME                JOB                                                       MGR HIREDATE                  SAL       COMM     DEPTNO
---------- -------------------- -------------------------------------------------- ---------- ------------------ ---------- ---------- ----------
 7369 Bundgaard            Server                                                   7809 24-MAY-86 11:57:52       1982                    23
 7370 Carstensen           Database Administrator                                   7852 20-AUG-80 05:26:25       1282        993         29
 7371 Clausen              Programmer                                               7701 02-OCT-87 20:34:15       2584                    13
 7372 Martinez             Doctor                                                   7832 26-JUL-82 14:06:43       3226                    20
 7373 Johansen             CFO                                                           12-JAN-82 02:28:18       4132                    19

But the outcome will be different every time. Different values by default. If on the other hand, you want predictable data from the random generator, you can simply submit a key as part of the generator call, like this:

SQL> select * from table(tdg_my_emps_t.my_emps_t(predictable_key => 'my oracle code demo'));
When you use this key, every call will produce the same output. An even better outcome from this feature is that you can now move your test data generators between multiple databases but still have the same outcome, which is very handy for unit testing, integration testing and more. I will demonstrate this in detail in an upcoming blog post.

Tagged in : RANDOM_NINJA