Cut-and-Paste-Driven-Development – Using utPLSQL to build a test harness in Oracle.

If you’re working on a Decision Support System (DSS) then sooner or later you’re likely to need to create or change a package which runs as part of a lengthy batch process.
In such cirumstances, it would be useful to have a good old-fashioned test harness to run your code so you can test it’s functionality without having to kick off the entire batch.

Fortunately, utPLSQL is not just useful when it comes to TDD, CI/CD pipelines and the like. It can easily be used for the purpose of creating a simple stand-alone unit test for a single package.

Having used utPLSQL for this purpose quite regularly, I’ve found that my test harnesses tend to follow the same basic pattern.
What follows is a walkthrough of a typical utPLSQL package I might construct for this purpose.

Obviously, it won’t be to everyone’s taste but hopefully, you’ll find something of use in it.

If you’re interested, you can find the code in this Github Repo.

Testing Approach

The main purpose of these tests is to check that the program being tested produces a set result for a set input.
Each individual test is structured in four sequential phases :

• Setup – setup the input data to use in the test
• Execute – run the test and retrieve the output
• Validate – check that the output matches what we expect
• Teardown – reset the everything back to how it was before we ran the test.

Test Data

Whilst tables in DSS applications can contain a large number of columns, our test records only need to contain values that are :

• mandatory in the table ( or possibly part of an RI constraint or Unique Key)
• relevant to the test we’re conducting

In terms of the values themselves, they are not usually constrained other than by the definition of the column into which they are being inserted.
Therefore, we may be able to use negative numbers for integer identifiers to avoid clashes with any pre-existing records.
Also there may be nothing to stop us using random strings for VARCHAR2 values. Alternatively, if you’re easily amused, you may craft your test values to follow a theme. Not that I’d ever do anything so frivolous…

Environment

The database I’m running on is Oracle 19c on Oracle Cloud (Free Tier).
The version of utPLSQL used here is 3.1.13, downloaded from the Project’s Github Repo

It was installed without the DDL trigger by connecting to my OCI instance as the Admin user and following the appropriate instructions here.

The Code we’ll be Testing

The package we want to test performs updates on this table :

drop table employees_core;
create table employees_core
(
    -- Columns populated by initial load
    load_id number not null,
    employee_id number not null,
    first_name varchar2(20),
    last_name varchar2(25),
    email varchar2(25),
    phone_number varchar2(20),
    hire_date date,
    job_id varchar2(10),
    salary number,
    commission_pct number,
    manager_id number,
    department_id number,
    -- Additional columns populated as part of enrichment process
    -- Job details
    job_title varchar2(50),
    -- Department Details
    department_name varchar2(50),
    -- Enrichment status
    record_status varchar2(25),
    constraint employees_core_pk primary key (load_id, employee_id)
)
/

To do so, it queries these tables :

create table departments
(
    department_id number not null,
    department_name varchar2(30) not null,
    manager_id number,
    location_id number,
    constraint departments_pk primary key ( department_id)
)
/

create table jobs
(
    JOB_ID varchar2(10) not null,
    JOB_TITLE varchar2(35) not null,
    MIN_SALARY number,
    MAX_SALARY number,
    constraint jobs_pk primary key( job_id)
)
/

The package itself is :

create or replace package enrich_employees 
is
    procedure department_details( i_load_id in number);
    procedure job_details( i_load_id in number);
end;
/

create or replace package body enrich_employees 
is
    procedure department_details( i_load_id in number)
    is
        -- Populate the Department Name.
        -- Suspend the record if we don't get a match.
    begin
        merge into employees_core emp
        using departments dept
        on 
        ( 
                emp.department_id = dept.department_id
            and emp.load_id = i_load_id
            and emp.record_status = 'PROCESS'
        )
        when matched then update
            set emp.department_name = dept.department_name;
    
        update employees_core
        set record_status = 'DEPARTMENT_ID_NOT_FOUND'
        where record_status = 'PROCESS'
        and department_name is null;

        commit;
    end department_details;    
            
    procedure job_details( i_load_id in number)
    is
        -- Don't suspend if we don't get a match, just leave the job_title empty.
    begin  
        merge into employees_core emp
        using jobs j
        on
        (
                emp.job_id = j.job_id
            and emp.record_status = 'PROCESS'                
            and emp.load_id = i_load_id
        )
        when matched then update
            set emp.job_title = j.job_title;
        
        commit;            
        
    end job_details;
