codemonth.dk

One project every month - making stuff better ...

PLSQL Test data generators with auto back reference fields

So in another entry I described how to manually reference back to other fields to use as input values in other columns for the row generator. What could be a bit more user friendly than that? Maybe using auto reference fields. So if you are using a generator in one of your columns that outputs something that can be used as input to another generator, it is now done automatically.

The best way to describe it is to show it. And the best way to show it, is using the time_random part of the plsql package random_ninja. We will create a generator using all the basic elements of a complete oracle date. Second, Minute, Hour, Day, Month and Year.


declare
  p_format varchar2(4000) := 'my_sec#number#time_random.r_second@my_min#number#time_random.r_minute@my_hour#number#time_random.r_hour@my_month#number#time_random.r_month@my_day#number#time_random.r_day@my_year#number#time_random.r_year@my_date#date#time_random.r_date';
begin
  testdata_ninja.generator_create('auto_reference', p_format);
end;
/

As you can see all the generators are defined without a single input. So once we actually create the full plsql generator, and select from it, we can see that all the individual values are carried over to the last generator function which is time_random.r_date. So the full date is a result of all the individual time_random calls.


SQL> alter session set nls_date_format = 'DD-MM-YYYY HH24:MI:SS';

Session altered.

SQL> select * from table(tdg_auto_reference.auto_reference(10));

MY_SEC     MY_MIN    MY_HOUR   MY_MONTH     MY_DAY    MY_YEAR MY_DATE
---------- ---------- ---------- ---------- ---------- ---------- -------------------
    42         33         21         11          3       1933 03-11-1933 21:33:42
     6         44          8          3         21       1948 21-03-1948 08:44:06
    12         53          9          2         14       1941 14-02-1941 09:53:12
    16         35          2          7         21       1939 21-07-1939 02:35:16
    12         33          1          3         27       1994 27-03-1994 01:33:12
     2         48         19          6          8       1982 08-06-1982 19:48:02
    49         46         23          2          5       1955 05-02-1955 23:46:49
    45         51         14          6          4       1939 04-06-1939 14:51:45
     8         53          5          2         27       1986 27-02-1986 05:53:08
     1         54          3         11          4       2012 04-11-2012 03:54:01

10 rows selected

SQL>

Another good example would be credit cards. So we can create a table where first column is name, second column is the credit card type and then finally a column with the credit card number. Since the input to the credit card number function is the type of the credit card, it can automatically create the random number to match the correct number series for that type of card.


declare
  p_format varchar2(4000) := 'my_name#varchar2(50)#person_random.r_name@my_creditcard#varchar2(20)#finance_random.r_creditcard@my_creditcardnum#number#finance_random.r_creditcardnum';
begin
  testdata_ninja.generator_create('ccards', p_format);
end;
/

So if we select from that generator the result would look like this


SQL> column my_name format a25
SQL> set numwidth 20
SQL> select * from table(tdg_ccards.ccards(10));

MY_NAME                   MY_CREDITCARD                                          MY_CREDITCARDNUM
------------------------- -------------------------------------------------- --------------------
Storm Jepsen              InstaPayment                                           6377778316030656
Asger Hedegaard           Solo                                                   6334971629832289
Karla Henriksen           Mastercard                                             5163313122929755
Hugo Nielsen              Visa Electron                                          4026844826709616
Gabriel Dam               Maestro                                                5018637815772799
Laerke Jacobsen           Solo                                                   6334315224256207
Leonora Justesen          Laser                                                  6304866765546265
Vega Andreasen            Visa Electron                                          4026411583898383
Ida Jakobsen              Visa                                                   4254846762457883
Filippa Ibsen             Maestro                                                5018778677415176

 10 rows selected 

SQL>

Again, no need to code for reference fields. It will automatically done.

Tagged in : RANDOM_NINJA