Introduction

Test Data Generator (Data Generator further on) is a PL/SQL library designed to simplify the generation of test data within Oracle databases, with a primary focus on facilitating unit testing. Built with a minimalist approach, it aims to relieve the user of work to provide data required by integrity constraints, but not essential for the particular test case.

For example, the following code will insert a new record into EMPLOYEES table:

BEGIN
    dg.insert_record('employees');
END;
/

Data Generator will take care of providing the new record with data, taking into account integrity constrains such as NOT NULL, unique, and foreign keys.

In the case of a foreign key constraint, Data Generator will initially attempt to locate an appropriate record in the referenced table. Should no matching record be found, it will proceed to insert a new one and establish the necessary reference. This process continues cascading until data integrity is achieved.

Data Generator also allows you to have specific values in particular columns:

DECLARE
    v_values dg.GTYPE_RECORD;
BEGIN
    -- FIRST_NAME and LAST_NAME are columns of EMPLOYEES table
    v_values('first_name').varchar2_val := 'Steven';
    v_values('last_name').varchar2_val := 'King';
    dg.insert_record(
        iTABLE  => 'employees',
        iVALUES => v_values);
END;
/

In this case it will do the same job as in the previous example, but additionaly ensureing that the specified columns receive the specified values.

The insert_record subprogram is also provided as a function, whose return value reflects the inserted record with all its values. This can be used, for example, to insert custom data into many dependent tables:

DECLARE
    v_job_values    dg.GTYPE_RECORD;
    v_job_rec       dg.GTYPE_RECORD;
    v_emp_values    dg.GTYPE_RECORD; 
BEGIN
    v_job_values('min_salary').number_val := 100000;
    v_job_values('max_salary').number_val := 999999;
    v_job_rec := dg.insert_record(
        iTABLE  => 'jobs',
        iVALUES => v_job_values);

    v_emp_values('job_id').varchar2_val := v_job_rec('job_id').varchar2_val;
    v_emp_values('salary').number_val := 500000;
    v_job_rec := dg.insert_record(
        iTABLE  => 'employees',
        iVALUES => v_emp_values);
END;
/

In this example an employee is inserted, whose job allows to have a salary of 500000.

Another feature provided by Data Generator is the ability to define additional constraints not defined in the data dictionary.

For example, you can specify any table column as not nullable even it is declared as nullable in the table definition:

BEGIN
    dg.describe_not_null('employees', 'phone_number');
END;
/

insert_record will then fill the PHONE_NUMBER column with a not null value.

Or define a unique constraint:

BEGIN
    dg.describe_unique('employees', dg.cols('first_name', 'last_name'));
END;
/

Or a foreign key constraint:

BEGIN
    dg.describe_fk(
        iTABLE      => 'employees',
        iCOLS       => dg.cols('email'),
        iREF_TABLE  => 'persons',
        iREF_COLS   => dg.cols('email')
END;
/