end;
/

Note that, as is common in such routines, commits are done immediately after potentially large DML statements in order to minimise the length of time that Undo space is in use.
This is significant as we’ll need to account for it explicitly in our tests.

Deciding what tests we need

For the DEPARTMENT_DETAILS procedure, we want to check what happens when :

• we find a match in the DEPARTMENTS table
• we don’t find a match
• the DEPARTMENT_ID on the core record is null

For JOB_DETAILS, the conditions to test are similar :

• RECORD_STATUS is not ‘PROCESS’
• we find a match in the JOBS table
• we don’t find a match
• the JOB_ID is null

From this list of scenarios, we can construct our test package specification :

create or replace package enrich_employees_ut
as
    --%suite(enrich_employees_ut)
    --%rollback(Manual)
    
    --%test( Department Lookup Succeeds)
    procedure department_is_found;
    
    --%test( Department does not exist)
    procedure department_not_found;
    
    --%test( Department ID is null)
    procedure department_is_null;
    
    --%test( Status is not PROCESS)
    procedure status_not_process;
    
    --%test( Job Lookup Succeeds)
    procedure job_is_found;
    
    --%test( Job does not exist)
    procedure job_not_found;
    
    --%test( Job ID is null)
    procedure job_is_null;
    
end enrich_employees_ut;

The annotations we’re using here are :

--%suite(enrich_employees_ut)

…which allows us to the option to group multiple test packages into the same suite should we want to.

--%rollback(Manual)

…which prevents the Framework attempting it’s default rollback behaviour ( rollback to a savepoint) which won’t work here due to the commits in the code we’re testing.

--%test

…which identifies and describes the tests themselves.

Creating a Stub for the test Package Body

We may well want to write tests and run them one-at-a-time so that we can adjust them ( or, indeed the code we’re testing) as we go.

In such circumstances, a script such as this, which uses the already created test package specification to generate a file containing the ddl for the package body, might come in handy :

clear screen
set heading off
set lines 130
set pages 500
set feedback off
set verify off
column pkg new_value v_package_name noprint
select '&1' as pkg from dual;

spool  '&v_package_name..pkb'

with package_skeleton as 
(
    select 1 as line, 'create or replace package body &v_package_name' as text from dual
    union 
    select line,
        case 
            when line < ( select max(line) from user_source where name = upper('&v_package_name') and type = 'PACKAGE')
            then
                replace (
                    replace( 
                        replace(text, '%test('), 
                        ')')
                    ,';', q'[ is begin ut.fail('Not yet written'); end;]')
            else text
        end as text
    from user_source
    where name = upper('&v_package_name')
    and type = 'PACKAGE'
    and line > 1 
    and
    (
        regexp_replace(text, '[[:space:]]') not like '--\%%' escape '\'
        or 
        regexp_replace(text, '[[:space:]]') like '--\%test%' escape '\'
    )    
)
select text from package_skeleton order by line
/

In this case, the generated package body looks like this, and compiles straight away :

create or replace package body enrich_employees_ut
as
    -- Department Lookup Succeeds
    procedure department_is_found is begin ut.fail('Not yet written'); end;
    -- Department does not exist
    procedure department_not_found is begin ut.fail('Not yet written'); end;
    -- Department ID is null
    procedure department_is_null is begin ut.fail('Not yet written'); end;
    -- Status is not PROCESS
    procedure status_not_process is begin ut.fail('Not yet written'); end;
    -- Job Lookup Succeeds
    procedure job_is_found is begin ut.fail('Not yet written'); end;
    -- Job does not exist
    procedure job_not_found is begin ut.fail('Not yet written'); end;
    -- Job ID is null
    procedure job_is_null is begin ut.fail('Not yet written'); end;
