codemonth.dk

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


first_name#varchar2(50)#(person_random.r_firstname)35

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


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

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
                                                   Kristensen
Hao                                                Collins
Arianna                                            Davis
Silje                                              Flores
Melina                                             Petersen
Li                                                 Loen
                                                   Ross

10 rows selected

SQL>

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