codemonth.dk

One project every month - making stuff better ...

Even more test data generated using the testdata_ninja plsql package

Last entry to the testdata_ninja blog roll, was about how to create iterators that were unique when you create test data rows. In this entry I will show how to use back references to other column values to use them as input to other row generator functions. One example could be that you were creating a table of employees and you wanted the name to be more realistic. So we could have a country column in the row as well and use that as an input to create the name, so it looks like a name from that country.

So let us define the table as follows:

  • employee_id - number - primary key incrementing by 10, starting from 500
  • country - varchar2 - 2 Letter ISO code of the country, for now just 50% US and 50% DK
  • gender - varchar2 - 1 letter abbr. F for female M for male
  • ename - varchar2 - The name of the employee
  • hiredate - date - Hiredate of the employee, no older than 10 years
  • salary - number - Salary of employee, spanning between 20000 and 55000, with a 85% weighted span between 30000 and 37500

To create that in testdata_ninja, let us take each column definition and see how that would look in the testdata_ninja definition language.

employee_id

number - primary key incrementing by 10, starting from 500
employee_id#number#^numiterate~500~10¤10

country

varchar2 - 2 Letter ISO code of the country, for now just 50% US and 50% DK
country#varchar2(2)#location_random.r_country#true

gender

1 letter abbr. F for female M for male
gender#varchar2(1)#person_random.r_gender

ename

varchar2 - The name of the employee
ename#varchar2(255)#person_random.r_firstname#%%country%%, %%gender%%
Notice the marked text above. Here we are referencing back to other columns already defined, to use them as inputs to the first name generator. You simply use the name of the column and enclose in 2 '%' characters.

hiredate

Hiredate of the employee, no older than 10 years
hiredate#date#time_random.r_date#time_random.r_year(to_char(sysdate-(10*365),'YYYY'))

salary

Salary of employee, spanning between 20000 and 55000, with a 85% weighted span between 30000 and 37500
salary#number#core_random.r_natural#20000,55000,30000,37500,85

So now we have all the definitions for the test data generator. So to create it we can run the following


declare
  p_format varchar2(32000) := 'employee_id#number#^numiterate~500~10¤10@country#varchar2(2)#location_random.r_country#true@gender#varchar2(1)#person_random.r_gender@ename#varchar2(255)#person_random.r_firstname#%%country%%, %%gender%%@hiredate#date#time_random.r_date#time_random.r_year(to_char(sysdate-(10*365),''YYYY''))@salary#number#core_random.r_natural#20000,55000,30000,37500,85';
begin
  testdata_ninja.generator_create('T_BREF', p_format);
end;
/

So now we can try and select from the generator. To start with we will just get 10 rows.


SQL> column ename format a30
SQL> select * from table(tdg_t_bref.t_bref(10));

EMPLOYEE_ID CO G ENAME                          HIREDATE               SALARY
--------------------------------------- -- - ------------------------------ ------------------ ----------
        500 DZ F Arianna                        24-FEB-09 20:13:12      30792
        510 AO F Vega                           28-SEP-12 01:08:33      30299
        520 CV M Karl                           06-JUN-09 16:23:00      32616
        530 AQ F Sigrid                         26-JUN-08 00:04:31      31321
        540 CW M Michael                        14-MAR-15 15:31:01      32903
        550 KH F Filippa                        25-JUN-11 21:22:59      37424
        560 GL M Isaac                          09-SEP-09 09:29:57      49338
        570 MW F Nanna                          23-JUL-08 12:47:18      36598
        580 BB F Victoria                       20-AUG-11 20:32:37      35962
        590 ZA F April                          23-DEC-15 03:50:52      30886

10 rows selected 

SQL>

So as you can see we can generate and reference column values within the same row to generate fast and realistic looking test data.

Tagged in : RANDOM_NINJA