end enrich_employees_ut;

Data Setup and Helper Code

I prefer to create global variables to hold the test data.
This is because each test is likely to use a similar data set so, this way, the variables only need to be declared once.
As we’re dealing with table rows here, I’m just declaring a single record variable for each table.
This makes it very simple to add columns that we want to populate as they’re pre-declared as part of the record type.

For this test, my globals are :

g_emp employees_core%rowtype;
g_job jobs%rowtype;
g_dept departments%rowtype;

g_result employees_core%rowtype;

Next, we need a procedure to initialise these globals :

procedure set_globals
is
begin
    -- start by setting the globals to the values required for the first test, which 
    -- I usually make the test for the most commonly expected behaviour
    
    --
    -- Values for Employees Core record
    --
    
    -- Making numeric values negative means that they are less likely to clash 
    -- with an existing sequence generated value
    g_emp.load_id := -1; 
    g_emp.employee_id := -8;
    
    -- However wide the table, we only have to populate mandatory columns, and
    -- any columns we want for the tests...
    g_emp.department_id := -64;
    g_emp.record_status := 'PROCESS';
    
    -- Job Id is a Varchar - no constraints on it other than length...
    g_emp.job_id := 'WIZZARD';
    
    --
    -- Values for the Department Lookup ( starting with ones that we expect to find)
    --
    
    -- Values set independently of the EMPLOYEES_CORE values as we'll want to see what happens 
    -- if they DON't match, as well as if they do.
    g_dept.department_id := -64; 
    g_dept.department_name := 'Cruel and Unusual Geography';
    
    --
    -- Values for the Job lookup
    --
    g_job.job_id := 'WIZZARD';
    g_job.job_title := 'Professor';
    
end set_globals;

Then there is a procedure to create the test records using the global variable values.
This separation between initialising the globals and creating the records is needed so that we can “tweak” the values we use for each test as appropriate :

procedure setup_data
is
    -- Populate the tables with our test data
begin
    insert into employees_core values g_emp;
    insert into departments values g_dept;
    insert into jobs values g_job;
    commit;
end setup_data;

This is followed by a procedure to retrieve the actual results of the program execution :

    procedure fetch_results
    is
        cursor c_result is
            select *
            from employees_core
            where load_id = g_emp.load_id
            and employee_id = g_emp.employee_id;
    begin
        open c_result;
        fetch c_result into g_result;
        close c_result;
    end fetch_results;

Finally, as we need to tidy up after we’ve finished, there’s a teardown procedure to remove any test records we’ve set up :

procedure teardown_data 
is
    -- Tidy up by removing the test data using unique values where possible.
begin
    delete from employees_core
    where employee_id = g_emp.employee_id 
    and load_id = g_emp.load_id;
    
    delete from departments
    where department_id = g_dept.department_id;
    
    delete from jobs
    where job_id = g_job.job_id;

    commit;
end teardown_data;    

Cut-n-Paste Driven Development

You might think we’ve done quite a bit of typing without writing any tests. The payoff for this up-front effort becomes apparent when you start on the first test, as you’re pretty much just calling everything you’ve already written and only need to add a couple of expectations, and an exception handler :

-- Department Lookup Succeeds
procedure department_is_found 
is 
begin 
    -- Setup
    set_globals;
    setup_data;

    -- Execute
    enrich_employees.department_details(g_emp.load_id);
    
    -- Get the actual results
    
    fetch_results;
    -- Validate
    ut.expect( g_result.department_name).to_(equal(g_dept.department_name));
    ut.expect( g_result.record_status).to_(equal('PROCESS'));
    
    -- Teardown 
    teardown_data;
exception when others then
        dbms_output.put_line(dbms_utility.format_error_stack);
        ut.fail('Unexpected Error');
        teardown_data;
end;

The exception handler is there to ensure that the teardown runs even if we hit an unexpected error.
This is more likely than usual as you’re developing the test code and (potentially) the code your testing iteratively when running these tests.

We can execute our test standalone :

set serverout on
exec ut.run('enrich_employees_ut.department_is_found');

