One project every month - making stuff better ...

Random null value in plsql generated data

I have added another small feature to the testdata library. The ability to create random data is good, but sometimes you also need to have "gaps" in that data. So I made it possible to randomly create null instead of a value for any generated field. Simply surround the generator function in parentheses and add how big a percentage the chance are of a null value after the parentheses.

So a field, where we want 35% of the rows to be null, we would define it as follows


If you leave out the number, it will generate 10% of the rows as null values. As an example we can create the following generator

  p_format varchar2(4000) := 'first_name#varchar2(50)#(person_random.r_firstname)20@last_name#varchar2(50)#person_random.r_lastname';
  testdata_ninja.generator_create('NUL', p_format);

So if we select from the generator we should get around 2-3 null values in the first column.

SQL> select * from table(tdg_nul.nul(10));

FIRST_NAME                                         LAST_NAME
-------------------------------------------------- --------------------------------------------------
Samuel                                             Poulsen
Kaya                                               Rasmussen
Serenity                                           Stewart
Hao                                                Collins
Arianna                                            Davis
Silje                                              Flores
Melina                                             Petersen
Li                                                 Loen

10 rows selected


We get exactly that. The next addition to the generator column, will be to store a generated column as a reference list. More on how that is useful in my next post.

Tagged in : RANDOM_NINJA