One project every month - making stuff better ...

Quick lists when generating test data using plsql

One of the outstanding issues that I have had on the testdata_ninja library, was the ability to do quick lists. A short list of possible values, like a status column or open/close kind of values.

It is implemented using the $ sign as the special character to denote a reference list. The format is very simple. First add the $ character and then enclosed in square brackets [] you out the comma separated list of potential values. So a complete definition of a referencelist field would be:


You can see a quick example of how to do it below:

  p_format  varchar2(4000) := 'eid#number#^numiterate~500~10ยค10@afield#varchar2(10)#$[one,two,status]';
  testdata_ninja.generator_create('TREFLIST', p_format);

And then we can see the following type of output from the test data generator:

SQL> select * from table(tdg_treflist.treflist);

EID       AFIELD  
---------- ----------
500      two       
510      status    
520      one       
530      two       
540      one       
550      one       
560      two       
570      status    
580      two       
590      two       

10 rows selected

Next week I will be releasing a series of videos demonstrating the usage of the package, starting from a beginners perspective and slowly adding more and more complexity and showing advanced use cases.

Tagged in : RANDOM_NINJA