enrich_employees_ut
  Department Lookup Succeeds [.005 sec]
 
Finished in .007019 seconds
1 tests, 0 failed, 0 errored, 0 disabled, 0 warning(s)
 


PL/SQL procedure successfully completed.

The code for our second test is almost identical, apart from the change in one variable value and the expected results :

-- Department does not exist
procedure department_not_found 
is 
begin
    
    -- Setup
    set_globals;
    -- Almost exactly the same as the first test excep...
    g_emp.department_id := -4096;
    setup_data;

    -- Execute
    enrich_employees.department_details(g_emp.load_id);
    
    fetch_results;
    
    -- Validate
    ut.expect( g_result.department_name).to_(be_null());
    ut.expect( g_result.record_status).to_(equal('DEPARTMENT_ID_NOT_FOUND'));
    
    -- Teardown 
    teardown_data;
exception when others then
        dbms_output.put_line(dbms_utility.format_error_stack);
        ut.fail('Unexpected Error');
        teardown_data;
end;

The rest of the test follow a broadly similar pattern.

The finished test package body looks like this :

create or replace package body enrich_employees_ut
as
    -- Global variables for use in tests
    g_emp employees_core%rowtype;
    g_job jobs%rowtype;
    g_dept departments%rowtype;
   
    g_result employees_core%rowtype;        

    procedure set_globals
    is
    begin
        g_emp.load_id := -1; 
        g_emp.employee_id := -8;
        
        g_emp.department_id := -64;
        g_emp.record_status := 'PROCESS';
       
        g_emp.job_id := 'WIZZARD';
        g_dept.department_id := -64; 
        g_dept.department_name := 'Cruel and Unusual Geography';
       
        g_job.job_id := 'WIZZARD';
        g_job.job_title := 'Professor';
        
    end set_globals;
    
    procedure setup_data
    is
    begin
        insert into employees_core values g_emp;
        insert into departments values g_dept;
        insert into jobs values g_job;
        commit;
    end setup_data;
    
    procedure fetch_results
    is
        cursor c_result is
            select *
            from employees_core
            where load_id = g_emp.load_id
            and employee_id = g_emp.employee_id;
    begin
        open c_result;
        fetch c_result into g_result;
        close c_result;
    end fetch_results;
    
    procedure teardown_data 
    is
        -- Tidy up by removing the test data using unique values where possible.
    begin
        delete from employees_core
        where employee_id = g_emp.employee_id 
        and load_id = g_emp.load_id;
        
        delete from departments
        where department_id = g_dept.department_id;
        
        delete from jobs
        where job_id = g_job.job_id;
    
        commit;
    end teardown_data;    
    
    -- Department Lookup Succeeds
    procedure department_is_found 
    is 
    begin 
        -- Setup
        set_globals;
        setup_data;

        -- Execute
        enrich_employees.department_details(g_emp.load_id);
        
        fetch_results;

        -- Validate
        ut.expect( g_result.department_name).to_(equal(g_dept.department_name));
        ut.expect( g_result.record_status).to_(equal('PROCESS'));
        
        -- Teardown 
        teardown_data;
    exception when others then
        dbms_output.put_line(dbms_utility.format_error_stack);
        ut.fail('Unexpected Error');
        teardown_data;
    end;

    -- Department does not exist
    procedure department_not_found 
    is 
    begin
        
        -- Setup
        set_globals;
        -- Almost exactly the same as the first test excep...
        g_emp.department_id := -4096;
        setup_data;

        -- Execute
        enrich_employees.department_details(g_emp.load_id);
        
        fetch_results;
        
        -- Validate
        ut.expect( g_result.department_name).to_(be_null());
        ut.expect( g_result.record_status).to_(equal('DEPARTMENT_ID_NOT_FOUND'));
        
        -- Teardown 
        teardown_data;
    exception when others then
        dbms_output.put_line(dbms_utility.format_error_stack);
        ut.fail('Unexpected Error');
        teardown_data;
    end;

    -- Department ID is null
    procedure department_is_null 
    is
    begin
        
        -- Setup
        set_globals;
        -- Again, just a single change required :
        g_emp.department_id := null;
        setup_data;

        -- Execute
        enrich_employees.department_details(g_emp.load_id);
        
        fetch_results;
        
        -- Validate
        ut.expect( g_result.department_name).to_(be_null());
        ut.expect( g_result.record_status).to_(equal('DEPARTMENT_ID_NOT_FOUND'));
        
        -- Teardown 
        teardown_data;
    exception when others then
        dbms_output.put_line(dbms_utility.format_error_stack);
        ut.fail('Unexpected Error');
        teardown_data;
    end;

    -- Status is not PROCESS
    procedure status_not_process 
    is 
    begin
        -- Setup
        set_globals;
        -- This time set the status to prevent processing
        g_emp.record_status := 'SUSPENDED';
        setup_data;

        -- Execute
        enrich_employees.department_details(g_emp.load_id);
        
        fetch_results;

        -- Validate
        ut.expect( g_result.department_name).to_(be_null());
        ut.expect( g_result.record_status).to_(equal('SUSPENDED'));
        
        -- Teardown 
        teardown_data;
    exception when others then
        dbms_output.put_line(dbms_utility.format_error_stack);
        ut.fail('Unexpected Error');
        teardown_data;
    end;

    -- JOB Lookup Succeeds
    procedure job_is_found 
    is 
    begin 
            
        -- Setup
        -- We can use the default values here
        set_globals;
        setup_data;

        -- Execute
        enrich_employees.job_details(g_emp.load_id);
        
        fetch_results;        
        
        -- Validate
        ut.expect( g_result.job_title).to_(equal(g_job.job_title));
        
        -- Teardown 
        teardown_data;
    exception when others then
        dbms_output.put_line(dbms_utility.format_error_stack);
        ut.fail('Unexpected Error');
        teardown_data;
    end;

    -- Job does not exist
    procedure job_not_found 
    is 
    begin
        -- Setup
        set_globals;
        g_emp.job_id := -32768;
        setup_data;

        -- Execute
        enrich_employees.job_details(g_emp.load_id);
        
        -- Get the actual results
        fetch_results;
        
        -- Validate
        ut.expect( g_result.job_title).to_(be_null());
        ut.expect( g_result.record_status).to_(equal('PROCESS'));
        
        -- Teardown 
        teardown_data;
    exception when others then
        dbms_output.put_line(dbms_utility.format_error_stack);
        ut.fail('Unexpected Error');
        teardown_data;
    end;

    -- Job ID is null
    procedure job_is_null 
    is 
    begin 
        -- Setup
        set_globals;
        g_emp.job_id := null;
        setup_data;

        -- Execute
        enrich_employees.job_details(g_emp.load_id);
        
        fetch_results;
        
        -- Validate
        ut.expect( g_result.job_title).to_(be_null());
        ut.expect( g_result.record_status).to_(equal('PROCESS'));
        
        -- Teardown 
        teardown_data;
    exception when others then
        dbms_output.put_line(dbms_utility.format_error_stack);
        ut.fail('Unexpected Error');
        teardown_data;
    end;

end enrich_employees_ut;

To run all of the tests in a suite, I usually use a script like this, which handles recompilation of either the test package or the code unit being tested without raising all those pesky ORA-4068 errors :

exec dbms_session.modify_package_state(dbms_session.reinitialize);
clear screen
set serveroutput on size unlimited
exec ut.run('enrich_employees_ut');

enrich_employees_ut
  Department Lookup Succeeds [.004 sec]
  Department does not exist [.003 sec]
  Department ID is null [.003 sec]
  Status is not PROCESS [.003 sec]
  Job Lookup Succeeds [.003 sec]
  Job does not exist [.003 sec]
  Job ID is null [.003 sec]
 
Finished in .026568 seconds
7 tests, 0 failed, 0 errored, 0 disabled, 0 warning(s)
 


PL/SQL procedure successfully completed.

Of course I will probably still need to run a batch at some point if I want to perform a full System Test and (optionally) a load test. For now however, I can be confident that my code is at least functionally correct.