Oracle and JUnit Data Driven Testing: An Example

In Design Patterns for Database API Testing 1: Web Service Saving 2 - Code I identified a number of antipatterns commonly seen in database testing.

utPLSQL, originally developed by Steve Feuerstein around 15 years ago, may have been the first Oracle unit testing framework and seems to be the most widely used. Its new utPLSQL GitHub Page describes itself thus:

The official new home of utPLSQL, unit testing framework for PL/SQL, based on xUnit.

xUnit refers to the family of unit testing frameworks derived from Java's JUnit, and present in most object-oriented languages, such as Ruby, Python etc.

It has occurred to me that some of the problems with unit testing in the database world may arise from translating object-oriented ideas on testing too zealously into the database world, where they may not work so well. It could also impinge on the design of base code, in that in the object-oriented world any complexity in unit tests is usually seen as a 'code smell' indicating the base units are too complex; testability is seen as a key objective in OO module design. To gain some insight into the differences between database and object-oriented testing, it seemed like a good idea to try to test the same functionality in both Java and Oracle. This article gives the results of this experiment.

I have submitted an abstract for a presentation entitled "Database Functional Testing: Automation, Yes; xUnit-Style Micro-Testing, No" to one of the Oracle User Group conferences for Q1 2017, but it has not yet been approved or rejected.

Update, 10 May 2017: My abstract for the Ireland OUG in March 2017 was not accepted. I did present on a second abstract that was accepted: Dimensional Performance Benchmarking of SQL - IOUG Presentation

Testing Example

In Java unit testing one normally tries to test code in isolation, without database access or other dependencies, whereas in Oracle it is normally database operations that are being tested. As a compromise I chose to implement code that would read a single key column from a CSV file and store counts of the key values in memory, with methods to return these key-value pairs as lists either unordered, ordered by key, or ordered by value (then key).

The constructor method will take three parameters:

  • File name
  • Delimiter
  • Key column position

We'll use the same file name, which in Java will include the full path, whereas Oracle will assume the path from an Oracle directory object. There will be two scenarios that will test different values for the other two parameters simultaneously (as what I call sub-scenarios in Design Patterns for Database API Testing 1: Web Service Saving 1 - Design).

For the unordered method, we will validate only the scalar count of the records returned, while for the ordered methods we will validate the full ordered lists of tuples returned. For illustrative purposes one test method for each scenario will have a deliberate error in the expected values.

Test Scenario 1 - Tie-break/single-delimiter/interior column

Here the test file has four columns, with the key column being the third, and the delimiter a single character ','. The file contains lines:

0,1,Cc,3
00,1,A,9
000,1,B,27
0000,1,A,81

Note that keys 'Cc' and 'B' both occur once, and when ordered by value (then key) 'B' should appear before 'Cc'.

Test Scenario 2 - Two copies/double-delimiter/first column

Here the test file has three columns, with the key column being the first, and the delimiter two characters ';;'. The file contains two identical lines:

X;;1;;A
X;;1;;A

Java - JUnit

JUnit Code

package colgroup;
/***************************************************************************************************
Name:        TestColGroup.java

Description: Junit testing class for Col_Group class. Uses Parameterized.class to data-drive
                                                                               
Modification History
Who                  When        Which What
-------------------- ----------- ----- -------------------------------------------------------------
B. Furey             22-Oct-2016 1.0   Created                       

***************************************************************************************************/
import static org.junit.Assert.*;

import java.io.IOException;
import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.util.AbstractMap;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.List;
import java.util.Map;

import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.junit.runners.Parameterized.Parameters;
import org.junit.runners.Parameterized;

@RunWith(Parameterized.class)
public class TestColGroup {
/***************************************************************************************************

Private instance variables: 2 scenarios, input, and expected records declared here, initially in 
2-level generic arrays, but expected records transferred to List for assertion

***************************************************************************************************/
  private ColGroup colGroup = null;
  private String testFile = "H:/Script/Input/ut_group.csv";
  private String[][] testLines = new String[][] { 
      {"0,1,Cc,3", "00,1,A,9", "000,1,B,27", "0000,1,A,81"}, 
      {"X;;1;;A", "X;;1;;A"}
  };
  private String[] testDelim = new String[] {",", ";;"};
  private int[] testColnum = new int[] {2, 0};
  private List<String> lines;
  private String delim;
  private int colnum;

  private String[][] keysK = new String[][] { 
      {"A", "Bx", "Cc"}, 
      {"X"}
  };
  private int[][] valuesK = new int[][] { 
      {2, 1, 1}, 
      {2}
  };
  private String[][] keysV = new String[][] { 
      {"B", "Cc", "A"},
      {"X"}
  };
  private int[][] valuesV = new int[][] { 
      {1, 1, 2}, 
      {2}
  };
  private int expAsIs;
  private List<Map.Entry<String,Long>> expListK = null;
  private List<Map.Entry<String,Long>> expListV = null;

  private void addMap (int i, String strValK, int lonValK, String strValV, int lonValV) {
    expListK.add (i, new AbstractMap.SimpleEntry<String, Long> (strValK, (long) lonValK));
    expListV.add (i, new AbstractMap.SimpleEntry<String, Long> (strValV, (long) lonValV));
  }

  private int testNum;

  /***************************************************************************************************

  TestColGroup: Constructor function, which sets the instance variables for given scenario (testNum), and
                is called before each test with parameters passed via test_data (see end)

  ***************************************************************************************************/
  public TestColGroup (int   testNum,   // test scenario number
                  int   nGroups) { // number of groups

    System.out.println("Doing TestCG3 before test "+testNum+"...");
    this.lines = Arrays.asList (testLines[testNum]);
    this.delim = testDelim[testNum];
    this.colnum = testColnum[testNum];
    this.expAsIs = nGroups;
    this.testNum = testNum;
    int i = 0;
    expListK = new ArrayList<Map.Entry<String,Long>>(keysK[testNum].length);
    expListV = new ArrayList<Map.Entry<String,Long>>(keysV[testNum].length);
    for (String k : keysK[testNum]) {
      addMap (i, k, valuesK[testNum][i], keysV[testNum][i], valuesV[testNum][i]);
      i++;
    }
  }
  /***************************************************************************************************

  getGroup: Before each test method to write the test file and instantiate base object, using instance
            variables set for the scenario in TestCG3

  ***************************************************************************************************/
  @Before
  public void getGroup() {
    try {
      System.out.println("Doing setup before test "+this.testNum+"...");
      Files.write (Paths.get (testFile), lines, StandardCharsets.UTF_8);
      colGroup = new ColGroup (testFile, delim, colnum);
    } catch (IOException e) {
      e.printStackTrace();
    }
  }
  /***************************************************************************************************

  delFile: After each test method to delete the test file

  ***************************************************************************************************/
  @After
  public void delFile() {
    try {
      System.out.println("Doing teardown after test "+this.testNum+"...");
      Files.delete(Paths.get (testFile));
    } catch (IOException e) {
      e.printStackTrace();
    }
  }
  /***************************************************************************************************

  test*: Test method for each base method; each one is run once for each record defined in test_data
         in @Parameters

  ***************************************************************************************************/
  @Test
  public void testAsIs() {

    List<Map.Entry<String,Long>> actList = colGroup.listAsIs();
    assertEquals ("(as is)", expAsIs, actList.size());
    colGroup.prList("(as is)", actList);
  }
  @Test
  public void testKey() {

    List<Map.Entry<String,Long>> actList = colGroup.sortByKey();
    assertEquals ("keys", expListK, actList);
    colGroup.prList("keys", actList);
  }
  @Test
  public void testValue() {
    List<Map.Entry<String,Long>> actList = colGroup.sortByValue();
    assertEquals ("values", expListV, actList);
    colGroup.prList("values", actList);
  }
  /***************************************************************************************************

  test_data: @Parameters section allows passing of data into tests per scenario; neater to pass in a
             pointer to the instance arrays for most of the data

  ***************************************************************************************************/
  @Parameters
  public static Collection<Object[]> test_data() {
    Object[][] data = new Object[][] { {0, 3}, {1, 2} }; // 2 records, columns = scenario #, # groups
    return Arrays.asList(data);
  }
}

JUnit Output

Unit Test Results.

Designed for use with JUnit and Ant.

All Tests

Class Name Status Type Time(s)
TestColGroup testKey[0] Failure keys expected:<[A=2, Bx=1, Cc=1]> but was:<[A=2, B=1, Cc=1]>

junit.framework.AssertionFailedError: keys expected:<[A=2, Bx=1, Cc=1]> but was:<[A=2, B=1, Cc=1]>
at colgroup.TestColGroup.testKey(TestColGroup.java:150)

0.055
TestColGroup testValue[0] Success 0.004
TestColGroup testAsIs[0] Success 0.002
TestColGroup testKey[1] Success 0.004
TestColGroup testValue[1] Success 0.002
TestColGroup testAsIs[1] Failure (as is) expected:<2> but was:<1>

junit.framework.AssertionFailedError: (as is) expected:<2> but was:<1>
at colgroup.TestColGroup.testAsIs(TestColGroup.java:143)

0.002

JUnit Notes

  • JUnit first creates instances of the test class for each test method and starts running the tests after each instance is created
  • From JUnit 4 it is possible to data-drive testing by means of the @Parameters annotation, as implemented here, whereas I identified lack of data-driving as a common antipattern
  • Test methods are identifiable only by their method names rather than full descriptions
  • Scenarios are identifiable only by a number, which is even worse
  • Execution of a test method (instance) is aborted on failure of any assertion
  • Here there is only one test method per base method, but in general there could be several
  • JUnit aborting on assertion failure means that unit tests should have one or very few assertions, with additional unit tests being generated where necessary
  • Data-driving allows JUnit to generate additional unit tests from a single method at run-time for each scenario
  • A good approach is to start with a single parameterized scenario, then add new scenarios just by adding data records; this is how I usually proceed in Oracle
  • On assertion failure JUnit prints expected and actual values for both scalar and complex values passed to the assertion

Oracle - utPLSQL

utPLSQL Code

CREATE OR REPLACE PACKAGE BODY UT_Col_Group AS
/***************************************************************************************************

Description: utPLSQL unit testing for polyglot group-counting module, Col_Group
                                                                               
Modification History
Who                  When        Which What
-------------------- ----------- ----- -------------------------------------------------------------
Brendan Furey        30-Oct-2016 1.0   Created

***************************************************************************************************/

c_proc_name_asis        CONSTANT VARCHAR2(60) := 'Col_Group.ut_AIP_List_Asis';
c_proc_name_key         CONSTANT VARCHAR2(60) := 'Col_Group.ut_AIP_Sort_By_Key';
c_proc_name_value       CONSTANT VARCHAR2(60) := 'Col_Group.ut_AIP_Sort_By_vALUE';

c_file_2lis             CONSTANT L2_chr_arr := L2_chr_arr (
                                      L1_chr_arr ('0,1,Cc,3', '00,1,A,9', '000,1,B,27', '0000,1,A,81'),
                                      L1_chr_arr ('X;;1;;A', 'X;;1;;A')
);
c_prms_2lis             CONSTANT L2_chr_arr := L2_chr_arr (
                                      L1_chr_arr ('lines.csv', ',', '3'), L1_chr_arr ('lines.csv', ';;', '1')
);
c_scenario_lis          CONSTANT L1_chr_arr := L1_chr_arr ('Tie-break/single-delimiter/interior column', 'Two copies/double-delimiter/first column');

/***************************************************************************************************

ut_Setup, ut_Teardown: Mandatory procedures for utPLSQL but don't do anything here

***************************************************************************************************/
PROCEDURE ut_Setup IS
BEGIN
  NULL;
END ut_Setup;

PROCEDURE ut_Teardown IS
BEGIN
  NULL;
END ut_Teardown;

/***************************************************************************************************

Do_Test: Main local procedure for utPLSQL unit testing Col_Group methods

***************************************************************************************************/

PROCEDURE Do_Test (p_proc_name          VARCHAR2,      -- procedure name
                   p_exp_2lis           L2_chr_arr) IS -- expected values 2-d array

  /***************************************************************************************************

  Setup: Setup procedure for unit testing Col_Group package. Writes test file, then calls
         constructor API to store data in an array, line counts grouped by key

  ***************************************************************************************************/
  PROCEDURE Setup (p_file                 VARCHAR2,      -- file name
                   p_delim                VARCHAR2,      -- delimiter
                   p_colnum               PLS_INTEGER,   -- key column number in file
                   p_dat_lis              L1_chr_arr) IS -- lines to write to test file

  BEGIN

    Utils.Delete_File (p_file);
    Utils.Write_File (p_file, p_dat_lis);

    Col_Group.AIP_Load_File (p_file => p_file, p_delim => p_delim, p_colnum => p_colnum);

  END Setup;

  /***************************************************************************************************

  Call_Proc: Calls the base method according to calling procedure, and uses utPLSQL assert procedure
             to assert list counts, and for ordered methods, record lists in delimited form

  ***************************************************************************************************/
  PROCEDURE Call_Proc (p_exp_lis        L1_chr_arr,  -- expected values list (delimited records)
                       p_scenario       VARCHAR2) IS -- scenario description

    l_arr_lis           chr_int_arr;
  BEGIN

    l_arr_lis := CASE p_proc_name
                   WHEN c_proc_name_asis        THEN Col_Group.AIP_List_Asis
                   WHEN c_proc_name_key         THEN Col_Group.AIP_Sort_By_Key
                   WHEN c_proc_name_value       THEN Col_Group.AIP_Sort_By_Value
                 END;

    IF p_proc_name = c_proc_name_asis THEN

      utAssert.Eq (p_scenario || ': List count', l_arr_lis.COUNT, p_exp_lis(1), TRUE);

    ELSE

      utAssert.Eq (p_scenario || ': List count', l_arr_lis.COUNT, p_exp_lis.COUNT, TRUE);
      FOR i IN 1..LEAST (l_arr_lis.COUNT, p_exp_lis.COUNT) LOOP

        utAssert.Eq ('...Record', Utils.List_Delim (l_arr_lis(i).chr_field, l_arr_lis(i).int_field), p_exp_lis(i), TRUE);

      END LOOP;

    END IF;

  END Call_Proc;

BEGIN

  FOR i IN 1..c_file_2lis.COUNT LOOP

    Setup (p_file              => c_prms_2lis(i)(1),
           p_delim             => c_prms_2lis(i)(2),
           p_colnum            => c_prms_2lis(i)(3),
           p_dat_lis           => c_file_2lis(i));

    Call_Proc (p_exp_2lis(i), c_scenario_lis(i));

  END LOOP;

END Do_Test;

/***************************************************************************************************

ut_AIP_List_Asis: Entry procedure for utPLSQL testing Col_Group.AIP_List_Asis

***************************************************************************************************/
PROCEDURE ut_AIP_List_Asis IS

  c_proc_name           CONSTANT VARCHAR2(61) := c_proc_name_asis;
  c_exp_2lis            CONSTANT L2_chr_arr := L2_chr_arr (L1_chr_arr('3'), L1_chr_arr('2'));

BEGIN

  Do_Test (c_proc_name, c_exp_2lis);

END ut_AIP_List_Asis;

/***************************************************************************************************

ut_AIP_Sort_By_Key: Entry procedure for utPLSQL testing Col_Group.AIP_Sort_By_Key

***************************************************************************************************/
PROCEDURE ut_AIP_Sort_By_Key IS

  c_proc_name           CONSTANT VARCHAR2(61) := c_proc_name_key;
  c_exp_2lis            CONSTANT L2_chr_arr := L2_chr_arr (L1_chr_arr (Utils.List_Delim ('A','2'),
                                                                         Utils.List_Delim ('Bx','1'),
                                                                         Utils.List_Delim ('Cc','1')),
                                                             L1_chr_arr (Utils.List_Delim ('X','2'))
                                               );
BEGIN

  Do_Test (c_proc_name, c_exp_2lis);

END ut_AIP_Sort_By_Key;

/***************************************************************************************************

ut_AIP_Sort_By_Value: Entry procedure for utPLSQL testing Col_Group.AIP_Sort_By_Value

***************************************************************************************************/
PROCEDURE ut_AIP_Sort_By_Value IS

  c_proc_name           CONSTANT VARCHAR2(61) := c_proc_name_value;
  c_exp_2lis            CONSTANT L2_chr_arr := L2_chr_arr (L1_chr_arr (Utils.List_Delim ('B','1'),
                                                                         Utils.List_Delim ('Cc','1'),
                                                                         Utils.List_Delim ('A','2')),
                                                             L1_chr_arr (Utils.List_Delim ('X','2'))
                                               );
BEGIN

  Do_Test (c_proc_name, c_exp_2lis);

END ut_AIP_Sort_By_Value;

END UT_Col_Group;
/

utPLSQL Output

.
>  FFFFFFF   AA     III  L      U     U RRRRR   EEEEEEE
>  F        A  A     I   L      U     U R    R  E
>  F       A    A    I   L      U     U R     R E
>  F      A      A   I   L      U     U R     R E
>  FFFF   A      A   I   L      U     U RRRRRR  EEEE
>  F      AAAAAAAA   I   L      U     U R   R   E
>  F      A      A   I   L      U     U R    R  E
>  F      A      A   I   L       U   U  R     R E
>  F      A      A  III  LLLLLLL  UUU   R     R EEEEEEE
.
 FAILURE: ".COL_GROUP"
.
> Individual Test Case Results:
>
SUCCESS - COL_GROUP.UT_AIP_LIST_ASIS: EQ "Tie-break/single-delimiter/interior column: List count" Expected "3" and got "3"
>
FAILURE - COL_GROUP.UT_AIP_LIST_ASIS: EQ "Two copies/double-delimiter/first column: List count" Expected "2" and got "1"
>
SUCCESS - COL_GROUP.UT_AIP_SORT_BY_KEY: EQ "Tie-break/single-delimiter/interior column: List count" Expected "3" and got "3"
>
SUCCESS - COL_GROUP.UT_AIP_SORT_BY_KEY: EQ "...Record" Expected "A|2" and got "A|2"
>
FAILURE - COL_GROUP.UT_AIP_SORT_BY_KEY: EQ "...Record" Expected "Bx|1" and got "B|1"
>
SUCCESS - COL_GROUP.UT_AIP_SORT_BY_KEY: EQ "...Record" Expected "Cc|1" and got "Cc|1"
>
SUCCESS - COL_GROUP.UT_AIP_SORT_BY_KEY: EQ "Two copies/double-delimiter/first column: List count" Expected "1" and got "1"
>
SUCCESS - COL_GROUP.UT_AIP_SORT_BY_KEY: EQ "...Record" Expected "X|2" and got "X|2"
>
SUCCESS - COL_GROUP.UT_AIP_SORT_BY_VALUE: EQ "Tie-break/single-delimiter/interior column: List count" Expected "3" and got "3"
>
SUCCESS - COL_GROUP.UT_AIP_SORT_BY_VALUE: EQ "...Record" Expected "B|1" and got "B|1"
>
SUCCESS - COL_GROUP.UT_AIP_SORT_BY_VALUE: EQ "...Record" Expected "Cc|1" and got "Cc|1"
>
SUCCESS - COL_GROUP.UT_AIP_SORT_BY_VALUE: EQ "...Record" Expected "A|2" and got "A|2"
>
SUCCESS - COL_GROUP.UT_AIP_SORT_BY_VALUE: EQ "Two copies/double-delimiter/first column: List count" Expected "1" and got "1"
>
SUCCESS - COL_GROUP.UT_AIP_SORT_BY_VALUE: EQ "...Record" Expected "X|2" and got "X|2"
>
>
> Errors recorded in utPLSQL Error Log:
>
> NONE FOUND

PL/SQL procedure successfully completed.

utPLSQL Notes

  • Code is shared between the three test methods by means of a common local procedure Do_Test
  • Data-driving is achieved by using generic arrays and looping over scenarios
  • Only the simple assertion procedure utAssert.Eq is used; from experience the complex utPLSQL assertions are rarely suitable, and I think it is conceptually simpler to avoid them altogether
  • In general the lists of actual and expected values may have different cardinalities, so looping over them I use the minimum cardinality as loop maximum, and explicitly assert the counts; this means you may not see the detail for the unmatched records - in my own TRAPIT framework I handle this case by adding null records to the smaller list
  • Delimited records are asserted to limit the number of assertions, which would matter more in realistic cases having larger number of columns
  • utPLSQL does not have any data model for scenarios or output groups, with just a single description field available within the assert call to describe the scenario/group/assertion; I recommend bundling scenario and group information into this message for want of anything better
  • utPLSQL allows only one test method per base method, unlike JUnit, so multiple assertions may be necessary; fortunately an assertion failure does not abort the test procedure
  • utPLSQL test procedures run sequentially, in alphabetical order, unlike JUnit

Oracle - TRAPIT

This is the framework I described in articles listed at TRAPIT - TRansactional API Testing in Oracle. I have renamed the framework, dropping the phrase 'unit testing' because it has connotations from JUnit of testing small, isolated pieces of code, which is not what it is intended for.

TRAPIT Code

CREATE OR REPLACE PACKAGE BODY TT_Col_Group AS
/***************************************************************************************************

Description: TRAPIT (TRansactional API Testing) package for Col_Group

Further details: 'TRAPIT - TRansactional API Testing in Oracle'
                 
TRAPIT - TRansactional API Testing in Oracle
Modification History Who When Which What -------------------- ----------- ----- ------------------------------------------------------------- Brendan Furey 22-Oct-2016 1.0 Created Brendan Furey 13-Nov-2016 1.1 Utils_TT -> Utils_TT ***************************************************************************************************/ c_ms_limit CONSTANT PLS_INTEGER := 2; c_proc_name_asis CONSTANT VARCHAR2(60) := 'Col_Group.tt_AIP_List_Asis'; c_proc_name_key CONSTANT VARCHAR2(60) := 'Col_Group.tt_AIP_Sort_By_Key'; c_proc_name_value CONSTANT VARCHAR2(60) := 'Col_Group.tt_AIP_Sort_By_vALUE'; c_file_2lis CONSTANT L2_chr_arr := L2_chr_arr ( L1_chr_arr ('0,1,Cc,3', '00,1,A,9', '000,1,B,27', '0000,1,A,81'), L1_chr_arr ('X;;1;;A', 'X;;1;;A') ); c_prms_2lis CONSTANT L2_chr_arr := L2_chr_arr ( L1_chr_arr ('lines.csv', ',', '3'), L1_chr_arr ('lines.csv', ';;', '1') ); c_scenario_lis CONSTANT L1_chr_arr := L1_chr_arr ('Tie-break/single-delimiter/interior column', 'Two copies/double-delimiter/first column'); c_inp_group_lis CONSTANT L1_chr_arr := L1_chr_arr ('Parameter', 'File'); c_inp_field_2lis CONSTANT L2_chr_arr := L2_chr_arr ( L1_chr_arr ('File Name', 'Delimiter', '*Column'), L1_chr_arr ('Line') ); c_out_group_lis CONSTANT L1_chr_arr := L1_chr_arr ('Sorted Array'); c_out_fields_2lis CONSTANT L2_chr_arr := L2_chr_arr (L1_chr_arr ('Key', '*Count')); /*************************************************************************************************** Do_Test: Main local procedure for TRAPIT testing Col_Group methods ***************************************************************************************************/ PROCEDURE Do_Test (p_proc_name VARCHAR2, p_exp_2lis L2_chr_arr, p_out_group_lis L1_chr_arr, p_out_fields_2lis L2_chr_arr) IS l_timer_set PLS_INTEGER; l_inp_3lis L3_chr_arr := L3_chr_arr(); l_act_2lis L2_chr_arr := L2_chr_arr(); /*************************************************************************************************** Setup: Setup procedure for TRAPIT testing Col_Group package. Writes test file, then calls constructor API to store data in an array, line counts grouped by key ***************************************************************************************************/ PROCEDURE Setup (p_file VARCHAR2, -- file name p_delim VARCHAR2, -- delimiter p_colnum PLS_INTEGER, -- key column number in file p_dat_lis L1_chr_arr, -- lines to write to test file x_inp_2lis OUT L2_chr_arr) IS -- generic inputs list BEGIN Utils.Delete_File (p_file); Utils.Write_File (p_file, p_dat_lis); x_inp_2lis := L2_chr_arr (L1_chr_arr (Utils.List_Delim (p_file, p_delim, p_colnum)), p_dat_lis); Col_Group.AIP_Load_File (p_file => p_file, p_delim => p_delim, p_colnum => p_colnum); END Setup; /*************************************************************************************************** Call_Proc: Calls the base method according to calling procedure, and converts record lists to delimited form, and populates the actual list for later checking ***************************************************************************************************/ PROCEDURE Call_Proc (x_act_lis OUT L1_chr_arr) IS -- actual values list (delimited records) l_arr_lis chr_int_arr; l_act_lis L1_chr_arr := L1_chr_arr(); BEGIN l_arr_lis := CASE p_proc_name WHEN c_proc_name_asis THEN Col_Group.AIP_List_Asis WHEN c_proc_name_key THEN Col_Group.AIP_Sort_By_Key WHEN c_proc_name_value THEN Col_Group.AIP_Sort_By_Value END; Timer_Set.Increment_Time (l_timer_set, Utils_TT.c_call_timer); l_act_lis.EXTEND (l_arr_lis.COUNT); FOR i IN 1..l_arr_lis.COUNT LOOP l_act_lis(i) := Utils.List_Delim (l_arr_lis(i).chr_field, l_arr_lis(i).int_field); END LOOP; x_act_lis := CASE p_proc_name WHEN c_proc_name_asis THEN L1_chr_arr(l_arr_lis.COUNT) ELSE l_act_lis END; END Call_Proc; BEGIN l_timer_set := Utils_TT.Init (p_proc_name); l_act_2lis.EXTEND (c_file_2lis.COUNT); l_inp_3lis.EXTEND (c_file_2lis.COUNT); FOR i IN 1..c_file_2lis.COUNT LOOP Setup (p_file => c_prms_2lis(i)(1), p_delim => c_prms_2lis(i)(2), p_colnum => c_prms_2lis(i)(3), p_dat_lis => c_file_2lis(i), -- data file inputs x_inp_2lis => l_inp_3lis(i)); Timer_Set.Increment_Time (l_timer_set, 'Setup'); Call_Proc (l_act_2lis(i)); END LOOP; Utils_TT.Check_TT_Results (p_proc_name, c_scenario_lis, l_inp_3lis, l_act_2lis, p_exp_2lis, l_timer_set, c_ms_limit, c_inp_group_lis, c_inp_field_2lis, p_out_group_lis, p_out_fields_2lis); END Do_Test; /*************************************************************************************************** tt_AIP_List_Asis: Entry procedure for TRAPIT testing Col_Group.AIP_List_Asis ***************************************************************************************************/ PROCEDURE tt_AIP_List_Asis IS c_proc_name CONSTANT VARCHAR2(61) := c_proc_name_asis; c_exp_2lis CONSTANT L2_chr_arr := L2_chr_arr (L1_chr_arr('3'), L1_chr_arr('2')); c_out_group_lis CONSTANT L1_chr_arr := L1_chr_arr ('Counts'); c_out_fields_2lis CONSTANT L2_chr_arr := L2_chr_arr (L1_chr_arr ('*#Records')); BEGIN Do_Test (c_proc_name, c_exp_2lis, c_out_group_lis, c_out_fields_2lis); END tt_AIP_List_Asis; /*************************************************************************************************** tt_AIP_Sort_By_Key: Entry procedure for TRAPIT testing Col_Group.AIP_Sort_By_Key ***************************************************************************************************/ PROCEDURE tt_AIP_Sort_By_Key IS c_proc_name CONSTANT VARCHAR2(61) := c_proc_name_key; c_exp_2lis CONSTANT L2_chr_arr := L2_chr_arr (L1_chr_arr (Utils.List_Delim ('A','2'), Utils.List_Delim ('Bx','1'), Utils.List_Delim ('Cc','1')), L1_chr_arr (Utils.List_Delim ('X','2')) ); BEGIN Do_Test (c_proc_name, c_exp_2lis, c_out_group_lis, c_out_fields_2lis); END tt_AIP_Sort_By_Key; /*************************************************************************************************** tt_AIP_Sort_By_Value: Entry procedure for TRAPIT testing Col_Group.AIP_Sort_By_Value ***************************************************************************************************/ PROCEDURE tt_AIP_Sort_By_Value IS c_proc_name CONSTANT VARCHAR2(61) := c_proc_name_value; c_exp_2lis CONSTANT L2_chr_arr := L2_chr_arr (L1_chr_arr (Utils.List_Delim ('B','1'), Utils.List_Delim ('Cc','1'), Utils.List_Delim ('A','2')), L1_chr_arr (Utils.List_Delim ('X','2')) ); BEGIN Do_Test (c_proc_name, c_exp_2lis, c_out_group_lis, c_out_fields_2lis); END tt_AIP_Sort_By_Value; END TT_Col_Group; /

TRAPIT Output


PL/SQL procedure successfully completed.


TEXT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

TRAPIT TEST: Col_Group.tt_AIP_List_Asis
=======================================

SCENARIO 1: Tie-break/single-delimiter/interior column {
========================================================

    INPUTS
    ======

        GROUP Parameter {
        =================

            File Name  Delimiter  Column
            ---------  ---------  ------
            lines.csv  ,               3

        }
        =

        GROUP File {
        ============

            Line
            -----------
            0,1,Cc,3
            00,1,A,9
            000,1,B,27
            0000,1,A,81

        }
        =

    OUTPUTS
    =======

        GROUP Counts: Actual = 1, Expected = 1 {
        ========================================

            F?  #Records
            --  --------
                       3

        } 0 failed, of 1: SUCCESS
        =========================

} 0 failed, of 1: SUCCESS
=========================

SCENARIO 2: Two copies/double-delimiter/first column {
======================================================

    INPUTS
    ======

        GROUP Parameter {
        =================

            File Name  Delimiter  Column
            ---------  ---------  ------
            lines.csv  ;;              1

        }
        =

        GROUP File {
        ============

            Line
            -------
            X;;1;;A
            X;;1;;A

        }
        =

    OUTPUTS
    =======

        GROUP Counts: Actual = 1, Expected = 1 {
        ========================================

            F?  #Records
            --  --------
            F          1
            >          2

        } 1 failed, of 1: FAILURE
        =========================

} 1 failed, of 1: FAILURE
=========================

TIMING: Actual = 0, Expected <= 2: SUCCESS
==========================================

SUMMARY for Col_Group.tt_AIP_List_Asis
======================================

Scenario                                    # Failed  # Tests  Status
------------------------------------------  --------  -------  -------
Tie-break/single-delimiter/interior column         0        1  SUCCESS
Two copies/double-delimiter/first column           1        1  FAILURE
Timing                                             0        1  SUCCESS
------------------------------------------  --------  -------  -------
Total                                              1        3  FAILURE
------------------------------------------  --------  -------  -------

Timer Set: Col_Group.tt_AIP_List_Asis, Constructed at 13 Nov 2016 09:07:08, written at 09:07:08
===============================================================================================
[Timer timed: Elapsed (per call): 0.01 (0.000006), CPU (per call): 0.00 (0.000000), calls: 1000, '***' denotes corrected line below]

Timer       Elapsed         CPU         Calls       Ela/Call       CPU/Call
-------  ----------  ----------  ------------  -------------  -------------
Setup          0.07        0.03             2        0.03700        0.01500
Caller         0.00        0.00             2        0.00000        0.00000
(Other)        0.01        0.01             1        0.00900        0.01000
-------  ----------  ----------  ------------  -------------  -------------
Total          0.08        0.04             5        0.01660        0.00800
-------  ----------  ----------  ------------  -------------  -------------

TRAPIT TEST: Col_Group.tt_AIP_Sort_By_Key
=========================================

SCENARIO 1: Tie-break/single-delimiter/interior column {
========================================================

    INPUTS
    ======

        GROUP Parameter {
        =================

            File Name  Delimiter  Column
            ---------  ---------  ------
            lines.csv  ,               3

        }
        =

        GROUP File {
        ============

            Line
            -----------
            0,1,Cc,3
            00,1,A,9
            000,1,B,27
            0000,1,A,81

        }
        =

    OUTPUTS
    =======

        GROUP Sorted Array: Actual = 3, Expected = 3 {
        ==============================================

            F?  Key  Count
            --  ---  -----
                A        2
            F   B        1
            >   Bx       1
                Cc       1

        } 1 failed, of 3: FAILURE
        =========================

} 1 failed, of 3: FAILURE
=========================

SCENARIO 2: Two copies/double-delimiter/first column {
======================================================

    INPUTS
    ======

        GROUP Parameter {
        =================

            File Name  Delimiter  Column
            ---------  ---------  ------
            lines.csv  ;;              1

        }
        =

        GROUP File {
        ============

            Line
            -------
            X;;1;;A
            X;;1;;A

        }
        =

    OUTPUTS
    =======

        GROUP Sorted Array: Actual = 1, Expected = 1 {
        ==============================================

            F?  Key  Count
            --  ---  -----
                X        2

        } 0 failed, of 1: SUCCESS
        =========================

} 0 failed, of 1: SUCCESS
=========================

TIMING: Actual = 2, Expected <= 2: SUCCESS
==========================================

SUMMARY for Col_Group.tt_AIP_Sort_By_Key
========================================

Scenario                                    # Failed  # Tests  Status
------------------------------------------  --------  -------  -------
Tie-break/single-delimiter/interior column         1        3  FAILURE
Two copies/double-delimiter/first column           0        1  SUCCESS
Timing                                             0        1  SUCCESS
------------------------------------------  --------  -------  -------
Total                                              1        5  FAILURE
------------------------------------------  --------  -------  -------

Timer Set: Col_Group.tt_AIP_Sort_By_Key, Constructed at 13 Nov 2016 09:07:08, written at 09:07:08
=================================================================================================
[Timer timed: Elapsed (per call): 0.01 (0.000006), CPU (per call): 0.00 (0.000000), calls: 1000, '***' denotes corrected line below]

Timer       Elapsed         CPU         Calls       Ela/Call       CPU/Call
-------  ----------  ----------  ------------  -------------  -------------
Setup          0.03        0.03             2        0.01400        0.01500
Caller         0.00        0.00             2        0.00150        0.00000
(Other)        0.01        0.02             1        0.01000        0.02000
-------  ----------  ----------  ------------  -------------  -------------
Total          0.04        0.05             5        0.00820        0.01000
-------  ----------  ----------  ------------  -------------  -------------

TRAPIT TEST: Col_Group.tt_AIP_Sort_By_vALUE
===========================================

SCENARIO 1: Tie-break/single-delimiter/interior column {
========================================================

    INPUTS
    ======

        GROUP Parameter {
        =================

            File Name  Delimiter  Column
            ---------  ---------  ------
            lines.csv  ,               3

        }
        =

        GROUP File {
        ============

            Line
            -----------
            0,1,Cc,3
            00,1,A,9
            000,1,B,27
            0000,1,A,81

        }
        =

    OUTPUTS
    =======

        GROUP Sorted Array: Actual = 3, Expected = 3 {
        ==============================================

            F?  Key  Count
            --  ---  -----
                B        1
                Cc       1
                A        2

        } 0 failed, of 3: SUCCESS
        =========================

} 0 failed, of 3: SUCCESS
=========================

SCENARIO 2: Two copies/double-delimiter/first column {
======================================================

    INPUTS
    ======

        GROUP Parameter {
        =================

            File Name  Delimiter  Column
            ---------  ---------  ------
            lines.csv  ;;              1

        }
        =

        GROUP File {
        ============

            Line
            -------
            X;;1;;A
            X;;1;;A

        }
        =

    OUTPUTS
    =======

        GROUP Sorted Array: Actual = 1, Expected = 1 {
        ==============================================

            F?  Key  Count
            --  ---  -----
                X        2

        } 0 failed, of 1: SUCCESS
        =========================

} 0 failed, of 1: SUCCESS
=========================

TIMING: Actual = 1, Expected <= 2: SUCCESS
==========================================

SUMMARY for Col_Group.tt_AIP_Sort_By_vALUE
==========================================

Scenario                                    # Failed  # Tests  Status
------------------------------------------  --------  -------  -------
Tie-break/single-delimiter/interior column         0        3  SUCCESS
Two copies/double-delimiter/first column           0        1  SUCCESS
Timing                                             0        1  SUCCESS
------------------------------------------  --------  -------  -------
Total                                              0        5  SUCCESS
------------------------------------------  --------  -------  -------

Timer Set: Col_Group.tt_AIP_Sort_By_vALUE, Constructed at 13 Nov 2016 09:07:08, written at 09:07:08
===================================================================================================
[Timer timed: Elapsed (per call): 0.01 (0.000008), CPU (per call): 0.01 (0.000010), calls: 1000, '***' denotes corrected line below]

Timer       Elapsed         CPU         Calls       Ela/Call       CPU/Call
-------  ----------  ----------  ------------  -------------  -------------
Setup          0.03        0.03             2        0.01450        0.01500
Caller         0.00        0.00             2        0.00050        0.00000
(Other)        0.01        0.02             1        0.01100        0.02000
-------  ----------  ----------  ------------  -------------  -------------
Total          0.04        0.05             5        0.00820        0.01000
-------  ----------  ----------  ------------  -------------  -------------

Suite Summary
=============

Package.Procedure               Tests  Fails         ELA         CPU
------------------------------  -----  -----  ----------  ----------
Col_Group.tt_AIP_List_Asis          3      1        0.08        0.04
Col_Group.tt_AIP_Sort_By_Key        5      1        0.04        0.05
Col_Group.tt_AIP_Sort_By_vALUE      5      0        0.04        0.05
------------------------------  -----  -----  ----------  ----------
Total                              13      2        0.17        0.14
------------------------------  -----  -----  ----------  ----------
Others error in (): ORA-20001: Suite BRENDAN returned error status: ORA-06512: at "LIB.UTILS_TT", line 152
ORA-06512: at "LIB.UTILS_TT", line 819
ORA-06512: at line 5


376 rows selected.

TRAPIT Notes

  • The approach to code-sharing and data-driving is similar to that used in the utPLSQL version
  • No assertions are made at all in the client code; the actual values are collected and passed to the library procedure for assertion
  • The famous 'arrange-act-assert' OO pattern is therefore not followed, with no ill effects
  • The output displays all inputs and outputs in 3-level format: Scenario/Group/Record with scenario descriptions, group names and column headings passed in

Oracle - SQL Developer Unit Test

I briefly tried to use this gui framework as well, but soon gave up when I could not see how to handle the object array return values.

Conclusions

  • Some significant differences in the functionality of the frameworks between utPLSQL and JUnit have been noted
  • Following design patterns for testing from the OO world may not always be advisable
  • It may be best to drop the term 'unit testing' for the database altogether, with the understanding that testing only transactional APIs such as web service procedures is a much more efficient approach
  • Consider using a data-driven approach to testing multiple scenarios, in testing both database and Java code, where applicable

All code and output can be seen on polyglot_group on GitHub, where Python, Ruby, Perl and Scala versions are also included.






 

Design Patterns for Database API Testing 4: REF Cursor Getter

In TRAPIT - TRansactional API Testing in Oracle, I supplied installation scripts for a new Oracle PL/SQL API testing framework, together with working example API test programs for four use cases against Oracle's HR demo schema. I linked to articles describing three of these example test programs, and the current article describes the fourth example.

The four examples were designed to cover the general categories of getting and setting data in batch or real time mode. This fourth example covers getting data in real time via a REF cursor, such as would often be used as the source for a web service.

Requirement Summary

Modern Oracle SQL is very powerful and can apply complex logic within a single statement, reducing the need for more complex procedural code. In order to show how to API test SQL that might be used in a batch getter module, we previously devised a test view, HR_Test_V, having a range of features that we might want to test in general. We can use similar SQL to demonstrate API testing of a real time getter procedure that might be used by a web service, where reference cursors are often used as output parameters. The following list of features to test is taken, in slightly modified form, from Design Patterns for Database API Testing 2: Views 1 - Design

  • Inner joins suppress driving records where there is no joining record
  • Outer joins return driving records where there is no joining record
  • Analytic functions that partition by some key, and return aggregates on the returned record set
  • Functions based on aggregates over records that include those not in the returned record set
  • Constraints based on aggregates over records that include those not in the returned record set
  • Constraints on column values

The SQL functionality can be described in words as:

  • Selected values
    • Employee name, department name, and salary
    • Manager's name
    • Ratio of employee's salary to the department average (returned employees only)
    • Ratio of employee's salary to the average salary of all employees
  • Constraints
    • Exclude employees in job 'AD_ASST'
    • Return employees for a department passed as a bind parameter
    • Do not return any records if the total salary of all employees is below 1600
  • Outer join
    • Include employees both with and without a manager

The REF cursor SQL is:

  WITH all_emps AS (
        SELECT Avg (salary) avg_sal, SUM (salary) sal_tot_g
          FROM employees e
)
SELECT e.last_name, d.department_name, m.last_name manager, e.salary,
       Round (e.salary / Avg (e.salary) OVER (PARTITION BY e.department_id), 2) sal_rat,
       Round (e.salary / a.avg_sal, 2) sal_rat_g
  FROM all_emps a
 CROSS JOIN employees e
  JOIN departments d
    ON d.department_id = e.department_id
  LEFT JOIN employees m
    ON m.employee_id = e.manager_id
 WHERE e.job_id != 'AD_ASST'
   AND a.sal_tot_g >= 1600
   AND d.department_id = :1

Notes on API Testing REF Cursor Procedures

  • A new utility function has been added to UT_Utils, Cursor_to_Array, that converts an open reference cursor to a delimited list of strings (created from an initial stand-alone procedure: A Utility for Reading REF Cursors into a List of Delimited Strings)
    • Using this utility, very little code needs to be written once the test data has been set up: One call to return the reference cursor, and a second to return the actual values in a list, to be passed at the end in a single call to the library results checker
  • The entire body section of the procedure is given below
    • Notice that c_scenario_ds_lis holds pointers to datasets to allow re-use for multiple scenarios (with different parameter data)
  l_timer_set := UT_Utils.Init (c_proc_name);
  l_act_2lis.EXTEND (c_exp_2lis.COUNT);
  l_inp_3lis.EXTEND (c_exp_2lis.COUNT);

  FOR i IN 1..c_exp_2lis.COUNT LOOP

    l_inp_3lis (i) := L2_chr_arr();
    l_inp_3lis (i).EXTEND(2);

    Setup (c_scenario_ds_lis (i), l_inp_3lis (i)(1));

    l_inp_3lis (i)(2) := L1_chr_arr (c_dep_lis(i));
    Timer_Set.Increment_Time (l_timer_set, UT_Utils.c_setup_timer);

    Emp_WS.AIP_Get_Dept_Emps (p_dep_id  => c_dep_lis(i),
                              x_emp_csr => l_emp_csr);
    l_act_2lis(i) := UT_Utils.List_or_Empty 
    (UT_Utils.Cursor_to_Array (x_csr => l_emp_csr));
    Timer_Set.Increment_Time (l_timer_set, UT_Utils.c_call_timer);
    ROLLBACK;

  END LOOP;

  UT_Utils.Check_UT_Results (c_proc_name, c_scenario_lis, l_inp_3lis, l_act_2lis, c_exp_2lis, l_timer_set, c_ms_limit,
                             c_inp_group_lis, c_inp_field_2lis, c_out_group_lis, c_out_field_2lis);

ERD

unit-testing-three-erd_rc

Design Pattern Groups

The API testing framework is centred around the concept of input and output groups, representing the data sets that respectively form the inputs to, and outputs from, the program. The records in each group are printed by the framework with column headers for each scenario. These groups are identified by the developer, and in this case they are as noted below.

Input Groups

  • Employees Table
  • Department Parameter

Output Groups

  • Select results
  • Timing of average call

Test Scenarios

The scenario descriptions start with a data set code, followed by a verbal description.

  1. DS-1, testing inner, outer joins, analytic over dep, and global ratios with 1 dep (10) - pass dep 10
  2. DS-2, testing same as 1 but with extra emp in another dep (20) - pass dep 10
  3. DS-2, as second scenario, but - pass dep 20
  4. DS-2, as second scenario, but - pass null dep
  5. DS-3, Salaries total 1500 (< threshold of 1600, so return nothing) - pass dep 10

Code Structure Diagram

unit-testing-three-csd_rc

Call Structure Table - UT_Emp_WS.ut_AIP_Get_Dept_Emps

Level 1 Level 2 Package
List_Delim Utils
Init UT_Utils
Setup UT_Emp_WS
Heading Utils
Col_Headers Utils
Ins_Emp DML_API_UT_HR
Pr_List_As_Line Utils
Increment_Time Timer_Set
AIP_Get_Dept_Emps Emp_WS
Cursor_to_Array UT_Utils
List_or_Empty UT_Utils
Check_UT_Results UT_Utils
Write_Other_Error Utils

Test Output

UNIT TEST for UT_Emp_WS.ut_AIP_Get_Dept_Emps
============================================

Employees created in setup: DS-1 - 4 emps, 1 dep (10), emp-3 has no dep, emp-4 has bad job
==========================================================================================

#  Employee id  Department id     Manager  Job id          Salary
-  -----------  -------------  ----------  ----------  ----------
1         1658             10              IT_PROG           1000
2         1659             10        1658  IT_PROG           2000
3         1660                       1658  IT_PROG           3000
4         1661             10        1658  AD_ASST           4000

Employees created in setup: DS-2 - As dataset 1 but with extra emp-5, in second dep (20)
========================================================================================

#  Employee id  Department id     Manager  Job id          Salary
-  -----------  -------------  ----------  ----------  ----------
1         1662             10              IT_PROG           1000
2         1663             10        1662  IT_PROG           2000
3         1664                       1662  IT_PROG           3000
4         1665             10        1662  AD_ASST           4000
5         1666             20        1662  IT_PROG           5000

Employees created in setup: DS-2 - As dataset 1 but with extra emp-5, in second dep (20)
========================================================================================

#  Employee id  Department id     Manager  Job id          Salary
-  -----------  -------------  ----------  ----------  ----------
1         1667             10              IT_PROG           1000
2         1668             10        1667  IT_PROG           2000
3         1669                       1667  IT_PROG           3000
4         1670             10        1667  AD_ASST           4000
5         1671             20        1667  IT_PROG           5000

Employees created in setup: DS-2 - As dataset 1 but with extra emp-5, in second dep (20)
========================================================================================

#  Employee id  Department id     Manager  Job id          Salary
-  -----------  -------------  ----------  ----------  ----------
1         1672             10              IT_PROG           1000
2         1673             10        1672  IT_PROG           2000
3         1674                       1672  IT_PROG           3000
4         1675             10        1672  AD_ASST           4000
5         1676             20        1672  IT_PROG           5000

Employees created in setup: DS-3 - As dataset 2 but with salaries * 0.1, total below reporting threshold of 1600
================================================================================================================

#  Employee id  Department id     Manager  Job id          Salary
-  -----------  -------------  ----------  ----------  ----------
1         1677             10              IT_PROG            100
2         1678             10        1677  IT_PROG            200
3         1679                       1677  IT_PROG            300
4         1680             10        1677  AD_ASST            400
5         1681             20        1677  IT_PROG            500

SCENARIO 1: DS-1, testing inner, outer joins, analytic over dep, and global ratios with 1 dep (10) - pass dep 10 {
==================================================================================================================

    INPUTS
    ======

        GROUP Employee {
        ================

            Employee Id  Last Name  Email  Hire Date    Job      Salary  Manager Id  Department Id  Updated
            -----------  ---------  -----  -----------  -------  ------  ----------  -------------  -----------
                   1658  LN_1       EM_1   01-OCT-2016  IT_PROG    1000                         10  01-OCT-2016
                   1659  LN_2       EM_2   01-OCT-2016  IT_PROG    2000        1658             10  01-OCT-2016
                   1660  LN_3       EM_3   01-OCT-2016  IT_PROG    3000        1658                 01-OCT-2016
                   1661  LN_4       EM_4   01-OCT-2016  AD_ASST    4000        1658             10  01-OCT-2016

        }
        =

        GROUP Department Parameter {
        ============================

            Department Id
            -------------
                       10

        }
        =

    OUTPUTS
    =======

        GROUP Select results: Actual = 2, Expected = 2 {
        ================================================

            F?  Name  Department      Manager  Salary  Salary Ratio (dep)  Salary Ratio (overall)
            --  ----  --------------  -------  ------  ------------------  ----------------------
                LN_1  Administration             1000                 .67                      .4
                LN_2  Administration  LN_1       2000                1.33                      .8

        } 0 failed, of 2: SUCCESS
        =========================

} 0 failed, of 2: SUCCESS
=========================

SCENARIO 2: DS-2, testing same as 1 but with extra emp in another dep (20) - pass dep 10 {
==========================================================================================

    INPUTS
    ======

        GROUP Employee {
        ================

            Employee Id  Last Name  Email  Hire Date    Job      Salary  Manager Id  Department Id  Updated
            -----------  ---------  -----  -----------  -------  ------  ----------  -------------  -----------
                   1662  LN_1       EM_1   01-OCT-2016  IT_PROG    1000                         10  01-OCT-2016
                   1663  LN_2       EM_2   01-OCT-2016  IT_PROG    2000        1662             10  01-OCT-2016
                   1664  LN_3       EM_3   01-OCT-2016  IT_PROG    3000        1662                 01-OCT-2016
                   1665  LN_4       EM_4   01-OCT-2016  AD_ASST    4000        1662             10  01-OCT-2016
                   1666  LN_5       EM_5   01-OCT-2016  IT_PROG    5000        1662             20  01-OCT-2016

        }
        =

        GROUP Department Parameter {
        ============================

            Department Id
            -------------
                       10

        }
        =

    OUTPUTS
    =======

        GROUP Select results: Actual = 2, Expected = 2 {
        ================================================

            F?  Name  Department      Manager  Salary  Salary Ratio (dep)  Salary Ratio (overall)
            --  ----  --------------  -------  ------  ------------------  ----------------------
                LN_1  Administration             1000                 .67                     .33
                LN_2  Administration  LN_1       2000                1.33                     .67

        } 0 failed, of 2: SUCCESS
        =========================

} 0 failed, of 2: SUCCESS
=========================

SCENARIO 3: DS-2, as second scenario, but - pass dep 20 {
=========================================================

    INPUTS
    ======

        GROUP Employee {
        ================

            Employee Id  Last Name  Email  Hire Date    Job      Salary  Manager Id  Department Id  Updated
            -----------  ---------  -----  -----------  -------  ------  ----------  -------------  -----------
                   1667  LN_1       EM_1   01-OCT-2016  IT_PROG    1000                         10  01-OCT-2016
                   1668  LN_2       EM_2   01-OCT-2016  IT_PROG    2000        1667             10  01-OCT-2016
                   1669  LN_3       EM_3   01-OCT-2016  IT_PROG    3000        1667                 01-OCT-2016
                   1670  LN_4       EM_4   01-OCT-2016  AD_ASST    4000        1667             10  01-OCT-2016
                   1671  LN_5       EM_5   01-OCT-2016  IT_PROG    5000        1667             20  01-OCT-2016

        }
        =

        GROUP Department Parameter {
        ============================

            Department Id
            -------------
                       20

        }
        =

    OUTPUTS
    =======

        GROUP Select results: Actual = 1, Expected = 1 {
        ================================================

            F?  Name  Department  Manager  Salary  Salary Ratio (dep)  Salary Ratio (overall)
            --  ----  ----------  -------  ------  ------------------  ----------------------
                LN_5  Marketing   LN_1       5000                   1                    1.67

        } 0 failed, of 1: SUCCESS
        =========================

} 0 failed, of 1: SUCCESS
=========================

SCENARIO 4: DS-2, as second scenario, but - pass null dep {
===========================================================

    INPUTS
    ======

        GROUP Employee {
        ================

            Employee Id  Last Name  Email  Hire Date    Job      Salary  Manager Id  Department Id  Updated
            -----------  ---------  -----  -----------  -------  ------  ----------  -------------  -----------
                   1672  LN_1       EM_1   01-OCT-2016  IT_PROG    1000                         10  01-OCT-2016
                   1673  LN_2       EM_2   01-OCT-2016  IT_PROG    2000        1672             10  01-OCT-2016
                   1674  LN_3       EM_3   01-OCT-2016  IT_PROG    3000        1672                 01-OCT-2016
                   1675  LN_4       EM_4   01-OCT-2016  AD_ASST    4000        1672             10  01-OCT-2016
                   1676  LN_5       EM_5   01-OCT-2016  IT_PROG    5000        1672             20  01-OCT-2016

        }
        =

        GROUP Department Parameter {
        ============================

            Department Id
            -------------


        }
        =

    OUTPUTS
    =======

        GROUP Select results: Actual = 0, Expected = 0: SUCCESS
        =======================================================

} 0 failed, of 1: SUCCESS
=========================

SCENARIO 5: DS-3, Salaries total 1500 (< threshold of 1600, so return nothing) - pass dep 10 {
==============================================================================================

    INPUTS
    ======

        GROUP Employee {
        ================

            Employee Id  Last Name  Email  Hire Date    Job      Salary  Manager Id  Department Id  Updated
            -----------  ---------  -----  -----------  -------  ------  ----------  -------------  -----------
                   1677  LN_1       EM_1   01-OCT-2016  IT_PROG     100                         10  01-OCT-2016
                   1678  LN_2       EM_2   01-OCT-2016  IT_PROG     200        1677             10  01-OCT-2016
                   1679  LN_3       EM_3   01-OCT-2016  IT_PROG     300        1677                 01-OCT-2016
                   1680  LN_4       EM_4   01-OCT-2016  AD_ASST     400        1677             10  01-OCT-2016
                   1681  LN_5       EM_5   01-OCT-2016  IT_PROG     500        1677             20  01-OCT-2016

        }
        =

        GROUP Department Parameter {
        ============================

            Department Id
            -------------
                       10

        }
        =

    OUTPUTS
    =======

        GROUP Select results: Actual = 0, Expected = 0: SUCCESS
        =======================================================

} 0 failed, of 1: SUCCESS
=========================

TIMING: Actual = 7, Expected <= 1: FAILURE
==========================================

SUMMARY for UT_Emp_WS.ut_AIP_Get_Dept_Emps
==========================================

Scenario                                                                                              # Failed  # Tests  Status
----------------------------------------------------------------------------------------------------  --------  -------  -------
DS-1, testing inner, outer joins, analytic over dep, and global ratios with 1 dep (10) - pass dep 10         0        2  SUCCESS
DS-2, testing same as 1 but with extra emp in another dep (20) - pass dep 10                                 0        2  SUCCESS
DS-2, as second scenario, but - pass dep 20                                                                  0        1  SUCCESS
DS-2, as second scenario, but - pass null dep                                                                0        1  SUCCESS
DS-3, Salaries total 1500 (< threshold of 1600, so return nothing) - pass dep 10                             0        1  SUCCESS
Timing                                                                                                       1        1  FAILURE
----------------------------------------------------------------------------------------------------  --------  -------  -------
Total                                                                                                        1        8  FAILURE
----------------------------------------------------------------------------------------------------  --------  -------  -------

Timer Set: UT_Emp_WS.ut_AIP_Get_Dept_Emps, Constructed at 01 Oct 2016 09:14:12, written at 09:14:13
===================================================================================================
[Timer timed: Elapsed (per call): 0.03 (0.000034), CPU (per call): 0.03 (0.000030), calls: 1000, '***' denotes corrected line below]

Timer       Elapsed         CPU         Calls       Ela/Call       CPU/Call
-------  ----------  ----------  ------------  -------------  -------------
Setup          0.08        0.05             5        0.01660        0.01000
Caller         0.03        0.01             5        0.00680        0.00200
(Other)        0.10        0.10             1        0.09500        0.10000
-------  ----------  ----------  ------------  -------------  -------------
Total          0.21        0.16            11        0.01927        0.01455
-------  ----------  ----------  ------------  -------------  -------------






 

Design Patterns for Database API Testing 3: Batch Loading of Flat Files

In TRAPIT - TRansactional API Testing in Oracle, I supplied installation scripts for a new Oracle PL/SQL unit testing framework, together with working example unit test programs for four use cases against Oracle's HR demo schema. I linked to articles describing two of these example test programs, and the current article describes the third example, with the fourth to follow shortly.

The four examples were designed to cover the general categories of getting and setting data in batch or real time mode. This third example covers batch loading of data into a database table from file by means of external tables. There are many possible variants of this kind of interface, and I tried to combine the features from past projects that seemed to work best. In particular:

  • The load procedure is assumed to be executed from an operating system script (normally Unix) that manages the input files, copying from source files into a single file for reading by the external table, looping over multiple files where necessary, and archiving processed files
    • It is possible to dynamically map the external table to multiple files, but that approach involves executing DDL and seems to be generally more complex
    • The idea is to do the different types of processing at the level most appropriate, so avoiding excessive file processing within the database
  • SQL operations are performed at set level, rather than within a loop, and the set concerned comprises the entire contents of the external table
    • This assumes that the Oracle internal working space requirements (such as size of TEMP tablespace) are not exceeded
    • The alternative approach of fetching batches of records into arrays for loading tends to be more complex, and less efficient
    • It may be preferable to restrict the size of the input files where necessary instead, either at the source end, or from the controlling operating system script
    • Where loading occurs from staging tables (which is not in scope here) the use of the DBMS_Parallel_Execute package to control transaction size looks an attractive modern approach (from v11, DBMS_PARALLEL_EXECUTE)
  • Metadata tables are used for specifying job control parameters, and for recording run statistics
    • Run statistics include job status and numbers of records succeeding and failing at both external table and database level
    • A percentage threshold is included in the job control table, that causes the job to fail if a higher percentage of records fails
  • Oracle's DML LOG ERRORS clause is used to divert failing records into an errors table, while processing the other records normally
    • While simpler than other approaches to error handling, prior to v12.1 this clause had a significant performance overhead, but from v12.1 this is no longer the case (see DML Error Logging in Oracle 10g Database Release 2 - it has a table of comparative timings up to v12.1 at the end)
    • The standard Oracle err$ table structure is used, with the addition of two columns, one to identify the associated job run, and the other the utid column used by the framework to identify unit test data
    • In a replication environment a unique identifier would be needed, but is not included here
    • The table is also used to capture records that fail custom validation (here when a record passed as an update does not match an existing record)

Requirement Summary

  • The procedure reads employee records from a single flat file
  • Employee id is an optional field, and if passed the record is treated as an update, otherwise it is an insert
  • A job statistics table is populated with a record on each run, and stores numbers of successful and failed records etc.
  • Records that fail at the database level are inserted into an errors table, extended from Oracle's err$ table format
  • The external table has all fields defined as 4000 bytes character fields to minimise records failing to be read from the external table
  • An audit date column is included, and is set to the current date if any change is made in a record; if an unchanged record is detected no update should be made
  • The load program takes two parameters that would be passed in by an operating system calling script:
    • An identifier for the file, that may be a timestamped original file name (but the external table file has a fixed name)
    • A line count for the file
  • The passed file identifier is saved in the job statistics table, and a repeat identifier will only be accepted if previous records all have failed status
  • A job control table stores an error percentage threshold, above which the run is considered unsuccessfull, and an exception is raised

Notes on Unit Testing Flat File Loads

  • It is considered best practice to keep unit testing code as self-contained as possible, and in order to avoid dependence on external data files, Oracle's UTL_File package is used to delete and create the test files from arrays within the unit test code
    • The Utils package contains two wrapper procedures to facilitate this: Delete_File and Write_File

Extended ERD

unit-testing-two-ff-erd-h

Design Pattern Groups

The unit testing framework is centred around the concept of input and output groups, representing the data sets that respectively form the inputs to, and outputs from, the program. The records in each group are printed by the framework with column headers for each scenario. These groups are identified by the developer, and in this case they are as noted below.

Input Groups

  • Parameters
  • Input File
  • Batch Job Table
  • Job Statistics Table
  • Employees Table

Output Groups

  • Employees Table
  • Errors Table
  • Job Statistics Table
  • Exception
  • Timing of average call

Test Scenarios

Key
The scenario descriptions start with two sets of counts, followed by a verbal description. The first set is on the records coming in from file:

  • NV - new valid records
  • OV - old valid records
  • OU - old unchanged records
  • NI - new invalid records
  • OI - old valid records
  • EI - external table invalid records

The second set is on the existing records in job statistics and employees (other than are in the input file; these are counted as old records in the first set of counts):

  • J - job statistics records
  • E - employees records

Scenario List

  1. NV/OV/OU/NI/OI/EI: 1/0/0/0/0/0. Existing J/E: 0/0. [1 valid new record from scratch]
  2. NV/OV/OU/NI/OI/EI: 1/1/1/0/0/0. Existing J/E: 1/0. [3 valid records of each kind]
  3. NV/OV/OU/NI/OI/EI: 0/0/0/0/1/0. Existing J/E: 1/1. Uid not found [1 invalid old - exception]
  4. NV/OV/OU/NI/OI/EI: 0/0/0/1/0/0. Existing J/E: 1/1. Email too long [1 invalid new - exception]
  5. NV/OV/OU/NI/OI/EI: 1/0/0/0/1/0. Existing J/E: 1/1. Name too long [1 valid new, 1 invalid old - no exception]
  6. NV/OV/OU/NI/OI/EI: 0/0/0/1/0/0. Existing J/E: 1/1. Invalid job [1 invalid new - exception]
  7. NV/OV/OU/NI/OI/EI: 0/1/0/1/1/0. Existing J/E: 1/2. 2 invalid jobs [1 valid old, 2 invalid: old and new - no exception]
  8. NV/OV/OU/NI/OI/EI: 0/1/0/0/0/1. Existing J/E: 1/2. Name 4001ch [1 valid old, 1 invalid new for external table - no exception; also file had previously failed]
  9. NV/OV/OU/NI/OI/EI: 0/0/0/1/0/0. Existing J/E: 1/1. [File already processed - exception]

Code Structure Diagram

unit-testing-two-ff-csd-h

Call Structure Table - UT_Emp_Batch.ut_AIP_Load_Emps

Level 1 Level 2 Level 3 Package
Init UT_Utils
Setup UT_Emp_Batch
Replace_Seq_Offset UT_Emp_Batch
Ins_jbs DML_API_UT_Bren
Ins_Emp DML_API_UT_HR
Delete_File Utils
Write_File Utils
Increment_Time Timer_Set
Call_Proc UT_Emp_Batch
Init_Time Timer_Set
AIP_Load_Emps Emp_Batch
Increment_Time Timer_Set
Get_Tab_Lis UT_Emp_Batch
List_Delim Utils
Get_Err_Lis UT_Emp_Batch
List_Delim Utils
Get_Jbs_Lis UT_Emp_Batch
List_Delim Utils
List_or_Empty UT_Utils
Check_UT_Results UT_Utils
Write_Other_Error Utils

Test Output

UNIT TEST for UT_Emp_Batch.ut_AIP_Load_Emps
===========================================
employees.dat was not present to delete!

SCENARIO 1: NV/OV/OU/NI/OI/EI: 1/0/0/0/0/0. Existing J/E: 0/0. [1 valid new record from scratch] {
==================================================================================================

    INPUTS
    ======

        GROUP Parameter {
        =================

            File Name               Count
            ----------------------  -----
            employees_20160801.dat      1

        }
        =

        GROUP File {
        ============

            Line
            ------------------------------------
            ,LN 1,EM 1,01-JAN-2010,IT_PROG,10000

        }
        =

        GROUP Batch Job Table {
        =======================

            Name       Fail Percent
            ---------  ------------
            LOAD_EMPS            70

        }
        =

        GROUP Statistics Table (No records)
        ===================================

        GROUP Employees Table (No records)
        ==================================

    OUTPUTS
    =======

        GROUP Employee: Actual = 1, Expected = 1 {
        ==========================================

            F?  Employee Id  Name  Email  Hired        Job      Salary  Updated
            --  -----------  ----  -----  -----------  -------  ------  -----------
                       1627  LN 1  EM 1   01-JAN-2010  IT_PROG  10000   11-SEP-2016

        } 0 failed, of 1: SUCCESS
        =========================

        GROUP Error: Actual = 0, Expected = 0: SUCCESS
        ==============================================

        GROUP Job Statistic: Actual = 1, Expected = 1 {
        ===============================================

            F?  Job Statistic Id  Batch job Id  File Name               Records Loaded  Records Failed ET  Records Failed DB  Start Time   End Time     Status
            --  ----------------  ------------  ----------------------  --------------  -----------------  -----------------  -----------  -----------  ------
                               3  LOAD_EMPS     employees_20160801.dat               1                  0                  0  11-SEP-2016  11-SEP-2016  S

        } 0 failed, of 1: SUCCESS
        =========================

        GROUP Exception: Actual = 0, Expected = 0: SUCCESS
        ==================================================

} 0 failed, of 4: SUCCESS
=========================

SCENARIO 2: NV/OV/OU/NI/OI/EI: 1/1/1/0/0/0. Existing J/E: 1/0. [3 valid records of each kind] {
===============================================================================================

    INPUTS
    ======

        GROUP Parameter {
        =================

            File Name               Count
            ----------------------  -----
            employees_20160801.dat      3

        }
        =

        GROUP File {
        ============

            Line
            -----------------------------------------
            ,LN 1,EM 1,01-JAN-2010,IT_PROG,10000
            1629,LN 2,EM 2,01-JAN-2010,IT_PROG,20000
            1630,LN 3U,EM 3,01-JAN-2010,IT_PROG,30000

        }
        =

        GROUP Batch Job Table {
        =======================

            Name       Fail Percent
            ---------  ------------
            LOAD_EMPS            70

        }
        =

        GROUP Statistics Table {
        ========================

            Job Statistic Id  Batch job Id  File Name               Records Loaded  Records Failed ET  Records Failed DB  Start Time   End Time     Status
            ----------------  ------------  ----------------------  --------------  -----------------  -----------------  -----------  -----------  ------
                           5  LOAD_EMPS     employees_20160101.dat              10                  0                  2  01-JAN-2010  01-JAN-2010  S

        }
        =

        GROUP Employees Table {
        =======================

            Employee Id  Name  Email  Hired        Job      Salary  Manager Id  Department Id  Updated
            -----------  ----  -----  -----------  -------  ------  ----------  -------------  -----------
                   1629  LN 2  EM 2   01-JAN-2010  IT_PROG   20000                             01-JAN-2010
                   1630  LN 3  EM 3   01-JAN-2010  IT_PROG   30000                             01-JAN-2010

        }
        =

    OUTPUTS
    =======

        GROUP Employee: Actual = 3, Expected = 3 {
        ==========================================

            F?  Employee Id  Name   Email  Hired        Job      Salary  Updated
            --  -----------  -----  -----  -----------  -------  ------  -----------
                       1629  LN 2   EM 2   01-JAN-2010  IT_PROG  20000   01-JAN-2010
                       1630  LN 3U  EM 3   01-JAN-2010  IT_PROG  30000   11-SEP-2016
                       1631  LN 1   EM 1   01-JAN-2010  IT_PROG  10000   11-SEP-2016

        } 0 failed, of 3: SUCCESS
        =========================

        GROUP Error: Actual = 0, Expected = 0: SUCCESS
        ==============================================

        GROUP Job Statistic: Actual = 2, Expected = 2 {
        ===============================================

            F?  Job Statistic Id  Batch job Id  File Name               Records Loaded  Records Failed ET  Records Failed DB  Start Time   End Time     Status
            --  ----------------  ------------  ----------------------  --------------  -----------------  -----------------  -----------  -----------  ------
                               5  LOAD_EMPS     employees_20160101.dat              10                  0                  2  01-JAN-2010  01-JAN-2010  S
                               6  LOAD_EMPS     employees_20160801.dat               2                  0                  0  11-SEP-2016  11-SEP-2016  S

        } 0 failed, of 2: SUCCESS
        =========================

        GROUP Exception: Actual = 0, Expected = 0: SUCCESS
        ==================================================

} 0 failed, of 7: SUCCESS
=========================

SCENARIO 3: NV/OV/OU/NI/OI/EI: 0/0/0/0/1/0. Existing J/E: 1/1. Uid not found [1 invalid old - exception] {
==========================================================================================================

    INPUTS
    ======

        GROUP Parameter {
        =================

            File Name               Count
            ----------------------  -----
            employees_20160801.dat      1

        }
        =

        GROUP File {
        ============

            Line
            --------------------------------------
            99,LN 1,EM 1,01-JAN-2010,IT_PROG,10000

        }
        =

        GROUP Batch Job Table {
        =======================

            Name       Fail Percent
            ---------  ------------
            LOAD_EMPS            70

        }
        =

        GROUP Statistics Table {
        ========================

            Job Statistic Id  Batch job Id  File Name               Records Loaded  Records Failed ET  Records Failed DB  Start Time   End Time     Status
            ----------------  ------------  ----------------------  --------------  -----------------  -----------------  -----------  -----------  ------
                           8  LOAD_EMPS     employees_20160101.dat              10                  0                  2  01-JAN-2010  01-JAN-2010  S

        }
        =

        GROUP Employees Table {
        =======================

            Employee Id  Name  Email  Hired        Job      Salary  Manager Id  Department Id  Updated
            -----------  ----  -----  -----------  -------  ------  ----------  -------------  -----------
                   1633  LN 2  EM 2   01-JAN-2010  IT_PROG   20000                             01-JAN-2010

        }
        =

    OUTPUTS
    =======

        GROUP Employee: Actual = 1, Expected = 1 {
        ==========================================

            F?  Employee Id  Name  Email  Hired        Job      Salary  Updated
            --  -----------  ----  -----  -----------  -------  ------  -----------
                       1633  LN 2  EM 2   01-JAN-2010  IT_PROG  20000   01-JAN-2010

        } 0 failed, of 1: SUCCESS
        =========================

        GROUP Error: Actual = 1, Expected = 1 {
        =======================================

            F?  Job Statistic Id  ORA_ERR_TAG$  ORA_ERR_MESG$       ORA_ERR_OPTYP$  Employee Id  Name  Email  Hired        Job      Salary
            --  ----------------  ------------  ------------------  --------------  -----------  ----  -----  -----------  -------  ------
                               9                Employee not found  PK                       99  LN 1  EM 1   01-JAN-2010  IT_PROG  10000

        } 0 failed, of 1: SUCCESS
        =========================

        GROUP Job Statistic: Actual = 2, Expected = 2 {
        ===============================================

            F?  Job Statistic Id  Batch job Id  File Name               Records Loaded  Records Failed ET  Records Failed DB  Start Time   End Time     Status
            --  ----------------  ------------  ----------------------  --------------  -----------------  -----------------  -----------  -----------  ------
                               8  LOAD_EMPS     employees_20160101.dat              10                  0                  2  01-JAN-2010  01-JAN-2010  S
                               9  LOAD_EMPS     employees_20160801.dat               0                  0                  1  11-SEP-2016  11-SEP-2016  F

        } 0 failed, of 2: SUCCESS
        =========================

        GROUP Exception: Actual = 1, Expected = 1 {
        ===========================================

            F?  Message
            --  ------------------------------------------------------
                ORA-20001: Batch failed with too many invalid records!

        } 0 failed, of 1: SUCCESS
        =========================

} 0 failed, of 5: SUCCESS
=========================

SCENARIO 4: NV/OV/OU/NI/OI/EI: 0/0/0/1/0/0. Existing J/E: 1/1. Email too long [1 invalid new - exception] {
===========================================================================================================

    INPUTS
    ======

        GROUP Parameter {
        =================

            File Name               Count
            ----------------------  -----
            employees_20160801.dat      1

        }
        =

        GROUP File {
        ============

            Line
            ------------------------------------------------------------------
            ,LN 1,EM 1123456789012345678901234567890,01-JAN-2010,IT_PROG,10000

        }
        =

        GROUP Batch Job Table {
        =======================

            Name       Fail Percent
            ---------  ------------
            LOAD_EMPS            70

        }
        =

        GROUP Statistics Table {
        ========================

            Job Statistic Id  Batch job Id  File Name               Records Loaded  Records Failed ET  Records Failed DB  Start Time   End Time     Status
            ----------------  ------------  ----------------------  --------------  -----------------  -----------------  -----------  -----------  ------
                          11  LOAD_EMPS     employees_20160101.dat              10                  0                  2  01-JAN-2010  01-JAN-2010  S

        }
        =

        GROUP Employees Table {
        =======================

            Employee Id  Name  Email  Hired        Job      Salary  Manager Id  Department Id  Updated
            -----------  ----  -----  -----------  -------  ------  ----------  -------------  -----------
                   1635  LN 2  EM 2   01-JAN-2010  IT_PROG   20000                             01-JAN-2010

        }
        =

    OUTPUTS
    =======

        GROUP Employee: Actual = 1, Expected = 1 {
        ==========================================

            F?  Employee Id  Name  Email  Hired        Job      Salary  Updated
            --  -----------  ----  -----  -----------  -------  ------  -----------
                       1635  LN 2  EM 2   01-JAN-2010  IT_PROG  20000   01-JAN-2010

        } 0 failed, of 1: SUCCESS
        =========================

        GROUP Error: Actual = 1, Expected = 1 {
        =======================================

            F?  Job Statistic Id  ORA_ERR_TAG$  ORA_ERR_MESG$                                                                             ORA_ERR_OPTYP$  Employee Id  Name  Email                               Hired        Job      Salary
            --  ----------------  ------------  ----------------------------------------------------------------------------------------  --------------  -----------  ----  ----------------------------------  -----------  -------  ------
                              12                ORA-12899: value too large for column "HR"."EMPLOYEES"."EMAIL" (actual: 34, maximum: 25)  I                      1636  LN 1  EM 1123456789012345678901234567890  01-JAN-2010  IT_PROG  10000

        } 0 failed, of 1: SUCCESS
        =========================

        GROUP Job Statistic: Actual = 2, Expected = 2 {
        ===============================================

            F?  Job Statistic Id  Batch job Id  File Name               Records Loaded  Records Failed ET  Records Failed DB  Start Time   End Time     Status
            --  ----------------  ------------  ----------------------  --------------  -----------------  -----------------  -----------  -----------  ------
                              11  LOAD_EMPS     employees_20160101.dat              10                  0                  2  01-JAN-2010  01-JAN-2010  S
                              12  LOAD_EMPS     employees_20160801.dat               0                  0                  1  11-SEP-2016  11-SEP-2016  F

        } 0 failed, of 2: SUCCESS
        =========================

        GROUP Exception: Actual = 1, Expected = 1 {
        ===========================================

            F?  Message
            --  ------------------------------------------------------
                ORA-20001: Batch failed with too many invalid records!

        } 0 failed, of 1: SUCCESS
        =========================

} 0 failed, of 5: SUCCESS
=========================

SCENARIO 5: NV/OV/OU/NI/OI/EI: 1/0/0/0/1/0. Existing J/E: 1/1. Name too long [1 valid new, 1 invalid old - no exception] {
==========================================================================================================================

    INPUTS
    ======

        GROUP Parameter {
        =================

            File Name               Count
            ----------------------  -----
            employees_20160801.dat      2

        }
        =

        GROUP File {
        ============

            Line
            ----------------------------------------------------------------------
            1638,LN 1123456789012345678901234567890,EM 1,01-JAN-2010,IT_PROG,10000
            ,LN 3,EM 3,01-JAN-2010,IT_PROG,30000

        }
        =

        GROUP Batch Job Table {
        =======================

            Name       Fail Percent
            ---------  ------------
            LOAD_EMPS            70

        }
        =

        GROUP Statistics Table {
        ========================

            Job Statistic Id  Batch job Id  File Name               Records Loaded  Records Failed ET  Records Failed DB  Start Time   End Time     Status
            ----------------  ------------  ----------------------  --------------  -----------------  -----------------  -----------  -----------  ------
                          14  LOAD_EMPS     employees_20160101.dat              10                  0                  2  01-JAN-2010  01-JAN-2010  S

        }
        =

        GROUP Employees Table {
        =======================

            Employee Id  Name  Email  Hired        Job      Salary  Manager Id  Department Id  Updated
            -----------  ----  -----  -----------  -------  ------  ----------  -------------  -----------
                   1638  LN 2  EM 2   01-JAN-2010  IT_PROG   20000                             01-JAN-2010

        }
        =

    OUTPUTS
    =======

        GROUP Employee: Actual = 2, Expected = 2 {
        ==========================================

            F?  Employee Id  Name  Email  Hired        Job      Salary  Updated
            --  -----------  ----  -----  -----------  -------  ------  -----------
                       1638  LN 2  EM 2   01-JAN-2010  IT_PROG  20000   01-JAN-2010
                       1639  LN 3  EM 3   01-JAN-2010  IT_PROG  30000   11-SEP-2016

        } 0 failed, of 2: SUCCESS
        =========================

        GROUP Error: Actual = 1, Expected = 1 {
        =======================================

            F?  Job Statistic Id  ORA_ERR_TAG$  ORA_ERR_MESG$                                                                                 ORA_ERR_OPTYP$  Employee Id  Name                                Email  Hired        Job      Salary
            --  ----------------  ------------  --------------------------------------------------------------------------------------------  --------------  -----------  ----------------------------------  -----  -----------  -------  ------
                              15                ORA-12899: value too large for column "HR"."EMPLOYEES"."LAST_NAME" (actual: 34, maximum: 25)  U                      1638  LN 1123456789012345678901234567890  EM 1   01-JAN-2010  IT_PROG  10000

        } 0 failed, of 1: SUCCESS
        =========================

        GROUP Job Statistic: Actual = 2, Expected = 2 {
        ===============================================

            F?  Job Statistic Id  Batch job Id  File Name               Records Loaded  Records Failed ET  Records Failed DB  Start Time   End Time     Status
            --  ----------------  ------------  ----------------------  --------------  -----------------  -----------------  -----------  -----------  ------
                              14  LOAD_EMPS     employees_20160101.dat              10                  0                  2  01-JAN-2010  01-JAN-2010  S
                              15  LOAD_EMPS     employees_20160801.dat               1                  0                  1  11-SEP-2016  11-SEP-2016  S

        } 0 failed, of 2: SUCCESS
        =========================

        GROUP Exception: Actual = 0, Expected = 0: SUCCESS
        ==================================================

} 0 failed, of 6: SUCCESS
=========================

SCENARIO 6: NV/OV/OU/NI/OI/EI: 0/0/0/1/0/0. Existing J/E: 1/1. Invalid job [1 invalid new - exception] {
========================================================================================================

    INPUTS
    ======

        GROUP Parameter {
        =================

            File Name               Count
            ----------------------  -----
            employees_20160801.dat      1

        }
        =

        GROUP File {
        ============

            Line
            ------------------------------------
            ,LN 1,EM 1,01-JAN-2010,NON_JOB,10000

        }
        =

        GROUP Batch Job Table {
        =======================

            Name       Fail Percent
            ---------  ------------
            LOAD_EMPS            70

        }
        =

        GROUP Statistics Table {
        ========================

            Job Statistic Id  Batch job Id  File Name               Records Loaded  Records Failed ET  Records Failed DB  Start Time   End Time     Status
            ----------------  ------------  ----------------------  --------------  -----------------  -----------------  -----------  -----------  ------
                          17  LOAD_EMPS     employees_20160101.dat              10                  0                  2  01-JAN-2010  01-JAN-2010  S

        }
        =

        GROUP Employees Table {
        =======================

            Employee Id  Name  Email  Hired        Job      Salary  Manager Id  Department Id  Updated
            -----------  ----  -----  -----------  -------  ------  ----------  -------------  -----------
                   1641  LN 2  EM 2   01-JAN-2010  IT_PROG   20000                             01-JAN-2010

        }
        =

    OUTPUTS
    =======

        GROUP Employee: Actual = 1, Expected = 1 {
        ==========================================

            F?  Employee Id  Name  Email  Hired        Job      Salary  Updated
            --  -----------  ----  -----  -----------  -------  ------  -----------
                       1641  LN 2  EM 2   01-JAN-2010  IT_PROG  20000   01-JAN-2010

        } 0 failed, of 1: SUCCESS
        =========================

        GROUP Error: Actual = 1, Expected = 1 {
        =======================================

            F?  Job Statistic Id  ORA_ERR_TAG$  ORA_ERR_MESG$                                                                    ORA_ERR_OPTYP$  Employee Id  Name  Email  Hired        Job      Salary
            --  ----------------  ------------  -------------------------------------------------------------------------------  --------------  -----------  ----  -----  -----------  -------  ------
                              18                ORA-02291: integrity constraint (HR.EMP_JOB_FK) violated - parent key not found  I                      1642  LN 1  EM 1   01-JAN-2010  NON_JOB  10000

        } 0 failed, of 1: SUCCESS
        =========================

        GROUP Job Statistic: Actual = 2, Expected = 2 {
        ===============================================

            F?  Job Statistic Id  Batch job Id  File Name               Records Loaded  Records Failed ET  Records Failed DB  Start Time   End Time     Status
            --  ----------------  ------------  ----------------------  --------------  -----------------  -----------------  -----------  -----------  ------
                              17  LOAD_EMPS     employees_20160101.dat              10                  0                  2  01-JAN-2010  01-JAN-2010  S
                              18  LOAD_EMPS     employees_20160801.dat               0                  0                  1  11-SEP-2016  11-SEP-2016  F

        } 0 failed, of 2: SUCCESS
        =========================

        GROUP Exception: Actual = 1, Expected = 1 {
        ===========================================

            F?  Message
            --  ------------------------------------------------------
                ORA-20001: Batch failed with too many invalid records!

        } 0 failed, of 1: SUCCESS
        =========================

} 0 failed, of 5: SUCCESS
=========================

SCENARIO 7: NV/OV/OU/NI/OI/EI: 0/1/0/1/1/0. Existing J/E: 1/2. 2 invalid jobs [1 valid old, 2 invalid: old and new - no exception] {
====================================================================================================================================

    INPUTS
    ======

        GROUP Parameter {
        =================

            File Name               Count
            ----------------------  -----
            employees_20160801.dat      3

        }
        =

        GROUP File {
        ============

            Line
            -----------------------------------------
            ,LN 1,EM 1,01-JAN-2010,NON_JOB,10000
            1644,LN 2,EM 2,01-JAN-2010,NON_JOB,20000
            1645,LN 3U,EM 3,01-JAN-2010,IT_PROG,30000

        }
        =

        GROUP Batch Job Table {
        =======================

            Name       Fail Percent
            ---------  ------------
            LOAD_EMPS            70

        }
        =

        GROUP Statistics Table {
        ========================

            Job Statistic Id  Batch job Id  File Name               Records Loaded  Records Failed ET  Records Failed DB  Start Time   End Time     Status
            ----------------  ------------  ----------------------  --------------  -----------------  -----------------  -----------  -----------  ------
                          20  LOAD_EMPS     employees_20160101.dat              10                  0                  2  01-JAN-2010  01-JAN-2010  S

        }
        =

        GROUP Employees Table {
        =======================

            Employee Id  Name  Email  Hired        Job      Salary  Manager Id  Department Id  Updated
            -----------  ----  -----  -----------  -------  ------  ----------  -------------  -----------
                   1644  LN 2  EM 2   01-JAN-2010  IT_PROG   20000                             01-JAN-2010
                   1645  LN 3  EM 3   01-JAN-2010  IT_PROG   30000                             01-JAN-2010

        }
        =

    OUTPUTS
    =======

        GROUP Employee: Actual = 2, Expected = 2 {
        ==========================================

            F?  Employee Id  Name   Email  Hired        Job      Salary  Updated
            --  -----------  -----  -----  -----------  -------  ------  -----------
                       1644  LN 2   EM 2   01-JAN-2010  IT_PROG  20000   01-JAN-2010
                       1645  LN 3U  EM 3   01-JAN-2010  IT_PROG  30000   11-SEP-2016

        } 0 failed, of 2: SUCCESS
        =========================

        GROUP Error: Actual = 2, Expected = 2 {
        =======================================

            F?  Job Statistic Id  ORA_ERR_TAG$  ORA_ERR_MESG$                                                                    ORA_ERR_OPTYP$  Employee Id  Name  Email  Hired        Job      Salary
            --  ----------------  ------------  -------------------------------------------------------------------------------  --------------  -----------  ----  -----  -----------  -------  ------
                              21                ORA-02291: integrity constraint (HR.EMP_JOB_FK) violated - parent key not found  U                      1644  LN 2  EM 2   01-JAN-2010  NON_JOB  20000
                              21                ORA-02291: integrity constraint (HR.EMP_JOB_FK) violated - parent key not found  I                      1646  LN 1  EM 1   01-JAN-2010  NON_JOB  10000

        } 0 failed, of 2: SUCCESS
        =========================

        GROUP Job Statistic: Actual = 2, Expected = 2 {
        ===============================================

            F?  Job Statistic Id  Batch job Id  File Name               Records Loaded  Records Failed ET  Records Failed DB  Start Time   End Time     Status
            --  ----------------  ------------  ----------------------  --------------  -----------------  -----------------  -----------  -----------  ------
                              20  LOAD_EMPS     employees_20160101.dat              10                  0                  2  01-JAN-2010  01-JAN-2010  S
                              21  LOAD_EMPS     employees_20160801.dat               1                  0                  2  11-SEP-2016  11-SEP-2016  S

        } 0 failed, of 2: SUCCESS
        =========================

        GROUP Exception: Actual = 0, Expected = 0: SUCCESS
        ==================================================

} 0 failed, of 7: SUCCESS
=========================

SCENARIO 8: NV/OV/OU/NI/OI/EI: 0/1/0/0/0/1. Existing J/E: 1/2. Name 4001ch [1 valid old, 1 invalid new for external table - no exception; also file had previously failed] {
============================================================================================================================================================================

    INPUTS
    ======

        GROUP Parameter {
        =================

            File Name               Count
            ----------------------  -----
            employees_20160801.dat      2

        }
        =

        GROUP File {
        ============

            Line
            --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
            ,123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567
            1649,LN 3U,EM 3,01-JAN-2010,IT_PROG,30000

        }
        =

        GROUP Batch Job Table {
        =======================

            Name       Fail Percent
            ---------  ------------
            LOAD_EMPS            70

        }
        =

        GROUP Statistics Table {
        ========================

            Job Statistic Id  Batch job Id  File Name               Records Loaded  Records Failed ET  Records Failed DB  Start Time   End Time     Status
            ----------------  ------------  ----------------------  --------------  -----------------  -----------------  -----------  -----------  ------
                          23  LOAD_EMPS     employees_20160801.dat               0                  0                  2  01-JAN-2010  01-JAN-2010  F

        }
        =

        GROUP Employees Table {
        =======================

            Employee Id  Name  Email  Hired        Job      Salary  Manager Id  Department Id  Updated
            -----------  ----  -----  -----------  -------  ------  ----------  -------------  -----------
                   1648  LN 2  EM 2   01-JAN-2010  IT_PROG   20000                             01-JAN-2010
                   1649  LN 3  EM 3   01-JAN-2010  IT_PROG   30000                             01-JAN-2010

        }
        =

    OUTPUTS
    =======

        GROUP Employee: Actual = 2, Expected = 2 {
        ==========================================

            F?  Employee Id  Name   Email  Hired        Job      Salary  Updated
            --  -----------  -----  -----  -----------  -------  ------  -----------
                       1648  LN 2   EM 2   01-JAN-2010  IT_PROG  20000   01-JAN-2010
                       1649  LN 3U  EM 3   01-JAN-2010  IT_PROG  30000   11-SEP-2016

        } 0 failed, of 2: SUCCESS
        =========================

        GROUP Error: Actual = 0, Expected = 0: SUCCESS
        ==============================================

        GROUP Job Statistic: Actual = 2, Expected = 2 {
        ===============================================

            F?  Job Statistic Id  Batch job Id  File Name               Records Loaded  Records Failed ET  Records Failed DB  Start Time   End Time     Status
            --  ----------------  ------------  ----------------------  --------------  -----------------  -----------------  -----------  -----------  ------
                              23  LOAD_EMPS     employees_20160801.dat               0                  0                  2  01-JAN-2010  01-JAN-2010  F
                              24  LOAD_EMPS     employees_20160801.dat               1                  1                  0  11-SEP-2016  11-SEP-2016  S

        } 0 failed, of 2: SUCCESS
        =========================

        GROUP Exception: Actual = 0, Expected = 0: SUCCESS
        ==================================================

} 0 failed, of 6: SUCCESS
=========================

SCENARIO 9: NV/OV/OU/NI/OI/EI: 0/0/0/1/0/0. Existing J/E: 1/1. [File already processed - exception] {
=====================================================================================================

    INPUTS
    ======

        GROUP Parameter {
        =================

            File Name               Count
            ----------------------  -----
            employees_20160801.dat      1

        }
        =

        GROUP File {
        ============

            Line
            ------------------------------------
            ,LN 1,EM 1,01-JAN-2010,NON_JOB,10000

        }
        =

        GROUP Batch Job Table {
        =======================

            Name       Fail Percent
            ---------  ------------
            LOAD_EMPS            70

        }
        =

        GROUP Statistics Table {
        ========================

            Job Statistic Id  Batch job Id  File Name               Records Loaded  Records Failed ET  Records Failed DB  Start Time   End Time     Status
            ----------------  ------------  ----------------------  --------------  -----------------  -----------------  -----------  -----------  ------
                          26  LOAD_EMPS     employees_20160801.dat              10                  0                  2  01-JAN-2010  01-JAN-2010  S

        }
        =

        GROUP Employees Table {
        =======================

            Employee Id  Name  Email  Hired        Job      Salary  Manager Id  Department Id  Updated
            -----------  ----  -----  -----------  -------  ------  ----------  -------------  -----------
                   1651  LN 2  EM 2   01-JAN-2010  IT_PROG   20000                             01-JAN-2010

        }
        =

    OUTPUTS
    =======

        GROUP Employee: Actual = 1, Expected = 1 {
        ==========================================

            F?  Employee Id  Name  Email  Hired        Job      Salary  Updated
            --  -----------  ----  -----  -----------  -------  ------  -----------
                       1651  LN 2  EM 2   01-JAN-2010  IT_PROG  20000   01-JAN-2010

        } 0 failed, of 1: SUCCESS
        =========================

        GROUP Error: Actual = 0, Expected = 0: SUCCESS
        ==============================================

        GROUP Job Statistic: Actual = 1, Expected = 1 {
        ===============================================

            F?  Job Statistic Id  Batch job Id  File Name               Records Loaded  Records Failed ET  Records Failed DB  Start Time   End Time     Status
            --  ----------------  ------------  ----------------------  --------------  -----------------  -----------------  -----------  -----------  ------
                              26  LOAD_EMPS     employees_20160801.dat              10                  0                  2  01-JAN-2010  01-JAN-2010  S

        } 0 failed, of 1: SUCCESS
        =========================

        GROUP Exception: Actual = 1, Expected = 1 {
        ===========================================

            F?  Message
            --  --------------------------------------------------------
                ORA-20002: File has already been processed successfully!

        } 0 failed, of 1: SUCCESS
        =========================

} 0 failed, of 4: SUCCESS
=========================

TIMING: Actual = 306, Expected <= 2: FAILURE
============================================

SUMMARY for UT_Emp_Batch.ut_AIP_Load_Emps
=========================================

Scenario                                                                                                                                                        # Failed  # Tests  Status
--------------------------------------------------------------------------------------------------------------------------------------------------------------  --------  -------  -------
NV/OV/OU/NI/OI/EI: 1/0/0/0/0/0. Existing J/E: 0/0. [1 valid new record from scratch]                                                                                   0        4  SUCCESS
NV/OV/OU/NI/OI/EI: 1/1/1/0/0/0. Existing J/E: 1/0. [3 valid records of each kind]                                                                                      0        7  SUCCESS
NV/OV/OU/NI/OI/EI: 0/0/0/0/1/0. Existing J/E: 1/1. Uid not found [1 invalid old - exception]                                                                           0        5  SUCCESS
NV/OV/OU/NI/OI/EI: 0/0/0/1/0/0. Existing J/E: 1/1. Email too long [1 invalid new - exception]                                                                          0        5  SUCCESS
NV/OV/OU/NI/OI/EI: 1/0/0/0/1/0. Existing J/E: 1/1. Name too long [1 valid new, 1 invalid old - no exception]                                                           0        6  SUCCESS
NV/OV/OU/NI/OI/EI: 0/0/0/1/0/0. Existing J/E: 1/1. Invalid job [1 invalid new - exception]                                                                             0        5  SUCCESS
NV/OV/OU/NI/OI/EI: 0/1/0/1/1/0. Existing J/E: 1/2. 2 invalid jobs [1 valid old, 2 invalid: old and new - no exception]                                                 0        7  SUCCESS
NV/OV/OU/NI/OI/EI: 0/1/0/0/0/1. Existing J/E: 1/2. Name 4001ch [1 valid old, 1 invalid new for external table - no exception; also file had previously failed]         0        6  SUCCESS
NV/OV/OU/NI/OI/EI: 0/0/0/1/0/0. Existing J/E: 1/1. [File already processed - exception]                                                                                0        4  SUCCESS
Timing                                                                                                                                                                 1        1  FAILURE
--------------------------------------------------------------------------------------------------------------------------------------------------------------  --------  -------  -------
Total                                                                                                                                                                  1       50  FAILURE
--------------------------------------------------------------------------------------------------------------------------------------------------------------  --------  -------  -------

Timer Set: UT_Emp_Batch.ut_AIP_Load_Emps, Constructed at 11 Sep 2016 16:10:46, written at 16:10:49
==================================================================================================
[Timer timed: Elapsed (per call): 0.04 (0.000036), CPU (per call): 0.03 (0.000030), calls: 1000, '***' denotes corrected line below]

Timer           Elapsed         CPU         Calls       Ela/Call       CPU/Call
-----------  ----------  ----------  ------------  -------------  -------------
Setup              1.10        0.30             9        0.12189        0.03333
Caller             1.53        1.16             5        0.30560        0.23200
Get_Tab_Lis        0.75        0.49             9        0.08378        0.05444
Get_Err_Lis        0.02        0.03             9        0.00244        0.00333
Get_Jbs_Lis        0.00        0.00             9        0.00011        0.00000
(Other)            0.34        0.35             1        0.33500        0.35000
-----------  ----------  ----------  ------------  -------------  -------------
Total              3.74        2.33            42        0.08898        0.05548
-----------  ----------  ----------  ------------  -------------  -------------

Suite Summary
=============

Package.Procedure                  Tests  Fails         ELA         CPU
---------------------------------  -----  -----  ----------  ----------
UT_Emp_WS.ut_AIP_Save_Emps            17      3        0.10        0.09
UT_Emp_WS.ut_AIP_Get_Dept_Emps         7      1        0.11        0.12
UT_View_Drivers.ut_HR_Test_View_V      9      0        0.12        0.12
UT_Emp_Batch.ut_AIP_Load_Emps         50      1        3.74        2.33
---------------------------------  -----  -----  ----------  ----------
Total                                 83      5        4.07        2.66
---------------------------------  -----  -----  ----------  ----------
Others error in (): ORA-20001: Suite BRENDAN returned error status: ORA-06512: at "DP_1.UT_UTILS", line 151
ORA-06512: at "DP_1.UT_UTILS", line 818
ORA-06512: at line 5






 

A Utility for Reading REF Cursors into a List of Delimited Strings

This is a generic utility function that I wrote for use in unit testing web services that return REF cursors. It takes as input an open REF cursor and an optional filter string and returns the output as an array of delimited strings matching the filter. This can then be used to assert against expected output records.

The initial technical difficulty I had with making this generic was in getting the structure of the cursor. This is resolved by translating the REF cursor into a cursor that can be managed by the package DBMS_SQL, using DBMS_SQL.To_Cursor_Number.

As I am using it for unit testing the function does not need to be scalable. For other uses involving large volumes it could be made scalable by, for example, converting to a pipelined function.

Data Types

The cursor needs to return a flat projection, and columns accounted for currently are:

  • NUMBER
  • VARCHAR2
  • DATE
  • TIMESTAMP

Custom Dependencies

  • L1_chr_arr - custom array type, equivalent to SYS.ODCIVarchar2List
  • Utils.List_Delim - function that turns an array of strings into one delimited string, included in my unit test framework, Brendan's Database Unit Testing Framework

Code

The function wiil (shortly) be packaged within my unit testing package UT_Utils, but for demo purposes, here is a script that has a driving anonymous block with the function declared locally within that block.

DECLARE
  l_csr         SYS_REFCURSOR;
  l_res_lis     L1_chr_arr;
  c_query_1     CONSTANT VARCHAR2(4000) := 'SELECT * FROM employees ORDER BY employee_id';
 
  PROCEDURE Write_Log (p_line VARCHAR2) IS
  BEGIN
    DBMS_Output.Put_Line (p_line);
  END Write_Log;
 
FUNCTION Cursor_to_Array (p_csr IN OUT SYS_REFCURSOR, p_filter VARCHAR2 DEFAULT NULL) RETURN L1_chr_arr IS
 
  c_chr_type    CONSTANT PLS_INTEGER := 1; --DBMS_Types.TYPECODE_* do not seem to quite work
  c_num_type    CONSTANT PLS_INTEGER := 2;
  c_dat_type    CONSTANT PLS_INTEGER := 12;
  c_stp_type    CONSTANT PLS_INTEGER := 180;
  l_csr_id      PLS_INTEGER;
  l_n_cols      PLS_INTEGER;
  l_desctab     DBMS_SQL.DESC_TAB;
  l_chr_val     VARCHAR2(4000);
  l_num_val     NUMBER;
  l_dat_val     DATE;
  l_stp_val     TIMESTAMP;
  l_val_lis     L1_chr_arr;
  l_res_lis     L1_chr_arr := L1_chr_arr();
  l_rec         VARCHAR2(4000);
 
BEGIN
 
  l_csr_id := DBMS_SQL.To_Cursor_Number (p_csr);
  DBMS_SQL.Describe_Columns (l_csr_id, l_n_cols, l_desctab);
 
  FOR i IN 1..l_n_cols LOOP
 
    CASE l_desctab(i).col_type
 
      WHEN c_chr_type THEN
        DBMS_SQL.Define_Column (l_csr_id, i, l_chr_val, 4000);
      WHEN c_num_type THEN
        DBMS_SQL.Define_Column (l_csr_id, i, l_num_val);
      WHEN c_dat_type THEN
        DBMS_SQL.Define_Column (l_csr_id, i, l_dat_val);
      WHEN c_stp_type THEN
         DBMS_SQL.Define_Column (l_csr_id, i, l_stp_val);
     ELSE
        Write_Log ('Col type ' || l_desctab(i).col_type || ' not accounted for!');
 
    END CASE;
 
  END LOOP;
 
  WHILE DBMS_SQL.Fetch_Rows (l_csr_id) > 0 LOOP
 
    l_val_lis := L1_chr_arr();
    l_val_lis.EXTEND (l_n_cols);
    FOR i IN 1 .. l_n_cols LOOP
 
      CASE l_desctab(i).col_type
 
        WHEN c_chr_type THEN
          DBMS_SQL.Column_Value (l_csr_id, i, l_chr_val);
          l_val_lis(i) := l_chr_val;
        WHEN c_num_type THEN
          DBMS_SQL.Column_Value (l_csr_id, i, l_num_val);
          l_val_lis(i) := l_num_val;
        WHEN c_dat_type THEN
          DBMS_SQL.Column_Value (l_csr_id, i, l_dat_val);
          l_val_lis(i) := l_dat_val;
        WHEN c_stp_type THEN
          DBMS_SQL.Column_Value (l_csr_id, i, l_stp_val);
          l_val_lis(i) := l_stp_val;
 
      END CASE;
 
    END LOOP;
 
    l_rec := Utils.List_Delim (l_val_lis);
    IF l_rec LIKE '%' || p_filter || '%' THEN
      l_res_lis.EXTEND;
      l_res_lis (l_res_lis.COUNT) := l_rec;
    END IF;
 
  END LOOP;
 
  DBMS_SQL.Close_Cursor (l_csr_id);
  RETURN l_res_lis;
 
END Cursor_to_Array;
 
BEGIN
 
  OPEN l_csr FOR c_query_1;
 
  l_res_lis := Cursor_to_Array (l_csr, 'SA_REP');
 
  FOR i IN 1..l_res_lis.COUNT LOOP
    Write_Log (i || ': ' || l_res_lis(i));
  END LOOP;
 
END;
/

 
Output for Cursor against HR.employees

1: 150|Peter|Tucker|PTUCKER|011.44.1344.129268|30-JAN-05|SA_REP|10000|.3|145|80|
2: 151|David|Bernstein|DBERNSTE|011.44.1344.345268|24-MAR-05|SA_REP|9500|.25|145|80|
3: 152|Peter|Hall|PHALL|011.44.1344.478968|20-AUG-05|SA_REP|9000|.25|145|80|
4: 153|Christopher|Olsen|COLSEN|011.44.1344.498718|30-MAR-06|SA_REP|8000|.2|145|80|
5: 154|Nanette|Cambrault|NCAMBRAU|011.44.1344.987668|09-DEC-06|SA_REP|7500|.2|145|80|
6: 155|Oliver|Tuvault|OTUVAULT|011.44.1344.486508|23-NOV-07|SA_REP|7000|.15|145|80|
7: 156|Janette|King|JKING|011.44.1345.429268|30-JAN-04|SA_REP|10000|.35|146|80|
8: 157|Patrick|Sully|PSULLY|011.44.1345.929268|04-MAR-04|SA_REP|9500|.35|146|80|
9: 158|Allan|McEwen|AMCEWEN|011.44.1345.829268|01-AUG-04|SA_REP|9000|.35|146|80|
10: 159|Lindsey|Smith|LSMITH|011.44.1345.729268|10-MAR-05|SA_REP|8000|.3|146|80|
11: 160|Louise|Doran|LDORAN|011.44.1345.629268|15-DEC-05|SA_REP|7500|.3|146|80|
12: 161|Sarath|Sewall|SSEWALL|011.44.1345.529268|03-NOV-06|SA_REP|7000|.25|146|80|
13: 162|Clara|Vishney|CVISHNEY|011.44.1346.129268|11-NOV-05|SA_REP|10500|.25|147|80|
14: 163|Danielle|Greene|DGREENE|011.44.1346.229268|19-MAR-07|SA_REP|9500|.15|147|80|
15: 164|Mattea|Marvins|MMARVINS|011.44.1346.329268|24-JAN-08|SA_REP|7200|.1|147|80|
16: 165|David|Lee|DLEE|011.44.1346.529268|23-FEB-08|SA_REP|6800|.1|147|80|
17: 166|Sundar|Ande|SANDE|011.44.1346.629268|24-MAR-08|SA_REP|6400|.1|147|80|
18: 167|Amit|Banda|ABANDA|011.44.1346.729268|21-APR-08|SA_REP|6200|.1|147|80|
19: 168|Lisa|Ozer|LOZER|011.44.1343.929268|11-MAR-05|SA_REP|11500|.25|148|80|
20: 169|Harrison|Bloom|HBLOOM|011.44.1343.829268|23-MAR-06|SA_REP|10000|.2|148|80|
21: 170|Tayler|Fox|TFOX|011.44.1343.729268|24-JAN-06|SA_REP|9600|.2|148|80|
22: 171|William|Smith|WSMITH|011.44.1343.629268|23-FEB-07|SA_REP|7400|.15|148|80|
23: 172|Elizabeth|Bates|EBATES|011.44.1343.529268|24-MAR-07|SA_REP|7300|.15|148|80|
24: 173|Sundita|Kumar|SKUMAR|011.44.1343.329268|21-APR-08|SA_REP|6100|.1|148|80|
25: 174|Ellen|Abel|EABEL|011.44.1644.429267|11-MAY-04|SA_REP|11000|.3|149|80|
26: 175|Alyssa|Hutton|AHUTTON|011.44.1644.429266|19-MAR-05|SA_REP|8800|.25|149|80|
27: 176|Jonathon|Taylor|JTAYLOR|011.44.1644.429265|24-MAR-06|SA_REP|8600|.2|149|80|
28: 177|Jack|Livingston|JLIVINGS|011.44.1644.429264|23-APR-06|SA_REP|8400|.2|149|80|
29: 178|Kimberely|Grant|KGRANT|011.44.1644.429263|24-MAY-07|SA_REP|7000|.15|149||
30: 179|Charles|Johnson|CJOHNSON|011.44.1644.429262|04-JAN-08|SA_REP|6200|.1|149|80|
 
PL/SQL procedure successfully completed.






 

A Note on Dependencies and Database Unit Testing

Ideas on unit testing for the database are often heavily influenced by the world of object oriented programming (OOP), usually Java in practice. This is no doubt because much of modern thinking on development methodologies, including test driven development (TDD), originated in this world. Some of these ideas appear to translate very well into the database world, including that of TDD itself, with automated unit tests. However, some ideas may not translate so well, or even make sense, in database unit testing. For example, Roy Osherove (2011), Unit Test - Definition says:

A good unit test ... runs in memory (no DB or File access, for example)

One concept that appears very important in the OOP world is that of dependencies, and of isolation of the code under test from its dependencies. This gives rise to complex mechanisms of 'mocking' and 'dependency injection' to bring about said isolation. Osherove mentions isolation in the same article as a requirement of good unit testing, and his view appears to be widespread. It's worth mentioning though that not everyone in the OOP world shares his insistence. The influential Martin Fowler (2014) uses a nice terminology of 'sociable' tests (as opposed to 'isolated' tests) for tests that rely on other units to fulfill the behaviour under test, and he uses this approach himself when practicable, UnitTest.

In the case of database unit testing, it seems to me to make very little sense to think in terms of isolating code under test from its dependencies. The following two diagrams represent how I see the relationships between base code, dependencies and unit test code across two distinct phases.

Development Phase
UT Phases-dev

Regression Phase
UT Phases-reg

I would welcome any comments or opinions.






 

TRAPIT - TRansactional API Testing in Oracle

I recently wrote a number of articles on design patterns for database API testing, with code included for testing example procedures against Oracle's HR demo schema. There are now four examples, intended together to represent the whole range of database API testing requirements, as follows:

ModeSetterGetter
Real TimeWeb Service SavingWeb Service Getting by REF Cursor
BatchBatch Loading of Flat FilesViews

The testing code used my own framework utility packages, and originally called procedures from the well-known utPLSQL framework. However, the use of utPLSQL was always minimal, and I later removed it to form my own lightweight independent framework that is designed to help avoid many API testing anti-patterns, and provide better output formatting than utPLSQL.

Update, 9 July 2016: I have added a listing of all inputs at scenario level (see box below). This means that the API test outputs now become precise and accurate documents of what the program does. All the output is printed by a central library procedure so that work by individual test programs is minimised.
Update, 24 September, 1 October 2016: I slightly renamed the articles below and added the third example for batch file loading, and the fourth for REF cursors.
Update, 22 October 2016: Name change to distinguish from OO-style unit testing: TRAPIT - TRansactional API Testing in Oracle: UT->TT etc.

The code is available here: BrenPatF/db_unit_test

See also the following articles. The output from my demo suite is included below.

Design Patterns for Database API Testing 1: Web Service Saving 1 - Design
Design Patterns for Database API Testing 1: Web Service Saving 2 - Code
Design Patterns for Database API Testing 2: Views 1 - Design
Design Patterns for Database API Testing 2: Views 2 - Code
Design Patterns for Database API Testing 3: Batch Loading of Flat Files
Design Patterns for Database API Testing 4: REF Cursor Getter

SQL> DECLARE
  2  BEGIN
  3  
  4    Utils.Clear_Log;
  5    Utils_TT.Run_Suite (Utils_TT.c_tt_suite_bren);
  6  
  7  EXCEPTION
  8    WHEN OTHERS THEN
  9      Utils.Write_Other_Error;
 10  END;
 11  /

PL/SQL procedure successfully completed.

SQL> @L_Log_Default

TEXT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

TRAPIT TEST: TT_Emp_WS.tt_AIP_Save_Emps
=======================================

SCENARIO 1: 1 valid record {
============================

    INPUTS
    ======

        GROUP Employee {
        ================

            Name  Email  Job      Salary
            ----  -----  -------  ------
            LN 1  EM 1   IT_PROG    1000

        }
        =

    OUTPUTS
    =======

        GROUP Employee: Actual = 1, Expected = 1 {
        ==========================================

            F?  Employee id  Name  Email  Job      Salary
            --  -----------  ----  -----  -------  ------
                       1927  LN 1  EM 1   IT_PROG    1000

        } 0 failed, of 1: SUCCESS
        =========================

        GROUP Output array: Actual = 1, Expected = 1 {
        ==============================================

            F?  Employee id  Description
            --  -----------  --------------------------------------
                       1927  ONE THOUSAND NINE HUNDRED TWENTY-SEVEN

        } 0 failed, of 1: SUCCESS
        =========================

        GROUP Exception: Actual = 0, Expected = 0: SUCCESS
        ==================================================

} 0 failed, of 3: SUCCESS
=========================

SCENARIO 2: 1 invalid job id {
==============================

    INPUTS
    ======

        GROUP Employee {
        ================

            Name  Email  Job      Salary
            ----  -----  -------  ------
            LN 2  EM 2   NON_JOB    1500

        }
        =

    OUTPUTS
    =======

        GROUP Employee: Actual = 0, Expected = 0: SUCCESS
        =================================================

        GROUP Output array: Actual = 1, Expected = 1 {
        ==============================================

            F?  Employee id  Description
            --  -----------  -------------------------------------------------------------------
                          0  ORA-02291: integrity constraint (.) violated - parent key not found

        } 0 failed, of 1: SUCCESS
        =========================

        GROUP Exception: Actual = 0, Expected = 0: SUCCESS
        ==================================================

} 0 failed, of 3: SUCCESS
=========================

SCENARIO 3: 1 invalid number {
==============================

    INPUTS
    ======

        GROUP Employee {
        ================

            Name  Email  Job      Salary
            ----  -----  -------  ------
            LN 3  EM 3   IT_PROG   2000x

        }
        =

    OUTPUTS
    =======

        GROUP Employee: Actual = 0, Expected = 0: SUCCESS
        =================================================

        GROUP Output array: Actual = 0, Expected = 0: SUCCESS
        =====================================================

        GROUP Exception: Actual = 1, Expected = 1 {
        ===========================================

            F?  Error message
            --  -------------------------------------------------------------------------------
                ORA-06502: PL/SQL: numeric or value error: character to number conversion error

        } 0 failed, of 1: SUCCESS
        =========================

} 0 failed, of 3: SUCCESS
=========================

SCENARIO 4: 2 valid records, 1 invalid job id (2 deliberate errors) {
=====================================================================

    INPUTS
    ======

        GROUP Employee {
        ================

            Name  Email  Job      Salary
            ----  -----  -------  ------
            LN 4  EM 4   IT_PROG    3000
            LN 5  EM 5   NON_JOB    4000
            LN 6  EM 6   IT_PROG    5000

        }
        =

    OUTPUTS
    =======

        GROUP Employee: Actual = 2, Expected = 3 {
        ==========================================

            F?  Employee id  Name  Email  Job      Salary
            --  -----------  ----  -----  -------  ------
            F          1929  LN 4  EM 4   IT_PROG    3000
            >          1929  LN 4  EM 4   IT_PROG    1000
                       1931  LN 6  EM 6   IT_PROG    5000
            F
            >          1931  LN 6  EM 6   IT_PROG    5000

        } 2 failed, of 3: FAILURE
        =========================

        GROUP Output array: Actual = 3, Expected = 3 {
        ==============================================

            F?  Employee id  Description
            --  -----------  -------------------------------------------------------------------
                       1929  ONE THOUSAND NINE HUNDRED TWENTY-NINE
                          0  ORA-02291: integrity constraint (.) violated - parent key not found
                       1931  ONE THOUSAND NINE HUNDRED THIRTY-ONE

        } 0 failed, of 3: SUCCESS
        =========================

        GROUP Exception: Actual = 0, Expected = 0: SUCCESS
        ==================================================

} 2 failed, of 7: FAILURE
=========================

TIMING: Actual = 1, Expected <= 2: SUCCESS
==========================================

SUMMARY for TT_Emp_WS.tt_AIP_Save_Emps
======================================

Scenario                                                 # Failed  # Tests  Status
-------------------------------------------------------  --------  -------  -------
1 valid record                                                  0        3  SUCCESS
1 invalid job id                                                0        3  SUCCESS
1 invalid number                                                0        3  SUCCESS
2 valid records, 1 invalid job id (2 deliberate errors)         2        7  FAILURE
Timing                                                          0        1  SUCCESS
-------------------------------------------------------  --------  -------  -------
Total                                                           2       17  FAILURE
-------------------------------------------------------  --------  -------  -------

Timer Set: TT_Emp_WS.tt_AIP_Save_Emps, Constructed at 22 Oct 2016 15:05:16, written at 15:05:16
===============================================================================================
[Timer timed: Elapsed (per call): 0.03 (0.000034), CPU (per call): 0.05 (0.000050), calls: 1000, '***' denotes corrected line below]

Timer       Elapsed         CPU         Calls       Ela/Call       CPU/Call
-------  ----------  ----------  ------------  -------------  -------------
Setup          0.00        0.00             1        0.00300        0.00000
Caller         0.00        0.02             3        0.00133        0.00667
SELECT         0.00        0.00             3        0.00033        0.00000
(Other)        0.08        0.06             1        0.07700        0.06000
-------  ----------  ----------  ------------  -------------  -------------
Total          0.09        0.08             8        0.01063        0.01000
-------  ----------  ----------  ------------  -------------  -------------

TRAPIT TEST: TT_Emp_WS.tt_AIP_Get_Dept_Emps
===========================================

Employees created in setup: DS-1 - 4 emps, 1 dep (10), emp-3 has no dep, emp-4 has bad job
==========================================================================================

#  Employee id  Department id     Manager  Job id          Salary
-  -----------  -------------  ----------  ----------  ----------
1         1932             10              IT_PROG           1000
2         1933             10        1932  IT_PROG           2000
3         1934                       1932  IT_PROG           3000
4         1935             10        1932  AD_ASST           4000

Employees created in setup: DS-2 - As dataset 1 but with extra emp-5, in second dep (20)
========================================================================================

#  Employee id  Department id     Manager  Job id          Salary
-  -----------  -------------  ----------  ----------  ----------
1         1936             10              IT_PROG           1000
2         1937             10        1936  IT_PROG           2000
3         1938                       1936  IT_PROG           3000
4         1939             10        1936  AD_ASST           4000
5         1940             20        1936  IT_PROG           5000

Employees created in setup: DS-2 - As dataset 1 but with extra emp-5, in second dep (20)
========================================================================================

#  Employee id  Department id     Manager  Job id          Salary
-  -----------  -------------  ----------  ----------  ----------
1         1941             10              IT_PROG           1000
2         1942             10        1941  IT_PROG           2000
3         1943                       1941  IT_PROG           3000
4         1944             10        1941  AD_ASST           4000
5         1945             20        1941  IT_PROG           5000

Employees created in setup: DS-2 - As dataset 1 but with extra emp-5, in second dep (20)
========================================================================================

#  Employee id  Department id     Manager  Job id          Salary
-  -----------  -------------  ----------  ----------  ----------
1         1946             10              IT_PROG           1000
2         1947             10        1946  IT_PROG           2000
3         1948                       1946  IT_PROG           3000
4         1949             10        1946  AD_ASST           4000
5         1950             20        1946  IT_PROG           5000

Employees created in setup: DS-3 - As dataset 2 but with salaries * 0.1, total below reporting threshold of 1600
================================================================================================================

#  Employee id  Department id     Manager  Job id          Salary
-  -----------  -------------  ----------  ----------  ----------
1         1951             10              IT_PROG            100
2         1952             10        1951  IT_PROG            200
3         1953                       1951  IT_PROG            300
4         1954             10        1951  AD_ASST            400
5         1955             20        1951  IT_PROG            500

SCENARIO 1: DS-1, testing inner, outer joins, analytic over dep, and global ratios with 1 dep (10) - pass dep 10 {
==================================================================================================================

    INPUTS
    ======

        GROUP Employee {
        ================

            Employee Id  Last Name  Email  Hire Date    Job      Salary  Manager Id  Department Id  Updated
            -----------  ---------  -----  -----------  -------  ------  ----------  -------------  -----------
                   1932  LN_1       EM_1   22-OCT-2016  IT_PROG    1000                         10  22-OCT-2016
                   1933  LN_2       EM_2   22-OCT-2016  IT_PROG    2000        1932             10  22-OCT-2016
                   1934  LN_3       EM_3   22-OCT-2016  IT_PROG    3000        1932                 22-OCT-2016
                   1935  LN_4       EM_4   22-OCT-2016  AD_ASST    4000        1932             10  22-OCT-2016

        }
        =

        GROUP Department Parameter {
        ============================

            Department Id
            -------------
                       10

        }
        =

    OUTPUTS
    =======

        GROUP Select results: Actual = 2, Expected = 2 {
        ================================================

            F?  Name  Department      Manager  Salary  Salary Ratio (dep)  Salary Ratio (overall)
            --  ----  --------------  -------  ------  ------------------  ----------------------
                LN_1  Administration             1000                 .67                      .4
                LN_2  Administration  LN_1       2000                1.33                      .8

        } 0 failed, of 2: SUCCESS
        =========================

} 0 failed, of 2: SUCCESS
=========================

SCENARIO 2: DS-2, testing same as 1 but with extra emp in another dep (20) - pass dep 10 {
==========================================================================================

    INPUTS
    ======

        GROUP Employee {
        ================

            Employee Id  Last Name  Email  Hire Date    Job      Salary  Manager Id  Department Id  Updated
            -----------  ---------  -----  -----------  -------  ------  ----------  -------------  -----------
                   1936  LN_1       EM_1   22-OCT-2016  IT_PROG    1000                         10  22-OCT-2016
                   1937  LN_2       EM_2   22-OCT-2016  IT_PROG    2000        1936             10  22-OCT-2016
                   1938  LN_3       EM_3   22-OCT-2016  IT_PROG    3000        1936                 22-OCT-2016
                   1939  LN_4       EM_4   22-OCT-2016  AD_ASST    4000        1936             10  22-OCT-2016
                   1940  LN_5       EM_5   22-OCT-2016  IT_PROG    5000        1936             20  22-OCT-2016

        }
        =

        GROUP Department Parameter {
        ============================

            Department Id
            -------------
                       10

        }
        =

    OUTPUTS
    =======

        GROUP Select results: Actual = 2, Expected = 2 {
        ================================================

            F?  Name  Department      Manager  Salary  Salary Ratio (dep)  Salary Ratio (overall)
            --  ----  --------------  -------  ------  ------------------  ----------------------
                LN_1  Administration             1000                 .67                     .33
                LN_2  Administration  LN_1       2000                1.33                     .67

        } 0 failed, of 2: SUCCESS
        =========================

} 0 failed, of 2: SUCCESS
=========================

SCENARIO 3: DS-2, as second scenario, but - pass dep 20 {
=========================================================

    INPUTS
    ======

        GROUP Employee {
        ================

            Employee Id  Last Name  Email  Hire Date    Job      Salary  Manager Id  Department Id  Updated
            -----------  ---------  -----  -----------  -------  ------  ----------  -------------  -----------
                   1941  LN_1       EM_1   22-OCT-2016  IT_PROG    1000                         10  22-OCT-2016
                   1942  LN_2       EM_2   22-OCT-2016  IT_PROG    2000        1941             10  22-OCT-2016
                   1943  LN_3       EM_3   22-OCT-2016  IT_PROG    3000        1941                 22-OCT-2016
                   1944  LN_4       EM_4   22-OCT-2016  AD_ASST    4000        1941             10  22-OCT-2016
                   1945  LN_5       EM_5   22-OCT-2016  IT_PROG    5000        1941             20  22-OCT-2016

        }
        =

        GROUP Department Parameter {
        ============================

            Department Id
            -------------
                       20

        }
        =

    OUTPUTS
    =======

        GROUP Select results: Actual = 1, Expected = 1 {
        ================================================

            F?  Name  Department  Manager  Salary  Salary Ratio (dep)  Salary Ratio (overall)
            --  ----  ----------  -------  ------  ------------------  ----------------------
                LN_5  Marketing   LN_1       5000                   1                    1.67

        } 0 failed, of 1: SUCCESS
        =========================

} 0 failed, of 1: SUCCESS
=========================

SCENARIO 4: DS-2, as second scenario, but - pass null dep {
===========================================================

    INPUTS
    ======

        GROUP Employee {
        ================

            Employee Id  Last Name  Email  Hire Date    Job      Salary  Manager Id  Department Id  Updated
            -----------  ---------  -----  -----------  -------  ------  ----------  -------------  -----------
                   1946  LN_1       EM_1   22-OCT-2016  IT_PROG    1000                         10  22-OCT-2016
                   1947  LN_2       EM_2   22-OCT-2016  IT_PROG    2000        1946             10  22-OCT-2016
                   1948  LN_3       EM_3   22-OCT-2016  IT_PROG    3000        1946                 22-OCT-2016
                   1949  LN_4       EM_4   22-OCT-2016  AD_ASST    4000        1946             10  22-OCT-2016
                   1950  LN_5       EM_5   22-OCT-2016  IT_PROG    5000        1946             20  22-OCT-2016

        }
        =

        GROUP Department Parameter {
        ============================

            Department Id
            -------------


        }
        =

    OUTPUTS
    =======

        GROUP Select results: Actual = 0, Expected = 0: SUCCESS
        =======================================================

} 0 failed, of 1: SUCCESS
=========================

SCENARIO 5: DS-3, Salaries total 1500 (< threshold of 1600, so return nothing) - pass dep 10 {
==============================================================================================

    INPUTS
    ======

        GROUP Employee {
        ================

            Employee Id  Last Name  Email  Hire Date    Job      Salary  Manager Id  Department Id  Updated
            -----------  ---------  -----  -----------  -------  ------  ----------  -------------  -----------
                   1951  LN_1       EM_1   22-OCT-2016  IT_PROG     100                         10  22-OCT-2016
                   1952  LN_2       EM_2   22-OCT-2016  IT_PROG     200        1951             10  22-OCT-2016
                   1953  LN_3       EM_3   22-OCT-2016  IT_PROG     300        1951                 22-OCT-2016
                   1954  LN_4       EM_4   22-OCT-2016  AD_ASST     400        1951             10  22-OCT-2016
                   1955  LN_5       EM_5   22-OCT-2016  IT_PROG     500        1951             20  22-OCT-2016

        }
        =

        GROUP Department Parameter {
        ============================

            Department Id
            -------------
                       10

        }
        =

    OUTPUTS
    =======

        GROUP Select results: Actual = 0, Expected = 0: SUCCESS
        =======================================================

} 0 failed, of 1: SUCCESS
=========================

TIMING: Actual = 1, Expected <= 1: SUCCESS
==========================================

SUMMARY for TT_Emp_WS.tt_AIP_Get_Dept_Emps
==========================================

Scenario                                                                                              # Failed  # Tests  Status
----------------------------------------------------------------------------------------------------  --------  -------  -------
DS-1, testing inner, outer joins, analytic over dep, and global ratios with 1 dep (10) - pass dep 10         0        2  SUCCESS
DS-2, testing same as 1 but with extra emp in another dep (20) - pass dep 10                                 0        2  SUCCESS
DS-2, as second scenario, but - pass dep 20                                                                  0        1  SUCCESS
DS-2, as second scenario, but - pass null dep                                                                0        1  SUCCESS
DS-3, Salaries total 1500 (< threshold of 1600, so return nothing) - pass dep 10                             0        1  SUCCESS
Timing                                                                                                       0        1  SUCCESS
----------------------------------------------------------------------------------------------------  --------  -------  -------
Total                                                                                                        0        8  SUCCESS
----------------------------------------------------------------------------------------------------  --------  -------  -------

Timer Set: TT_Emp_WS.tt_AIP_Get_Dept_Emps, Constructed at 22 Oct 2016 15:05:16, written at 15:05:16
===================================================================================================
[Timer timed: Elapsed (per call): 0.04 (0.000035), CPU (per call): 0.03 (0.000030), calls: 1000, '***' denotes corrected line below]

Timer       Elapsed         CPU         Calls       Ela/Call       CPU/Call
-------  ----------  ----------  ------------  -------------  -------------
Setup          0.04        0.02             5        0.00840        0.00400
Caller         0.01        0.02             5        0.00140        0.00400
(Other)        0.09        0.10             1        0.09200        0.10000
-------  ----------  ----------  ------------  -------------  -------------
Total          0.14        0.14            11        0.01282        0.01273
-------  ----------  ----------  ------------  -------------  -------------

TRAPIT TEST: TT_View_Drivers.tt_HR_Test_View_V
==============================================

Employees created in setup: DS-1 - 4 emps, 1 dep (10), emp-3 has no dep, emp-4 has bad job
==========================================================================================

#  Employee id  Department id     Manager  Job id          Salary
-  -----------  -------------  ----------  ----------  ----------
1         1956             10              IT_PROG           1000
2         1957             10        1956  IT_PROG           2000
3         1958                       1956  IT_PROG           3000
4         1959             10        1956  AD_ASST           4000

Employees created in setup: DS-2 - As dataset 1 but with extra emp-5, in second dep (20)
========================================================================================

#  Employee id  Department id     Manager  Job id          Salary
-  -----------  -------------  ----------  ----------  ----------
1         1960             10              IT_PROG           1000
2         1961             10        1960  IT_PROG           2000
3         1962                       1960  IT_PROG           3000
4         1963             10        1960  AD_ASST           4000
5         1964             20        1960  IT_PROG           5000

Employees created in setup: DS-2 - As dataset 1 but with extra emp-5, in second dep (20)
========================================================================================

#  Employee id  Department id     Manager  Job id          Salary
-  -----------  -------------  ----------  ----------  ----------
1         1965             10              IT_PROG           1000
2         1966             10        1965  IT_PROG           2000
3         1967                       1965  IT_PROG           3000
4         1968             10        1965  AD_ASST           4000
5         1969             20        1965  IT_PROG           5000

Employees created in setup: DS-3 - As dataset 2 but with salaries * 0.1, total below reporting threshold of 1600
================================================================================================================

#  Employee id  Department id     Manager  Job id          Salary
-  -----------  -------------  ----------  ----------  ----------
1         1970             10              IT_PROG            100
2         1971             10        1970  IT_PROG            200
3         1972                       1970  IT_PROG            300
4         1973             10        1970  AD_ASST            400
5         1974             20        1970  IT_PROG            500

SCENARIO 1: DS-1, testing inner, outer joins, analytic over dep, and global ratios with 1 dep {
===============================================================================================

    INPUTS
    ======

        GROUP Employee {
        ================

            Employee Id  Last Name  Email  Hire Date    Job      Salary  Manager Id  Department Id  Updated
            -----------  ---------  -----  -----------  -------  ------  ----------  -------------  -----------
                   1956  LN_1       EM_1   22-OCT-2016  IT_PROG    1000                         10  22-OCT-2016
                   1957  LN_2       EM_2   22-OCT-2016  IT_PROG    2000        1956             10  22-OCT-2016
                   1958  LN_3       EM_3   22-OCT-2016  IT_PROG    3000        1956                 22-OCT-2016
                   1959  LN_4       EM_4   22-OCT-2016  AD_ASST    4000        1956             10  22-OCT-2016

        }
        =

        GROUP Where {
        =============

            Where
            -----


        }
        =

    OUTPUTS
    =======

        GROUP Select results: Actual = 2, Expected = 2 {
        ================================================

            F?  Name  Department      Manager  Salary  Salary Ratio (dep)  Salary Ratio (overall)
            --  ----  --------------  -------  ------  ------------------  ----------------------
                LN_1  Administration             1000                 .67                      .4
                LN_2  Administration  LN_1       2000                1.33                      .8

        } 0 failed, of 2: SUCCESS
        =========================

} 0 failed, of 2: SUCCESS
=========================

SCENARIO 2: DS-2, testing same as 1 but with extra emp in another dep {
=======================================================================

    INPUTS
    ======

        GROUP Employee {
        ================

            Employee Id  Last Name  Email  Hire Date    Job      Salary  Manager Id  Department Id  Updated
            -----------  ---------  -----  -----------  -------  ------  ----------  -------------  -----------
                   1960  LN_1       EM_1   22-OCT-2016  IT_PROG    1000                         10  22-OCT-2016
                   1961  LN_2       EM_2   22-OCT-2016  IT_PROG    2000        1960             10  22-OCT-2016
                   1962  LN_3       EM_3   22-OCT-2016  IT_PROG    3000        1960                 22-OCT-2016
                   1963  LN_4       EM_4   22-OCT-2016  AD_ASST    4000        1960             10  22-OCT-2016
                   1964  LN_5       EM_5   22-OCT-2016  IT_PROG    5000        1960             20  22-OCT-2016

        }
        =

        GROUP Where {
        =============

            Where
            -----


        }
        =

    OUTPUTS
    =======

        GROUP Select results: Actual = 3, Expected = 3 {
        ================================================

            F?  Name  Department      Manager  Salary  Salary Ratio (dep)  Salary Ratio (overall)
            --  ----  --------------  -------  ------  ------------------  ----------------------
                LN_1  Administration             1000                 .67                     .33
                LN_2  Administration  LN_1       2000                1.33                     .67
                LN_5  Marketing       LN_1       5000                   1                    1.67

        } 0 failed, of 3: SUCCESS
        =========================

} 0 failed, of 3: SUCCESS
=========================

SCENARIO 3: DS-2, passing 'WHERE dep=10' {
==========================================

    INPUTS
    ======

        GROUP Employee {
        ================

            Employee Id  Last Name  Email  Hire Date    Job      Salary  Manager Id  Department Id  Updated
            -----------  ---------  -----  -----------  -------  ------  ----------  -------------  -----------
                   1965  LN_1       EM_1   22-OCT-2016  IT_PROG    1000                         10  22-OCT-2016
                   1966  LN_2       EM_2   22-OCT-2016  IT_PROG    2000        1965             10  22-OCT-2016
                   1967  LN_3       EM_3   22-OCT-2016  IT_PROG    3000        1965                 22-OCT-2016
                   1968  LN_4       EM_4   22-OCT-2016  AD_ASST    4000        1965             10  22-OCT-2016
                   1969  LN_5       EM_5   22-OCT-2016  IT_PROG    5000        1965             20  22-OCT-2016

        }
        =

        GROUP Where {
        =============

            Where
            --------------------------------
            department_name='Administration'

        }
        =

    OUTPUTS
    =======

        GROUP Select results: Actual = 2, Expected = 2 {
        ================================================

            F?  Name  Department      Manager  Salary  Salary Ratio (dep)  Salary Ratio (overall)
            --  ----  --------------  -------  ------  ------------------  ----------------------
                LN_1  Administration             1000                 .67                     .33
                LN_2  Administration  LN_1       2000                1.33                     .67

        } 0 failed, of 2: SUCCESS
        =========================

} 0 failed, of 2: SUCCESS
=========================

SCENARIO 4: DS-3, Salaries total 1500 (< threshold of 1600, so return nothing) {
================================================================================

    INPUTS
    ======

        GROUP Employee {
        ================

            Employee Id  Last Name  Email  Hire Date    Job      Salary  Manager Id  Department Id  Updated
            -----------  ---------  -----  -----------  -------  ------  ----------  -------------  -----------
                   1970  LN_1       EM_1   22-OCT-2016  IT_PROG     100                         10  22-OCT-2016
                   1971  LN_2       EM_2   22-OCT-2016  IT_PROG     200        1970             10  22-OCT-2016
                   1972  LN_3       EM_3   22-OCT-2016  IT_PROG     300        1970                 22-OCT-2016
                   1973  LN_4       EM_4   22-OCT-2016  AD_ASST     400        1970             10  22-OCT-2016
                   1974  LN_5       EM_5   22-OCT-2016  IT_PROG     500        1970             20  22-OCT-2016

        }
        =

        GROUP Where {
        =============

            Where
            -----


        }
        =

    OUTPUTS
    =======

        GROUP Select results: Actual = 0, Expected = 0: SUCCESS
        =======================================================

} 0 failed, of 1: SUCCESS
=========================

TIMING: Actual = 1, Expected <= 1: SUCCESS
==========================================

SUMMARY for TT_View_Drivers.tt_HR_Test_View_V
=============================================

Scenario                                                                           # Failed  # Tests  Status
---------------------------------------------------------------------------------  --------  -------  -------
DS-1, testing inner, outer joins, analytic over dep, and global ratios with 1 dep         0        2  SUCCESS
DS-2, testing same as 1 but with extra emp in another dep                                 0        3  SUCCESS
DS-2, passing 'WHERE dep=10'                                                              0        2  SUCCESS
DS-3, Salaries total 1500 (< threshold of 1600, so return nothing)                        0        1  SUCCESS
Timing                                                                                    0        1  SUCCESS
---------------------------------------------------------------------------------  --------  -------  -------
Total                                                                                     0        9  SUCCESS
---------------------------------------------------------------------------------  --------  -------  -------

Timer Set: TT_View_Drivers.tt_HR_Test_View_V, Constructed at 22 Oct 2016 15:05:16, written at 15:05:16
======================================================================================================
[Timer timed: Elapsed (per call): 0.03 (0.000033), CPU (per call): 0.03 (0.000030), calls: 1000, '***' denotes corrected line below]

Timer       Elapsed         CPU         Calls       Ela/Call       CPU/Call
-------  ----------  ----------  ------------  -------------  -------------
Setup          0.03        0.03             4        0.00825        0.00750
Caller         0.01        0.00             4        0.00125        0.00000
(Other)        0.07        0.08             1        0.07000        0.08000
-------  ----------  ----------  ------------  -------------  -------------
Total          0.11        0.11             9        0.01200        0.01222
-------  ----------  ----------  ------------  -------------  -------------

TRAPIT TEST: TT_Emp_Batch.tt_AIP_Load_Emps
==========================================

SCENARIO 1: NV/OV/OU/NI/OI/EI: 1/0/0/0/0/0. Existing J/E: 0/0. [1 valid new record from scratch] {
==================================================================================================

    INPUTS
    ======

        GROUP Parameter {
        =================

            File Name               Count
            ----------------------  -----
            employees_20160801.dat      1

        }
        =

        GROUP File {
        ============

            Line
            ------------------------------------
            ,LN 1,EM 1,01-JAN-2010,IT_PROG,10000

        }
        =

        GROUP Batch Job Table {
        =======================

            Name       Fail Percent
            ---------  ------------
            LOAD_EMPS            70

        }
        =

        GROUP Statistics Table (No records)
        ===================================

        GROUP Employees Table (No records)
        ==================================

    OUTPUTS
    =======

        GROUP Employee: Actual = 1, Expected = 1 {
        ==========================================

            F?  Employee Id  Name  Email  Hired        Job      Salary  Updated
            --  -----------  ----  -----  -----------  -------  ------  -----------
                       1976  LN 1  EM 1   01-JAN-2010  IT_PROG  10000   22-OCT-2016

        } 0 failed, of 1: SUCCESS
        =========================

        GROUP Error: Actual = 0, Expected = 0: SUCCESS
        ==============================================

        GROUP Job Statistic: Actual = 1, Expected = 1 {
        ===============================================

            F?  Job Statistic Id  Batch job Id  File Name               Records Loaded  Records Failed ET  Records Failed DB  Start Time   End Time     Status
            --  ----------------  ------------  ----------------------  --------------  -----------------  -----------------  -----------  -----------  ------
                              52  LOAD_EMPS     employees_20160801.dat               1                  0                  0  22-OCT-2016  22-OCT-2016  S

        } 0 failed, of 1: SUCCESS
        =========================

        GROUP Exception: Actual = 0, Expected = 0: SUCCESS
        ==================================================

} 0 failed, of 4: SUCCESS
=========================

SCENARIO 2: NV/OV/OU/NI/OI/EI: 1/1/1/0/0/0. Existing J/E: 1/0. [3 valid records of each kind] {
===============================================================================================

    INPUTS
    ======

        GROUP Parameter {
        =================

            File Name               Count
            ----------------------  -----
            employees_20160801.dat      3

        }
        =

        GROUP File {
        ============

            Line
            -----------------------------------------
            ,LN 1,EM 1,01-JAN-2010,IT_PROG,10000
            1978,LN 2,EM 2,01-JAN-2010,IT_PROG,20000
            1979,LN 3U,EM 3,01-JAN-2010,IT_PROG,30000

        }
        =

        GROUP Batch Job Table {
        =======================

            Name       Fail Percent
            ---------  ------------
            LOAD_EMPS            70

        }
        =

        GROUP Statistics Table {
        ========================

            Job Statistic Id  Batch job Id  File Name               Records Loaded  Records Failed ET  Records Failed DB  Start Time   End Time     Status
            ----------------  ------------  ----------------------  --------------  -----------------  -----------------  -----------  -----------  ------
                          54  LOAD_EMPS     employees_20160101.dat              10                  0                  2  01-JAN-2010  01-JAN-2010  S

        }
        =

        GROUP Employees Table {
        =======================

            Employee Id  Name  Email  Hired        Job      Salary  Manager Id  Department Id  Updated
            -----------  ----  -----  -----------  -------  ------  ----------  -------------  -----------
                   1978  LN 2  EM 2   01-JAN-2010  IT_PROG   20000                             01-JAN-2010
                   1979  LN 3  EM 3   01-JAN-2010  IT_PROG   30000                             01-JAN-2010

        }
        =

    OUTPUTS
    =======

        GROUP Employee: Actual = 3, Expected = 3 {
        ==========================================

            F?  Employee Id  Name   Email  Hired        Job      Salary  Updated
            --  -----------  -----  -----  -----------  -------  ------  -----------
                       1978  LN 2   EM 2   01-JAN-2010  IT_PROG  20000   01-JAN-2010
                       1979  LN 3U  EM 3   01-JAN-2010  IT_PROG  30000   22-OCT-2016
                       1980  LN 1   EM 1   01-JAN-2010  IT_PROG  10000   22-OCT-2016

        } 0 failed, of 3: SUCCESS
        =========================

        GROUP Error: Actual = 0, Expected = 0: SUCCESS
        ==============================================

        GROUP Job Statistic: Actual = 2, Expected = 2 {
        ===============================================

            F?  Job Statistic Id  Batch job Id  File Name               Records Loaded  Records Failed ET  Records Failed DB  Start Time   End Time     Status
            --  ----------------  ------------  ----------------------  --------------  -----------------  -----------------  -----------  -----------  ------
                              54  LOAD_EMPS     employees_20160101.dat              10                  0                  2  01-JAN-2010  01-JAN-2010  S
                              55  LOAD_EMPS     employees_20160801.dat               2                  0                  0  22-OCT-2016  22-OCT-2016  S

        } 0 failed, of 2: SUCCESS
        =========================

        GROUP Exception: Actual = 0, Expected = 0: SUCCESS
        ==================================================

} 0 failed, of 7: SUCCESS
=========================

SCENARIO 3: NV/OV/OU/NI/OI/EI: 0/0/0/0/1/0. Existing J/E: 1/1. Uid not found [1 invalid old - exception] {
==========================================================================================================

    INPUTS
    ======

        GROUP Parameter {
        =================

            File Name               Count
            ----------------------  -----
            employees_20160801.dat      1

        }
        =

        GROUP File {
        ============

            Line
            --------------------------------------
            99,LN 1,EM 1,01-JAN-2010,IT_PROG,10000

        }
        =

        GROUP Batch Job Table {
        =======================

            Name       Fail Percent
            ---------  ------------
            LOAD_EMPS            70

        }
        =

        GROUP Statistics Table {
        ========================

            Job Statistic Id  Batch job Id  File Name               Records Loaded  Records Failed ET  Records Failed DB  Start Time   End Time     Status
            ----------------  ------------  ----------------------  --------------  -----------------  -----------------  -----------  -----------  ------
                          57  LOAD_EMPS     employees_20160101.dat              10                  0                  2  01-JAN-2010  01-JAN-2010  S

        }
        =

        GROUP Employees Table {
        =======================

            Employee Id  Name  Email  Hired        Job      Salary  Manager Id  Department Id  Updated
            -----------  ----  -----  -----------  -------  ------  ----------  -------------  -----------
                   1982  LN 2  EM 2   01-JAN-2010  IT_PROG   20000                             01-JAN-2010

        }
        =

    OUTPUTS
    =======

        GROUP Employee: Actual = 1, Expected = 1 {
        ==========================================

            F?  Employee Id  Name  Email  Hired        Job      Salary  Updated
            --  -----------  ----  -----  -----------  -------  ------  -----------
                       1982  LN 2  EM 2   01-JAN-2010  IT_PROG  20000   01-JAN-2010

        } 0 failed, of 1: SUCCESS
        =========================

        GROUP Error: Actual = 1, Expected = 1 {
        =======================================

            F?  Job Statistic Id  ORA_ERR_TAG$  ORA_ERR_MESG$       ORA_ERR_OPTYP$  Employee Id  Name  Email  Hired        Job      Salary
            --  ----------------  ------------  ------------------  --------------  -----------  ----  -----  -----------  -------  ------
                              58                Employee not found  PK                       99  LN 1  EM 1   01-JAN-2010  IT_PROG  10000

        } 0 failed, of 1: SUCCESS
        =========================

        GROUP Job Statistic: Actual = 2, Expected = 2 {
        ===============================================

            F?  Job Statistic Id  Batch job Id  File Name               Records Loaded  Records Failed ET  Records Failed DB  Start Time   End Time     Status
            --  ----------------  ------------  ----------------------  --------------  -----------------  -----------------  -----------  -----------  ------
                              57  LOAD_EMPS     employees_20160101.dat              10                  0                  2  01-JAN-2010  01-JAN-2010  S
                              58  LOAD_EMPS     employees_20160801.dat               0                  0                  1  22-OCT-2016  22-OCT-2016  F

        } 0 failed, of 2: SUCCESS
        =========================

        GROUP Exception: Actual = 1, Expected = 1 {
        ===========================================

            F?  Message
            --  ------------------------------------------------------
                ORA-20001: Batch failed with too many invalid records!

        } 0 failed, of 1: SUCCESS
        =========================

} 0 failed, of 5: SUCCESS
=========================

SCENARIO 4: NV/OV/OU/NI/OI/EI: 0/0/0/1/0/0. Existing J/E: 1/1. Email too long [1 invalid new - exception] {
===========================================================================================================

    INPUTS
    ======

        GROUP Parameter {
        =================

            File Name               Count
            ----------------------  -----
            employees_20160801.dat      1

        }
        =

        GROUP File {
        ============

            Line
            ------------------------------------------------------------------
            ,LN 1,EM 1123456789012345678901234567890,01-JAN-2010,IT_PROG,10000

        }
        =

        GROUP Batch Job Table {
        =======================

            Name       Fail Percent
            ---------  ------------
            LOAD_EMPS            70

        }
        =

        GROUP Statistics Table {
        ========================

            Job Statistic Id  Batch job Id  File Name               Records Loaded  Records Failed ET  Records Failed DB  Start Time   End Time     Status
            ----------------  ------------  ----------------------  --------------  -----------------  -----------------  -----------  -----------  ------
                          60  LOAD_EMPS     employees_20160101.dat              10                  0                  2  01-JAN-2010  01-JAN-2010  S

        }
        =

        GROUP Employees Table {
        =======================

            Employee Id  Name  Email  Hired        Job      Salary  Manager Id  Department Id  Updated
            -----------  ----  -----  -----------  -------  ------  ----------  -------------  -----------
                   1984  LN 2  EM 2   01-JAN-2010  IT_PROG   20000                             01-JAN-2010

        }
        =

    OUTPUTS
    =======

        GROUP Employee: Actual = 1, Expected = 1 {
        ==========================================

            F?  Employee Id  Name  Email  Hired        Job      Salary  Updated
            --  -----------  ----  -----  -----------  -------  ------  -----------
                       1984  LN 2  EM 2   01-JAN-2010  IT_PROG  20000   01-JAN-2010

        } 0 failed, of 1: SUCCESS
        =========================

        GROUP Error: Actual = 1, Expected = 1 {
        =======================================

            F?  Job Statistic Id  ORA_ERR_TAG$  ORA_ERR_MESG$                                                                             ORA_ERR_OPTYP$  Employee Id  Name  Email                               Hired        Job      Salary
            --  ----------------  ------------  ----------------------------------------------------------------------------------------  --------------  -----------  ----  ----------------------------------  -----------  -------  ------
                              61                ORA-12899: value too large for column "HR"."EMPLOYEES"."EMAIL" (actual: 34, maximum: 25)  I                      1985  LN 1  EM 1123456789012345678901234567890  01-JAN-2010  IT_PROG  10000

        } 0 failed, of 1: SUCCESS
        =========================

        GROUP Job Statistic: Actual = 2, Expected = 2 {
        ===============================================

            F?  Job Statistic Id  Batch job Id  File Name               Records Loaded  Records Failed ET  Records Failed DB  Start Time   End Time     Status
            --  ----------------  ------------  ----------------------  --------------  -----------------  -----------------  -----------  -----------  ------
                              60  LOAD_EMPS     employees_20160101.dat              10                  0                  2  01-JAN-2010  01-JAN-2010  S
                              61  LOAD_EMPS     employees_20160801.dat               0                  0                  1  22-OCT-2016  22-OCT-2016  F

        } 0 failed, of 2: SUCCESS
        =========================

        GROUP Exception: Actual = 1, Expected = 1 {
        ===========================================

            F?  Message
            --  ------------------------------------------------------
                ORA-20001: Batch failed with too many invalid records!

        } 0 failed, of 1: SUCCESS
        =========================

} 0 failed, of 5: SUCCESS
=========================

SCENARIO 5: NV/OV/OU/NI/OI/EI: 1/0/0/0/1/0. Existing J/E: 1/1. Name too long [1 valid new, 1 invalid old - no exception] {
==========================================================================================================================

    INPUTS
    ======

        GROUP Parameter {
        =================

            File Name               Count
            ----------------------  -----
            employees_20160801.dat      2

        }
        =

        GROUP File {
        ============

            Line
            ----------------------------------------------------------------------
            1987,LN 1123456789012345678901234567890,EM 1,01-JAN-2010,IT_PROG,10000
            ,LN 3,EM 3,01-JAN-2010,IT_PROG,30000

        }
        =

        GROUP Batch Job Table {
        =======================

            Name       Fail Percent
            ---------  ------------
            LOAD_EMPS            70

        }
        =

        GROUP Statistics Table {
        ========================

            Job Statistic Id  Batch job Id  File Name               Records Loaded  Records Failed ET  Records Failed DB  Start Time   End Time     Status
            ----------------  ------------  ----------------------  --------------  -----------------  -----------------  -----------  -----------  ------
                          63  LOAD_EMPS     employees_20160101.dat              10                  0                  2  01-JAN-2010  01-JAN-2010  S

        }
        =

        GROUP Employees Table {
        =======================

            Employee Id  Name  Email  Hired        Job      Salary  Manager Id  Department Id  Updated
            -----------  ----  -----  -----------  -------  ------  ----------  -------------  -----------
                   1987  LN 2  EM 2   01-JAN-2010  IT_PROG   20000                             01-JAN-2010

        }
        =

    OUTPUTS
    =======

        GROUP Employee: Actual = 2, Expected = 2 {
        ==========================================

            F?  Employee Id  Name  Email  Hired        Job      Salary  Updated
            --  -----------  ----  -----  -----------  -------  ------  -----------
                       1987  LN 2  EM 2   01-JAN-2010  IT_PROG  20000   01-JAN-2010
                       1988  LN 3  EM 3   01-JAN-2010  IT_PROG  30000   22-OCT-2016

        } 0 failed, of 2: SUCCESS
        =========================

        GROUP Error: Actual = 1, Expected = 1 {
        =======================================

            F?  Job Statistic Id  ORA_ERR_TAG$  ORA_ERR_MESG$                                                                                 ORA_ERR_OPTYP$  Employee Id  Name                                Email  Hired        Job      Salary
            --  ----------------  ------------  --------------------------------------------------------------------------------------------  --------------  -----------  ----------------------------------  -----  -----------  -------  ------
                              64                ORA-12899: value too large for column "HR"."EMPLOYEES"."LAST_NAME" (actual: 34, maximum: 25)  U                      1987  LN 1123456789012345678901234567890  EM 1   01-JAN-2010  IT_PROG  10000

        } 0 failed, of 1: SUCCESS
        =========================

        GROUP Job Statistic: Actual = 2, Expected = 2 {
        ===============================================

            F?  Job Statistic Id  Batch job Id  File Name               Records Loaded  Records Failed ET  Records Failed DB  Start Time   End Time     Status
            --  ----------------  ------------  ----------------------  --------------  -----------------  -----------------  -----------  -----------  ------
                              63  LOAD_EMPS     employees_20160101.dat              10                  0                  2  01-JAN-2010  01-JAN-2010  S
                              64  LOAD_EMPS     employees_20160801.dat               1                  0                  1  22-OCT-2016  22-OCT-2016  S

        } 0 failed, of 2: SUCCESS
        =========================

        GROUP Exception: Actual = 0, Expected = 0: SUCCESS
        ==================================================

} 0 failed, of 6: SUCCESS
=========================

SCENARIO 6: NV/OV/OU/NI/OI/EI: 0/0/0/1/0/0. Existing J/E: 1/1. Invalid job [1 invalid new - exception] {
========================================================================================================

    INPUTS
    ======

        GROUP Parameter {
        =================

            File Name               Count
            ----------------------  -----
            employees_20160801.dat      1

        }
        =

        GROUP File {
        ============

            Line
            ------------------------------------
            ,LN 1,EM 1,01-JAN-2010,NON_JOB,10000

        }
        =

        GROUP Batch Job Table {
        =======================

            Name       Fail Percent
            ---------  ------------
            LOAD_EMPS            70

        }
        =

        GROUP Statistics Table {
        ========================

            Job Statistic Id  Batch job Id  File Name               Records Loaded  Records Failed ET  Records Failed DB  Start Time   End Time     Status
            ----------------  ------------  ----------------------  --------------  -----------------  -----------------  -----------  -----------  ------
                          66  LOAD_EMPS     employees_20160101.dat              10                  0                  2  01-JAN-2010  01-JAN-2010  S

        }
        =

        GROUP Employees Table {
        =======================

            Employee Id  Name  Email  Hired        Job      Salary  Manager Id  Department Id  Updated
            -----------  ----  -----  -----------  -------  ------  ----------  -------------  -----------
                   1990  LN 2  EM 2   01-JAN-2010  IT_PROG   20000                             01-JAN-2010

        }
        =

    OUTPUTS
    =======

        GROUP Employee: Actual = 1, Expected = 1 {
        ==========================================

            F?  Employee Id  Name  Email  Hired        Job      Salary  Updated
            --  -----------  ----  -----  -----------  -------  ------  -----------
                       1990  LN 2  EM 2   01-JAN-2010  IT_PROG  20000   01-JAN-2010

        } 0 failed, of 1: SUCCESS
        =========================

        GROUP Error: Actual = 1, Expected = 1 {
        =======================================

            F?  Job Statistic Id  ORA_ERR_TAG$  ORA_ERR_MESG$                                                                    ORA_ERR_OPTYP$  Employee Id  Name  Email  Hired        Job      Salary
            --  ----------------  ------------  -------------------------------------------------------------------------------  --------------  -----------  ----  -----  -----------  -------  ------
                              67                ORA-02291: integrity constraint (HR.EMP_JOB_FK) violated - parent key not found  I                      1991  LN 1  EM 1   01-JAN-2010  NON_JOB  10000

        } 0 failed, of 1: SUCCESS
        =========================

        GROUP Job Statistic: Actual = 2, Expected = 2 {
        ===============================================

            F?  Job Statistic Id  Batch job Id  File Name               Records Loaded  Records Failed ET  Records Failed DB  Start Time   End Time     Status
            --  ----------------  ------------  ----------------------  --------------  -----------------  -----------------  -----------  -----------  ------
                              66  LOAD_EMPS     employees_20160101.dat              10                  0                  2  01-JAN-2010  01-JAN-2010  S
                              67  LOAD_EMPS     employees_20160801.dat               0                  0                  1  22-OCT-2016  22-OCT-2016  F

        } 0 failed, of 2: SUCCESS
        =========================

        GROUP Exception: Actual = 1, Expected = 1 {
        ===========================================

            F?  Message
            --  ------------------------------------------------------
                ORA-20001: Batch failed with too many invalid records!

        } 0 failed, of 1: SUCCESS
        =========================

} 0 failed, of 5: SUCCESS
=========================

SCENARIO 7: NV/OV/OU/NI/OI/EI: 0/1/0/1/1/0. Existing J/E: 1/2. 2 invalid jobs [1 valid old, 2 invalid: old and new - no exception] {
====================================================================================================================================

    INPUTS
    ======

        GROUP Parameter {
        =================

            File Name               Count
            ----------------------  -----
            employees_20160801.dat      3

        }
        =

        GROUP File {
        ============

            Line
            -----------------------------------------
            ,LN 1,EM 1,01-JAN-2010,NON_JOB,10000
            1993,LN 2,EM 2,01-JAN-2010,NON_JOB,20000
            1994,LN 3U,EM 3,01-JAN-2010,IT_PROG,30000

        }
        =

        GROUP Batch Job Table {
        =======================

            Name       Fail Percent
            ---------  ------------
            LOAD_EMPS            70

        }
        =

        GROUP Statistics Table {
        ========================

            Job Statistic Id  Batch job Id  File Name               Records Loaded  Records Failed ET  Records Failed DB  Start Time   End Time     Status
            ----------------  ------------  ----------------------  --------------  -----------------  -----------------  -----------  -----------  ------
                          69  LOAD_EMPS     employees_20160101.dat              10                  0                  2  01-JAN-2010  01-JAN-2010  S

        }
        =

        GROUP Employees Table {
        =======================

            Employee Id  Name  Email  Hired        Job      Salary  Manager Id  Department Id  Updated
            -----------  ----  -----  -----------  -------  ------  ----------  -------------  -----------
                   1993  LN 2  EM 2   01-JAN-2010  IT_PROG   20000                             01-JAN-2010
                   1994  LN 3  EM 3   01-JAN-2010  IT_PROG   30000                             01-JAN-2010

        }
        =

    OUTPUTS
    =======

        GROUP Employee: Actual = 2, Expected = 2 {
        ==========================================

            F?  Employee Id  Name   Email  Hired        Job      Salary  Updated
            --  -----------  -----  -----  -----------  -------  ------  -----------
                       1993  LN 2   EM 2   01-JAN-2010  IT_PROG  20000   01-JAN-2010
                       1994  LN 3U  EM 3   01-JAN-2010  IT_PROG  30000   22-OCT-2016

        } 0 failed, of 2: SUCCESS
        =========================

        GROUP Error: Actual = 2, Expected = 2 {
        =======================================

            F?  Job Statistic Id  ORA_ERR_TAG$  ORA_ERR_MESG$                                                                    ORA_ERR_OPTYP$  Employee Id  Name  Email  Hired        Job      Salary
            --  ----------------  ------------  -------------------------------------------------------------------------------  --------------  -----------  ----  -----  -----------  -------  ------
                              70                ORA-02291: integrity constraint (HR.EMP_JOB_FK) violated - parent key not found  U                      1993  LN 2  EM 2   01-JAN-2010  NON_JOB  20000
                              70                ORA-02291: integrity constraint (HR.EMP_JOB_FK) violated - parent key not found  I                      1995  LN 1  EM 1   01-JAN-2010  NON_JOB  10000

        } 0 failed, of 2: SUCCESS
        =========================

        GROUP Job Statistic: Actual = 2, Expected = 2 {
        ===============================================

            F?  Job Statistic Id  Batch job Id  File Name               Records Loaded  Records Failed ET  Records Failed DB  Start Time   End Time     Status
            --  ----------------  ------------  ----------------------  --------------  -----------------  -----------------  -----------  -----------  ------
                              69  LOAD_EMPS     employees_20160101.dat              10                  0                  2  01-JAN-2010  01-JAN-2010  S
                              70  LOAD_EMPS     employees_20160801.dat               1                  0                  2  22-OCT-2016  22-OCT-2016  S

        } 0 failed, of 2: SUCCESS
        =========================

        GROUP Exception: Actual = 0, Expected = 0: SUCCESS
        ==================================================

} 0 failed, of 7: SUCCESS
=========================

SCENARIO 8: NV/OV/OU/NI/OI/EI: 0/1/0/0/0/1. Existing J/E: 1/2. Name 4001ch [1 valid old, 1 invalid new for external table - no exception; also file had previously failed] {
============================================================================================================================================================================

    INPUTS
    ======

        GROUP Parameter {
        =================

            File Name               Count
            ----------------------  -----
            employees_20160801.dat      2

        }
        =

        GROUP File {
        ============

            Line
            --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
            ,123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567
            1998,LN 3U,EM 3,01-JAN-2010,IT_PROG,30000

        }
        =

        GROUP Batch Job Table {
        =======================

            Name       Fail Percent
            ---------  ------------
            LOAD_EMPS            70

        }
        =

        GROUP Statistics Table {
        ========================

            Job Statistic Id  Batch job Id  File Name               Records Loaded  Records Failed ET  Records Failed DB  Start Time   End Time     Status
            ----------------  ------------  ----------------------  --------------  -----------------  -----------------  -----------  -----------  ------
                          72  LOAD_EMPS     employees_20160801.dat               0                  0                  2  01-JAN-2010  01-JAN-2010  F

        }
        =

        GROUP Employees Table {
        =======================

            Employee Id  Name  Email  Hired        Job      Salary  Manager Id  Department Id  Updated
            -----------  ----  -----  -----------  -------  ------  ----------  -------------  -----------
                   1997  LN 2  EM 2   01-JAN-2010  IT_PROG   20000                             01-JAN-2010
                   1998  LN 3  EM 3   01-JAN-2010  IT_PROG   30000                             01-JAN-2010

        }
        =

    OUTPUTS
    =======

        GROUP Employee: Actual = 2, Expected = 2 {
        ==========================================

            F?  Employee Id  Name   Email  Hired        Job      Salary  Updated
            --  -----------  -----  -----  -----------  -------  ------  -----------
                       1997  LN 2   EM 2   01-JAN-2010  IT_PROG  20000   01-JAN-2010
                       1998  LN 3U  EM 3   01-JAN-2010  IT_PROG  30000   22-OCT-2016

        } 0 failed, of 2: SUCCESS
        =========================

        GROUP Error: Actual = 0, Expected = 0: SUCCESS
        ==============================================

        GROUP Job Statistic: Actual = 2, Expected = 2 {
        ===============================================

            F?  Job Statistic Id  Batch job Id  File Name               Records Loaded  Records Failed ET  Records Failed DB  Start Time   End Time     Status
            --  ----------------  ------------  ----------------------  --------------  -----------------  -----------------  -----------  -----------  ------
                              72  LOAD_EMPS     employees_20160801.dat               0                  0                  2  01-JAN-2010  01-JAN-2010  F
                              73  LOAD_EMPS     employees_20160801.dat               1                  1                  0  22-OCT-2016  22-OCT-2016  S

        } 0 failed, of 2: SUCCESS
        =========================

        GROUP Exception: Actual = 0, Expected = 0: SUCCESS
        ==================================================

} 0 failed, of 6: SUCCESS
=========================

SCENARIO 9: NV/OV/OU/NI/OI/EI: 0/0/0/1/0/0. Existing J/E: 1/1. [File already processed - exception] {
=====================================================================================================

    INPUTS
    ======

        GROUP Parameter {
        =================

            File Name               Count
            ----------------------  -----
            employees_20160801.dat      1

        }
        =

        GROUP File {
        ============

            Line
            ------------------------------------
            ,LN 1,EM 1,01-JAN-2010,NON_JOB,10000

        }
        =

        GROUP Batch Job Table {
        =======================

            Name       Fail Percent
            ---------  ------------
            LOAD_EMPS            70

        }
        =

        GROUP Statistics Table {
        ========================

            Job Statistic Id  Batch job Id  File Name               Records Loaded  Records Failed ET  Records Failed DB  Start Time   End Time     Status
            ----------------  ------------  ----------------------  --------------  -----------------  -----------------  -----------  -----------  ------
                          75  LOAD_EMPS     employees_20160801.dat              10                  0                  2  01-JAN-2010  01-JAN-2010  S

        }
        =

        GROUP Employees Table {
        =======================

            Employee Id  Name  Email  Hired        Job      Salary  Manager Id  Department Id  Updated
            -----------  ----  -----  -----------  -------  ------  ----------  -------------  -----------
                   2000  LN 2  EM 2   01-JAN-2010  IT_PROG   20000                             01-JAN-2010

        }
        =

    OUTPUTS
    =======

        GROUP Employee: Actual = 1, Expected = 1 {
        ==========================================

            F?  Employee Id  Name  Email  Hired        Job      Salary  Updated
            --  -----------  ----  -----  -----------  -------  ------  -----------
                       2000  LN 2  EM 2   01-JAN-2010  IT_PROG  20000   01-JAN-2010

        } 0 failed, of 1: SUCCESS
        =========================

        GROUP Error: Actual = 0, Expected = 0: SUCCESS
        ==============================================

        GROUP Job Statistic: Actual = 1, Expected = 1 {
        ===============================================

            F?  Job Statistic Id  Batch job Id  File Name               Records Loaded  Records Failed ET  Records Failed DB  Start Time   End Time     Status
            --  ----------------  ------------  ----------------------  --------------  -----------------  -----------------  -----------  -----------  ------
                              75  LOAD_EMPS     employees_20160801.dat              10                  0                  2  01-JAN-2010  01-JAN-2010  S

        } 0 failed, of 1: SUCCESS
        =========================

        GROUP Exception: Actual = 1, Expected = 1 {
        ===========================================

            F?  Message
            --  --------------------------------------------------------
                ORA-20002: File has already been processed successfully!

        } 0 failed, of 1: SUCCESS
        =========================

} 0 failed, of 4: SUCCESS
=========================

TIMING: Actual = 254, Expected <= 2: FAILURE
============================================

SUMMARY for TT_Emp_Batch.tt_AIP_Load_Emps
=========================================

Scenario                                                                                                                                                        # Failed  # Tests  Status
--------------------------------------------------------------------------------------------------------------------------------------------------------------  --------  -------  -------
NV/OV/OU/NI/OI/EI: 1/0/0/0/0/0. Existing J/E: 0/0. [1 valid new record from scratch]                                                                                   0        4  SUCCESS
NV/OV/OU/NI/OI/EI: 1/1/1/0/0/0. Existing J/E: 1/0. [3 valid records of each kind]                                                                                      0        7  SUCCESS
NV/OV/OU/NI/OI/EI: 0/0/0/0/1/0. Existing J/E: 1/1. Uid not found [1 invalid old - exception]                                                                           0        5  SUCCESS
NV/OV/OU/NI/OI/EI: 0/0/0/1/0/0. Existing J/E: 1/1. Email too long [1 invalid new - exception]                                                                          0        5  SUCCESS
NV/OV/OU/NI/OI/EI: 1/0/0/0/1/0. Existing J/E: 1/1. Name too long [1 valid new, 1 invalid old - no exception]                                                           0        6  SUCCESS
NV/OV/OU/NI/OI/EI: 0/0/0/1/0/0. Existing J/E: 1/1. Invalid job [1 invalid new - exception]                                                                             0        5  SUCCESS
NV/OV/OU/NI/OI/EI: 0/1/0/1/1/0. Existing J/E: 1/2. 2 invalid jobs [1 valid old, 2 invalid: old and new - no exception]                                                 0        7  SUCCESS
NV/OV/OU/NI/OI/EI: 0/1/0/0/0/1. Existing J/E: 1/2. Name 4001ch [1 valid old, 1 invalid new for external table - no exception; also file had previously failed]         0        6  SUCCESS
NV/OV/OU/NI/OI/EI: 0/0/0/1/0/0. Existing J/E: 1/1. [File already processed - exception]                                                                                0        4  SUCCESS
Timing                                                                                                                                                                 1        1  FAILURE
--------------------------------------------------------------------------------------------------------------------------------------------------------------  --------  -------  -------
Total                                                                                                                                                                  1       50  FAILURE
--------------------------------------------------------------------------------------------------------------------------------------------------------------  --------  -------  -------

Timer Set: TT_Emp_Batch.tt_AIP_Load_Emps, Constructed at 22 Oct 2016 15:05:16, written at 15:05:20
==================================================================================================
[Timer timed: Elapsed (per call): 0.03 (0.000033), CPU (per call): 0.03 (0.000030), calls: 1000, '***' denotes corrected line below]

Timer           Elapsed         CPU         Calls       Ela/Call       CPU/Call
-----------  ----------  ----------  ------------  -------------  -------------
Setup              0.97        0.23             9        0.10811        0.02556
Caller             1.27        0.99             5        0.25400        0.19800
Get_Tab_Lis        0.73        0.59             9        0.08122        0.06556
Get_Err_Lis        0.00        0.00             9        0.00011        0.00000
Get_Jbs_Lis        0.01        0.02             9        0.00056        0.00222
(Other)            0.34        0.35             1        0.34200        0.35000
-----------  ----------  ----------  ------------  -------------  -------------
Total              3.32        2.18            42        0.07910        0.05190
-----------  ----------  ----------  ------------  -------------  -------------

Suite Summary
=============

Package.Procedure                  Tests  Fails         ELA         CPU
---------------------------------  -----  -----  ----------  ----------
TT_Emp_WS.tt_AIP_Save_Emps            17      2        0.09        0.08
TT_Emp_WS.tt_AIP_Get_Dept_Emps         8      0        0.14        0.14
TT_View_Drivers.tt_HR_Test_View_V      9      0        0.11        0.11
TT_Emp_Batch.tt_AIP_Load_Emps         50      1        3.32        2.18
---------------------------------  -----  -----  ----------  ----------
Total                                 84      3        3.66        2.51
---------------------------------  -----  -----  ----------  ----------
Others error in (): ORA-20001: Suite BRENDAN returned error status: ORA-06512: at "DP_3.UTILS_TT", line 152
ORA-06512: at "DP_3.UTILS_TT", line 819
ORA-06512: at line 5


1687 rows selected.






 

Design Patterns for Database API Testing 2: Views 2 - Code

In Design Patterns for Database API Testing 3: Views - Design I presented a design pattern for unit testing views, using an example based on Oracle's HR demo schema, and here I list the code for the main unit test procedure and a couple of the utility procedures, with notes (all the code is included in a linked article).

First a structure diagram shows how the PL/SQL packages relate to each other, and two call structure tables are given for both the main unit test procedure for the view and for the previous web service saving procedure. The utility code has been substantially re-factored to improve output format and for other reasons. Sections of the code are listed with notes.

All the code and installation scripts are supplied in a linked article. With these scripts, anyone can run and experiment with the unit testing example, and can use it as a template for developing real unit testing code following the same design patterns.

Update, 26 June 2016: The code has been re-factored to improve formatting and remove the dependence on utPLSQL, TRAPIT - TRansactional API Testing in Oracle.
Update, 10 July 2016: I have added in scenario-level printing of all inputs.

Package Structure Diagram

Unit Testing-CSD-All

Call Structure Table - UT_Emp_WS.ut_AIP_Save_Emps

Level 1 Level 2 Level 3 Package
Init UT_Utils
Setup UT_Emp_WS
Increment_Time Timer_Set
Call_WS UT_Emp_WS
Do_Save UT_Emp_WS
Init_Time Timer_Set
AIP_Save_Emps UT_Emp_WS
Increment_Time Timer_Set
Get_Tab_Lis UT_Emp_WS
List_Delim Utils
Get_Arr_Lis UT_Emp_WS
List_Delim Utils
List_or_Empty UT_Utils
Check_UT_Results UT_Utils
Write_Other_Error Utils

Call Structure Table - UT_View_Drivers.ut_HR_Test_View_V

Level 1 Level 2 Package
Init UT_Utils
Setup UT_View_Drivers
Heading Utils
Col_Headers Utils
Ins_Emp DML_API_UT_HR
Increment_Time Timer_Set
Get_View UT_Utils
Check_UT_Results UT_Utils
Write_Other_Error Utils

UT_View_Drivers.ut_HR_Test_View_V - View Unit Test Procedure

Declare section

PROCEDURE ut_HR_Test_View_V IS

  c_view_name           CONSTANT VARCHAR2(61) := 'HR_Test_View_V';
  c_proc_name           CONSTANT VARCHAR2(61) := 'UT_View_Drivers.ut_' || c_view_name;
  c_dep_id_1            CONSTANT PLS_INTEGER := 10;
  c_dep_id_2            CONSTANT PLS_INTEGER := 20;
  c_dep_nm_1            CONSTANT VARCHAR2(100) := 'Administration';
  c_dep_nm_2            CONSTANT VARCHAR2(100) := 'Marketing';
  c_job_bad             CONSTANT VARCHAR2(100) := 'AD_ASST';
  c_job_good            CONSTANT VARCHAR2(100) := 'IT_PROG';
  c_base_sal            CONSTANT PLS_INTEGER := 1000;

  c_ln_pre              CONSTANT VARCHAR2(10) := DML_API_UT_HR.c_ln_pre;

  c_sel_lis             CONSTANT L1_chr_arr := L1_chr_arr ('last_name', 'department_name', 'manager', 'salary', 'sal_rat', 'sal_rat_g');
  c_where_lis           CONSTANT L1_chr_arr := L1_chr_arr (NULL, NULL, 'department_name=''Administration''', NULL);

  c_dataset_3lis        CONSTANT L3_chr_arr := L3_chr_arr (
                             L2_chr_arr (L1_chr_arr ('4 emps, 1 dep (10), emp-3 has no dep, emp-4 has bad job'),
--                                         dep           job          salary
                               L1_chr_arr (c_dep_id_1,   c_job_good,  '1000'),
                               L1_chr_arr (c_dep_id_1,   c_job_good,  '2000'),
                               L1_chr_arr (NULL,         c_job_good,  '3000'),
                               L1_chr_arr (c_dep_id_1,   c_job_bad,   '4000')
                                             ),
                             L2_chr_arr (L1_chr_arr ('As dataset 1 but with extra emp-5, in second dep (20)'),
                               L1_chr_arr (c_dep_id_1,   c_job_good,  '1000'),
                               L1_chr_arr (c_dep_id_1,   c_job_good,  '2000'),
                               L1_chr_arr (NULL,         c_job_good,  '3000'),
                               L1_chr_arr (c_dep_id_1,   c_job_bad,   '4000'),
                               L1_chr_arr (c_dep_id_2,   c_job_good,  '5000')
                                             ),
                             L2_chr_arr (L1_chr_arr ('As dataset 2 but with salaries * 0.1, total below reporting threshold of 1600'),
                               L1_chr_arr (c_dep_id_1,   c_job_good,  '100'),
                               L1_chr_arr (c_dep_id_1,   c_job_good,  '200'),
                               L1_chr_arr (NULL,         c_job_good,  '300'),
                               L1_chr_arr (c_dep_id_1,   c_job_bad,   '400'),
                               L1_chr_arr (c_dep_id_2,   c_job_good,  '500')
                                             )
                        );

  c_exp_2lis            CONSTANT L2_chr_arr := L2_chr_arr (
                                               L1_chr_arr (
                                       Utils.List_Delim (c_ln_pre || '1',   c_dep_nm_1, NULL,            '1000', '.67',   '.4'),
                                       Utils.List_Delim (c_ln_pre || '2',   c_dep_nm_1, c_ln_pre || '1', '2000',  '1.33', '.8')
                                               ),
                                               L1_chr_arr (
                                       Utils.List_Delim (c_ln_pre || '1',   c_dep_nm_1, NULL,            '1000', '.67',  '.33'),
                                       Utils.List_Delim (c_ln_pre || '2',   c_dep_nm_1, c_ln_pre || '1', '2000',  '1.33', '.67'),
                                       Utils.List_Delim (c_ln_pre || '5',   c_dep_nm_2, c_ln_pre || '1', '5000',  '1',    '1.67')
                                               ),
                                               L1_chr_arr (
                                       Utils.List_Delim (c_ln_pre || '1',   c_dep_nm_1, NULL,            '1000', '.67',   '.33'),
                                       Utils.List_Delim (c_ln_pre || '2',   c_dep_nm_1, c_ln_pre || '1', '2000',  '1.33', '.67')
                                               ),
                                               ut_Utils.c_empty_list
                        );

  c_scenario_ds_lis     CONSTANT L1_num_arr := L1_num_arr (1, 2, 2, 3);
  c_scenario_lis        CONSTANT L1_chr_arr := L1_chr_arr (
                               'DS-1, testing inner, outer joins, analytic over dep, and global ratios with 1 dep',
                               'DS-2, testing same as 1 but with extra emp in another dep',
                               'DS-2, passing ''WHERE dep=10''',
                               'DS-3, Salaries total 1500 (< threshold of 1600)');

  c_inp_group_lis       CONSTANT L1_chr_arr := L1_chr_arr ('Employee', 'Where');
  c_inp_field_2lis      CONSTANT L2_chr_arr := L2_chr_arr (
                                                        L1_chr_arr (
                                                                '*Employee Id',
                                                                'Last Name',
                                                                'Email',
                                                                'Hire Date',
                                                                'Job',
                                                                '*Salary',
                                                                '*Manager Id',
                                                                '*department Id'),
                                                        L1_chr_arr (
                                                                'Where')
  );
  c_out_field_2lis      CONSTANT L2_chr_arr :=  L2_chr_arr ( L1_chr_arr (
                                'Name',
                                'Department',
                                'Manager',
                                '*Salary',
                                '*Salary Ratio (dep)',
                                '*Salary Ratio (overall)'));

  l_act_2lis                      L2_chr_arr := L2_chr_arr();
  c_ms_limit            CONSTANT PLS_INTEGER := 1;
  l_timer_set                    PLS_INTEGER;
  l_inp_3lis                     L3_chr_arr := L3_chr_arr();

Notes on declare section

  • Data sets, scenarios, expected values etc. are stored in generic arrays, where:
    • L1_chr_arr is type of array of VARCHAR2(4000), same as standard type SYS.ODCIVarchar2List
    • L2_chr_arr is a type of array of L1_chr_arr
    • L3_chr_arr is a type of array of L2_chr_arr

Setup section

  PROCEDURE Setup (p_call_ind PLS_INTEGER, x_inp_lis OUT L1_chr_arr) IS -- scenario index

    l_emp_id            PLS_INTEGER;
    l_mgr_id            PLS_INTEGER;
    l_len_lis           L1_num_arr := L1_num_arr (1, -11, -13, -10, 10, -10);

  BEGIN

    Utils.Heading ('Employees created in setup: DS-' || p_call_ind || ' - ' || c_dataset_3lis (p_call_ind)(1)(1));
    Utils.Col_Headers (L1_chr_arr ('#', 'Employee id', 'Department id', 'Manager', 'Job id', 'Salary'), l_len_lis);
    x_inp_lis := L1_chr_arr();
    x_inp_lis.EXTEND (c_dataset_3lis (p_call_ind).COUNT - 1);
    FOR i IN 2..c_dataset_3lis (p_call_ind).COUNT LOOP

      l_emp_id := DML_API_UT_HR.Ins_Emp (
                            p_emp_ind  => i - 1,
                            p_dep_id   => c_dataset_3lis (p_call_ind)(i)(1),
                            p_mgr_id   => l_mgr_id,
                            p_job_id   => c_dataset_3lis (p_call_ind)(i)(2),
                            p_salary   => c_dataset_3lis (p_call_ind)(i)(3),
                            x_rec      => x_inp_lis(i - 1));
      Utils.Pr_List_As_Line (L1_chr_arr ((i-1), l_emp_id, Nvl (c_dataset_3lis (p_call_ind)(i)(1), ' '), Nvl (To_Char(l_mgr_id), ' '), c_dataset_3lis (p_call_ind)(i)(2), c_dataset_3lis (p_call_ind)(i)(3)), l_len_lis);
      IF i = 2 THEN
        l_mgr_id := l_emp_id;
      END IF;

    END LOOP;

  END Setup;

Notes on setup section

  • c_dataset_3lis contains the data for all data sets indexed by (data set, record, field)
  • Setup is called for a single data set at a time in each scenario
  • Description of the data set is contained in the array and printed out
  • Data set is printed out in tabular format. In the most recent version of the utility code, this is not strictly necessary, because all the input data is printed out before the outputs

Main section

BEGIN

  l_timer_set := UT_Utils.Init (c_proc_name);
  l_act_2lis.EXTEND (c_exp_2lis.COUNT);
  l_inp_3lis.EXTEND (c_exp_2lis.COUNT);

  FOR i IN 1..c_exp_2lis.COUNT LOOP

    l_inp_3lis (i) := L2_chr_arr();
    l_inp_3lis (i).EXTEND(2);

    Setup (c_scenario_ds_lis (i), l_inp_3lis (i)(1));

    l_inp_3lis (i)(2) := L1_chr_arr (c_where_lis(i));
    Timer_Set.Increment_Time (l_timer_set, UT_Utils.c_setup_timer);
    l_act_2lis(i) := UT_Utils.Get_View (
                            p_view_name         => c_view_name,
                            p_sel_field_lis     => c_sel_lis,
                            p_where             => c_where_lis(i),
                            p_timer_set         => l_timer_set);

  END LOOP;

  UT_Utils.Check_UT_Results (c_proc_name, c_scenario_lis, l_inp_3lis, l_act_2lis, c_exp_2lis, l_timer_set, c_ms_limit,
                             c_inp_group_lis, c_inp_field_2lis, c_out_group_lis, c_out_field_2lis);

EXCEPTION

  WHEN OTHERS THEN
    Utils.Write_Other_Error;
    RAISE;

END ut_HR_Test_View_V;

Notes on main section

  • It's quite short isn't it šŸ™‚
  • Main section loops over the scenarios
  • Setup is called to create the data set for the scenario
  • Get_View returns the results of the query on the view as 2-level array
  • Get_View rolls back after getting the results, so the inserted test records are removed from the database
  • Check_UT_Results is called to do all the assertions within nested loops, then print the results

UT_Utils - Unit Test Utility Procedures

Check_UT_Results - to check results from testing

PROCEDURE Check_UT_Results (p_proc_name                 VARCHAR2,      -- calling procedure
                            p_test_lis                  L1_chr_arr,    -- test descriptions
                            p_inp_3lis                  L3_chr_arr,    -- actual result strings
                            p_act_3lis                  L3_chr_arr,    -- actual result strings
                            p_exp_3lis                  L3_chr_arr,    -- expected result strings
                            p_timer_set                 PLS_INTEGER,   -- timer set index
                            p_ms_limit                  PLS_INTEGER,   -- call time limit in ms
                            p_inp_group_lis             L1_chr_arr,    -- input group names
                            p_inp_fields_2lis           L2_chr_arr,    -- input fields descriptions
                            p_out_group_lis             L1_chr_arr,    -- output group names
                            p_fields_2lis               L2_chr_arr) IS -- test fields descriptions

  l_num_fails_sce                L1_num_arr :=  L1_num_arr();
  l_num_tests_sce                L1_num_arr :=  L1_num_arr();
  l_tot_fails                    PLS_INTEGER := 0;
  l_tot_tests                    PLS_INTEGER := 0;

.
.
.
(private procedures - see gitHub project, https://github.com/BrenPatF/db_unit_test, for full code listings)
.
.
.
BEGIN

  Detail_Section (l_num_fails_sce, l_num_tests_sce);
  Summary_Section (l_num_fails_sce, l_num_tests_sce, l_tot_fails, l_tot_tests);
  Set_Global_Summary (l_tot_fails, l_tot_tests + 1);

END Check_UT_Results;

Notes on Check_UT_Results

  • This is the base version of Check_UT_Results with 3-level arrays of expected and actuals
  • The view testing procedure has only one output group, and an overloaded version is called that passes 1 record in the middle index to the base version
  • The inner loop asserts actual values (which are records) against expected
  • The final assertion is against average call time
  • It is expected that all assertion within a unit test procedure will be via a single call to one of the versions of this procedure, making a big reduction in code compared with traditional unit testing approaches
  • After final assertion a call is made to write out all the results, by scenario, with all inputs printed first, followed by actuals (and expected, where they differ); this means that the unit test outputs now become precise and accurate documents of what the program does

Get_View - run a query dynamically on a view and return result set as array of strings

FUNCTION Get_View (p_view_name         VARCHAR2,               -- name of view
                   p_sel_field_lis     L1_chr_arr,             -- list of fields to select
                   p_where             VARCHAR2 DEFAULT NULL,  -- optional where clause
                   p_timer_set         PLS_INTEGER)            -- timer set handle
                   RETURN              L1_chr_arr IS           -- list of delimited result records

  l_cur            SYS_REFCURSOR;
  l_sql_txt        VARCHAR2(32767) := 'SELECT Utils.List_Delim (L1_chr_arr (';
  l_result_lis     L1_chr_arr;
  l_len            PLS_INTEGER;

BEGIN

  FOR i IN 1..p_sel_field_lis.COUNT LOOP

    l_sql_txt := l_sql_txt || p_sel_field_lis(i) || ',';

  END LOOP;

  l_sql_txt := RTrim (l_sql_txt, ',') || ')) FROM ' || p_view_name || ' WHERE ' || Nvl (p_where, '1=1 ') || 'ORDER BY 1';

  OPEN l_cur FOR l_sql_txt;

  FETCH l_cur BULK COLLECT -- ut, small result set, hence no need for limit clause
   INTO l_result_lis;

  CLOSE l_cur;

  Timer_Set.Increment_Time (p_timer_set, UT_Utils.c_call_timer);
  ROLLBACK;
  RETURN ut_Utils.List_or_Empty (l_result_lis);

END Get_View;

Notes on Get_View

  • A query string is constructed from the input list of fields and optional where clause
  • The fields are concatenated with delimiters and returned into an array of strings
  • A rollback occurs to remove any test data created, so as not to interfere with any subsequent call
  • If no data was returned from the query, we return a default 1-record listing containing the string 'EMPTY'

Package block section

BEGIN

  DBMS_Application_Info.Set_Client_Info (client_info => 'UT');
  Utils.c_session_id_if_UT := SYS_Context ('userenv', 'sessionid');

END UT_Utils;

Notes on package block section

  • client_info is set to 'UT', meaning the session operates in test mode
  • The session id is stored in a package variable
  • This id is referenced in the testing views and in insertion of test records with utid column

Installation Instructions

See Brendan's Database Unit Testing Framework for a link to the installation code for both the framework and the demo code on gitHub, and links to related articles.

Conclusions

  • A second design pattern has been presented, for testing views, with scenarios and output results given in Design Patterns for Database Unit Testing 3: Views - Design
  • The implementation, presented here, was against an Oracle database publicly available demonstration schema, and used Brendan's database unit testing framework






 

Design Patterns for Database API Testing 2: Views 1 - Design

In the first of a 2-part article, Design Patterns for Database API Testing 1: Web Service Saving - Design I presented a design pattern for unit testing web service 'save' procedures, beginning with an abstract discussion, and progressing to an example based on Oracle's HR demo schema. In the second part, Design Patterns for Database API Testing 2: Web Service Saving - Code, I listed various extremely common antipatterns in database unit testing, and how they may be avoided, and provided the code with notes.

In this post, I present a design pattern for unit testing views, as the first of another 2-part article. I start by discussing when and how to unit test views. Unlike in the previous article, test data has to be created during unit testing of views, and a very general approach to creating and selecting the test data is proposed. The use case for the design pattern is described, and scenarios and sub-scenarios are defined conceptually. Finally, the output from the unit testing is presented, with notes.

The second post provides the code, with notes: Design Patterns for Database API Testing 4: Views - Code. As before, a self-contained installation file is attached (now in a separate link) for others who may be interested, including the earlier web service API test code, which has been substantially re-factored.

Update, 26 June 2016:The code has been re-factored to improve formatting and remove the dependence on utPLSQL, TRAPIT - TRansactional API Testing in Oracle.
Update, 09 July 2016:I have added in scenario-level printing of all inputs.

When to Unit Test Views

Views can be simple or complex, or, as I categorise them in Brendan's 2-Page Oracle Programming Standards, thin or thick, where thick views include table joins and thinviews don't. Thin views do not normally require unit testing while it may or may not be appropriate to test thick views.

As explained in the second part article mentioned above, method-based testing is a bad idea, and occurs when the test suite is based on testing all the methods in a package, rather than units of (external) behaviour (often corresponding to procedures prefixed AIP in a common naming convention). Similarly, we can consider views in the same way as methods and ask whether they represent testable units of behaviour or are merely internal code structures, which should not normally have individual automated unit tests for the reasons given there.

Good examples of views that should be unit tested would be those that form the basis of complex data extraction to file, by ETL tools such as Informatica, or those that form the basis of reporting tools such as Business Objects. In fact, it is very good practice to place SQL for these tools into views precisely so that they can be unit tested.

How to Unit Test Views Using a PL/SQL Testing Framework

In order to leverage a PL/SQL unit testing framework to also test views, the unit test package procedures call a library procedure passing the name of the relevant view: The library procedure returns the result of querying the view as an array of delimited strings, and the unit test procedures then compare the results against their own expected results.

Each unit test procedure will have its own setup local procedure to create test data, and we need to discuss the issue of distinguishing test data from pre-existing data.

Unit Test Data

In the earlier article on database save procedures, we did not create any test data within the testing code itself, but the base procedure did create data, and those were queried back for assertion. In order to select only the data created by the procedure call a prefix was used in one of the string fields which was assumed not to exist already. This is a workable approach in some cases, but may not be possible in general. Let us consider the different types of database data that may affect our testing:

  • Data created by the base code being tested
  • Data created by test code to be read by the base code
  • Data not created by test code to be read by base code

In order to verify that the program calls are giving results as expected, the test code needs to know all the data that influence the results, not necessarily just directly created data. Our view testing use case described below has an example where the results depend on an aggregate of all the records on the database. This is a problem when we have a shared database, where we cannot freeze the data at the time of unit test development. In order to handle this problem, we propose to borrow a technique used in Oracle's ebusinees applications.

Partitioning Views with System Contexts
In Oracle ebusiness's multi-org implementations (at least up to release 11), transactions are partitioned by a numeric identifier for the organization owning the transaction. This org_id value is stored in a column in the base table on transaction creation. Within the application code the base table is not queried directly, but through a view that restricts records returned to those for the organization corresponding to the current role of the application user, which is stored in the userenv system context.
See SYS_CONTEXT) for information on the system context database feature, and Multiple Organizations in Oracle Applications for its use in Oracle ebusiness.

Partitioning Views for Unit Testing
We propose to use views in a similar way to the multi-org views, to restrict records to those created in the unit testing session, by means of a utid column on the base table that will hold the session id. The new optional column is added to those tables where this approach is required, and view are created on the tables. Our unit testing utility package UT_Utils sets a context variable to the value 'UT' to signify testing mode, and the session id is set to a package variable in the general utilities package Utils.

Any base code that inserts data into the tables has to check for test mode, and if set, put the session id into the utid field, and if not, leave it blank. The views use the following clause:

 WHERE (utid = SYS_Context ('userenv', 'sessionid') OR 
        Substr (Nvl (SYS_Context ('userenv', 'client_info'), 'XX'), 1, 2) != 'UT')

Both unit test code and base code now query the views instead of the base tables. As the base code to write to the tables has to account for the new column, it is necessary for the column to be added in all instances including production. If this seems a little drastic, consider the importance that you attach to unit testing, and bear in mind that the earlier, less general, approaches may suffice in many cases. In this design pattern demo I will use the general solution, and back-port it to the code for the database save procedure of the previous articles.

Schema Structure

In the earlier articles, the base code and unit test packages were created in the HR schema, with utility packages kept in the custom brendan schema. However, it is more common to use separate schemas for code and data, so we will now place all packages and supporting objects in the brendan schema, and create the unit testing views there.

Design Pattern Use Case for Testing Views

Modern Oracle SQL is very powerful and can apply complex logic within a single statement, reducing the need for more complex procedural code. In order to show how to unit test SQL, we will devise a test view, HR_Test_V, having a range of features that we might want to test in general:

  • Inner joins suppress driving records where there is no joining record
  • Outer joins return driving records where there is no joining record
  • Analytic functions that partition by some key, and return aggregates on the returned record set
  • Functions based on aggregates over records that include those not in the returned record set
  • Constraints based on aggregates over records that include those not in the returned record set
  • Constraints on column values

The view functionality can be described in words as:

  • Selected values
    • Employee name, department name, and salary
    • Manager's name
    • Ratio of employee's salary to the department average (returned employees only)
    • Ratio of employee's salary to the average salary of all employees
  • Constraints
    • Exclude employees in job 'AD_ASST'
    • Exclude employees without a department
    • Do not return any records if the total salary of all employees is below 1600
  • Outer join
    • Include employees both with and without a manager

The view SQL is:

CREATE OR REPLACE VIEW hr_test_view_v AS
WITH all_emps AS (
        SELECT Avg (salary) avg_sal, SUM (salary) sal_tot_g
          FROM employees e
)
SELECT e.last_name, d.department_name, m.last_name manager, e.salary,
       Round (e.salary / Avg (e.salary) OVER (PARTITION BY e.department_id), 2) sal_rat,
       Round (e.salary / a.avg_sal, 2) sal_rat_g
  FROM all_emps a
 CROSS JOIN employees e
  JOIN departments d
    ON d.department_id = e.department_id
  LEFT JOIN employees m
    ON m.employee_id = e.manager_id
 WHERE e.job_id != 'AD_ASST'
   AND a.sal_tot_g >= 1600

Scenarios and Sub-scenarios

Scenario definition
Following our earlier article, we may define a scenario as being the set of all relevant records, both on the database and passed as parameters, to a single program call. Unit testing involves creating one or more scenarios, calling the program (or executing the process) for each scenario, and verifying that the output records are as expected.

Good unit testing is achieved when the scenarios are chosen to validate as wide a range of behaviours as possible. It is not always, or usually, necessary to create a new scenario for each aspect of behaviour to be tested.

Sub-scenario definition
Often, several features can be tested in the same program call by setting up different records in the scenario that will independently test the different features. For example, in our use case above we can create employees with and without a department, and with and without a manager in the same scenario to test the different types of join.

It may be helpful to think of these separate records, or fields within a record, as corresponding to sub-scenarios, and try to construct scenarios as efficiently as possible without making more calls than necessary.

View Unit Test Output

Data setup section

SCENARIO 1: DS-1, testing inner, outer joins, analytic over dep, and global ratios with 1 dep, Employees created in setup: DS-1 - 4 emps, 1 dep (10), emp-3 has no dep, emp-4 has bad job
=========================================================================================================================================================================================

#  Employee id  Department id     Manager  Job id          Salary
-  -----------  -------------  ----------  ----------  ----------
1         1493             10              IT_PROG           1000
2         1494             10        1493  IT_PROG           2000
3         1495                       1493  IT_PROG           3000
4         1496             10        1493  AD_ASST           4000

SCENARIO 2: DS-2, testing same as 1 but with extra emp in another dep, Employees created in setup: DS-2 - As dataset 1 but with extra emp-5, in second dep (20)
===============================================================================================================================================================

#  Employee id  Department id     Manager  Job id          Salary
-  -----------  -------------  ----------  ----------  ----------
1         1497             10              IT_PROG           1000
2         1498             10        1497  IT_PROG           2000
3         1499                       1497  IT_PROG           3000
4         1500             10        1497  AD_ASST           4000
5         1501             20        1497  IT_PROG           5000

SCENARIO 3: DS-2, passing 'WHERE dep=10', Employees created in setup: DS-2 - As dataset 1 but with extra emp-5, in second dep (20)
==================================================================================================================================

#  Employee id  Department id     Manager  Job id          Salary
-  -----------  -------------  ----------  ----------  ----------
1         1502             10              IT_PROG           1000
2         1503             10        1502  IT_PROG           2000
3         1504                       1502  IT_PROG           3000
4         1505             10        1502  AD_ASST           4000
5         1506             20        1502  IT_PROG           5000

SCENARIO 4: DS-3, Salaries total 1500 (< threshold of 1600), Employees created in setup: DS-3 - As dataset 2 but with salaries * 0.1, total below reporting threshold of 1600
=============================================================================================================================================================================

#  Employee id  Department id     Manager  Job id          Salary
-  -----------  -------------  ----------  ----------  ----------
1         1507             10              IT_PROG            100
2         1508             10        1507  IT_PROG            200
3         1509                       1507  IT_PROG            300
4         1510             10        1507  AD_ASST            400
5         1511             20        1507  IT_PROG            500

Notes on data setup section

  • There are three data sets, and four scenarios, each of which references a data set
  • The call to set up the data for a scenario writes out all the data created
  • A header provides a description of the features (or sub-scenarios) in the data set
  • In the output above scenarios 2 and 3 use the same data set, DS-2

Results section

SQL> DECLARE
  2  BEGIN
  3  
  4    Utils.Clear_Log;
  5    UT_Utils.Run_Suite (UT_Utils.c_ut_suite_bren);
  6  
  7  EXCEPTION
  8    WHEN OTHERS THEN
  9      Utils.Write_Other_Error;
 10  END;
 11  /

PL/SQL procedure successfully completed.

SQL> @L_Log_Default

TEXT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

UNIT TEST for UT_Emp_WS.ut_AIP_Save_Emps
========================================

SCENARIO 1: 1 valid record {
============================

    INPUTS
    ======

        GROUP Employee {
        ================

            Name  Email  Job      Salary
            ----  -----  -------  ------
            LN 1  EM 1   IT_PROG    1000

        }
        =

    OUTPUTS
    =======

        GROUP Employee: Actual = 1, Expected = 1 {
        ==========================================

            F?  Employee id  Name  Email  Job      Salary
            --  -----------  ----  -----  -------  ------
                       1513  LN 1  EM 1   IT_PROG    1000

        } 0 failed, of 1: SUCCESS
        =========================

        GROUP Output array: Actual = 1, Expected = 1 {
        ==============================================

            F?  Employee id  Description
            --  -----------  ----------------------------------
                       1513  ONE THOUSAND FIVE HUNDRED THIRTEEN

        } 0 failed, of 1: SUCCESS
        =========================

        GROUP Exception: Actual = 0, Expected = 0: SUCCESS
        ==================================================

} 0 failed, of 3: SUCCESS
=========================

SCENARIO 2: 1 invalid job id {
==============================

    INPUTS
    ======

        GROUP Employee {
        ================

            Name  Email  Job      Salary
            ----  -----  -------  ------
            LN 2  EM 2   NON_JOB    1500

        }
        =

    OUTPUTS
    =======

        GROUP Employee: Actual = 0, Expected = 0: SUCCESS
        =================================================

        GROUP Output array: Actual = 1, Expected = 1 {
        ==============================================

            F?  Employee id  Description
            --  -----------  -------------------------------------------------------------------
                          0  ORA-02291: integrity constraint (.) violated - parent key not found

        } 0 failed, of 1: SUCCESS
        =========================

        GROUP Exception: Actual = 0, Expected = 0: SUCCESS
        ==================================================

} 0 failed, of 3: SUCCESS
=========================

SCENARIO 3: 1 invalid number {
==============================

    INPUTS
    ======

        GROUP Employee {
        ================

            Name  Email  Job      Salary
            ----  -----  -------  ------
            LN 3  EM 3   IT_PROG   2000x

        }
        =

    OUTPUTS
    =======

        GROUP Employee: Actual = 0, Expected = 0: SUCCESS
        =================================================

        GROUP Output array: Actual = 0, Expected = 0: SUCCESS
        =====================================================

        GROUP Exception: Actual = 1, Expected = 1 {
        ===========================================

            F?  Error message
            --  -------------------------------------------------------------------------------
                ORA-06502: PL/SQL: numeric or value error: character to number conversion error

        } 0 failed, of 1: SUCCESS
        =========================

} 0 failed, of 3: SUCCESS
=========================

SCENARIO 4: 2 valid records, 1 invalid job id (2 deliberate errors) {
=====================================================================

    INPUTS
    ======

        GROUP Employee {
        ================

            Name  Email  Job      Salary
            ----  -----  -------  ------
            LN 4  EM 4   IT_PROG    3000
            LN 5  EM 5   NON_JOB    4000
            LN 6  EM 6   IT_PROG    5000

        }
        =

    OUTPUTS
    =======

        GROUP Employee: Actual = 2, Expected = 3 {
        ==========================================

            F?  Employee id  Name  Email  Job      Salary
            --  -----------  ----  -----  -------  ------
            F          1515  LN 4  EM 4   IT_PROG    3000
            >          1515  LN 4  EM 4   IT_PROG    1000
                       1517  LN 6  EM 6   IT_PROG    5000
            F
            >          1517  LN 6  EM 6   IT_PROG    5000

        } 2 failed, of 3: FAILURE
        =========================

        GROUP Output array: Actual = 3, Expected = 3 {
        ==============================================

            F?  Employee id  Description
            --  -----------  -------------------------------------------------------------------
                       1515  ONE THOUSAND FIVE HUNDRED FIFTEEN
                          0  ORA-02291: integrity constraint (.) violated - parent key not found
                       1517  ONE THOUSAND FIVE HUNDRED SEVENTEEN

        } 0 failed, of 3: SUCCESS
        =========================

        GROUP Exception: Actual = 0, Expected = 0: SUCCESS
        ==================================================

} 2 failed, of 7: FAILURE
=========================

TIMING: Actual = 222, Expected <= 2: FAILURE
============================================

SUMMARY for UT_Emp_WS.ut_AIP_Save_Emps
======================================

Scenario                                                 # Failed  # Tests  Status
-------------------------------------------------------  --------  -------  -------
1 valid record                                                  0        3  SUCCESS
1 invalid job id                                                0        3  SUCCESS
1 invalid number                                                0        3  SUCCESS
2 valid records, 1 invalid job id (2 deliberate errors)         2        7  FAILURE
Timing                                                          1        1  FAILURE
-------------------------------------------------------  --------  -------  -------
Total                                                           3       17  FAILURE
-------------------------------------------------------  --------  -------  -------

Timer Set: UT_Emp_WS.ut_AIP_Save_Emps, Constructed at 09 Jul 2016 13:32:40, written at 13:32:42
===============================================================================================
[Timer timed: Elapsed (per call): 0.01 (0.000013), CPU (per call): 0.02 (0.000020), calls: 1000, '***' denotes corrected line below]

Timer       Elapsed         CPU         Calls       Ela/Call       CPU/Call
-------  ----------  ----------  ------------  -------------  -------------
Setup          0.04        0.01             1        0.03700        0.01000
Caller         0.67        0.03             3        0.22200        0.01000
SELECT         0.60        0.00             3        0.19933        0.00000
(Other)        0.41        0.03             1        0.40500        0.03000
-------  ----------  ----------  ------------  -------------  -------------
Total          1.71        0.07             8        0.21325        0.00875
-------  ----------  ----------  ------------  -------------  -------------

UNIT TEST for UT_View_Drivers.ut_HR_Test_View_V
===============================================

Employees created in setup: DS-1 - 4 emps, 1 dep (10), emp-3 has no dep, emp-4 has bad job
==========================================================================================

#  Employee id  Department id     Manager  Job id          Salary
-  -----------  -------------  ----------  ----------  ----------
1         1518             10              IT_PROG           1000
2         1519             10        1518  IT_PROG           2000
3         1520                       1518  IT_PROG           3000
4         1521             10        1518  AD_ASST           4000

Employees created in setup: DS-2 - As dataset 1 but with extra emp-5, in second dep (20)
========================================================================================

#  Employee id  Department id     Manager  Job id          Salary
-  -----------  -------------  ----------  ----------  ----------
1         1522             10              IT_PROG           1000
2         1523             10        1522  IT_PROG           2000
3         1524                       1522  IT_PROG           3000
4         1525             10        1522  AD_ASST           4000
5         1526             20        1522  IT_PROG           5000

Employees created in setup: DS-2 - As dataset 1 but with extra emp-5, in second dep (20)
========================================================================================

#  Employee id  Department id     Manager  Job id          Salary
-  -----------  -------------  ----------  ----------  ----------
1         1527             10              IT_PROG           1000
2         1528             10        1527  IT_PROG           2000
3         1529                       1527  IT_PROG           3000
4         1530             10        1527  AD_ASST           4000
5         1531             20        1527  IT_PROG           5000

Employees created in setup: DS-3 - As dataset 2 but with salaries * 0.1, total below reporting threshold of 1600
================================================================================================================

#  Employee id  Department id     Manager  Job id          Salary
-  -----------  -------------  ----------  ----------  ----------
1         1532             10              IT_PROG            100
2         1533             10        1532  IT_PROG            200
3         1534                       1532  IT_PROG            300
4         1535             10        1532  AD_ASST            400
5         1536             20        1532  IT_PROG            500

SCENARIO 1: DS-1, testing inner, outer joins, analytic over dep, and global ratios with 1 dep {
===============================================================================================

    INPUTS
    ======

        GROUP Employee {
        ================

            Employee Id  Last Name  Email  Hire Date  Job      Salary  Manager Id  department Id
            -----------  ---------  -----  ---------  -------  ------  ----------  -------------
                   1518  LN_1       EM_1   09-JUL-16  IT_PROG    1000                         10
                   1519  LN_2       EM_2   09-JUL-16  IT_PROG    2000        1518             10
                   1520  LN_3       EM_3   09-JUL-16  IT_PROG    3000        1518
                   1521  LN_4       EM_4   09-JUL-16  AD_ASST    4000        1518             10

        }
        =

        GROUP Where {
        =============

            Where
            -----


        }
        =

    OUTPUTS
    =======

        GROUP Select results: Actual = 2, Expected = 2 {
        ================================================

            F?  Name  Department      Manager  Salary  Salary Ratio (dep)  Salary Ratio (overall)
            --  ----  --------------  -------  ------  ------------------  ----------------------
                LN_1  Administration             1000                 .67                      .4
                LN_2  Administration  LN_1       2000                1.33                      .8

        } 0 failed, of 2: SUCCESS
        =========================

} 0 failed, of 2: SUCCESS
=========================

SCENARIO 2: DS-2, testing same as 1 but with extra emp in another dep {
=======================================================================

    INPUTS
    ======

        GROUP Employee {
        ================

            Employee Id  Last Name  Email  Hire Date  Job      Salary  Manager Id  department Id
            -----------  ---------  -----  ---------  -------  ------  ----------  -------------
                   1522  LN_1       EM_1   09-JUL-16  IT_PROG    1000                         10
                   1523  LN_2       EM_2   09-JUL-16  IT_PROG    2000        1522             10
                   1524  LN_3       EM_3   09-JUL-16  IT_PROG    3000        1522
                   1525  LN_4       EM_4   09-JUL-16  AD_ASST    4000        1522             10
                   1526  LN_5       EM_5   09-JUL-16  IT_PROG    5000        1522             20

        }
        =

        GROUP Where {
        =============

            Where
            -----


        }
        =

    OUTPUTS
    =======

        GROUP Select results: Actual = 3, Expected = 3 {
        ================================================

            F?  Name  Department      Manager  Salary  Salary Ratio (dep)  Salary Ratio (overall)
            --  ----  --------------  -------  ------  ------------------  ----------------------
                LN_1  Administration             1000                 .67                     .33
                LN_2  Administration  LN_1       2000                1.33                     .67
                LN_5  Marketing       LN_1       5000                   1                    1.67

        } 0 failed, of 3: SUCCESS
        =========================

} 0 failed, of 3: SUCCESS
=========================

SCENARIO 3: DS-2, passing 'WHERE dep=10' {
==========================================

    INPUTS
    ======

        GROUP Employee {
        ================

            Employee Id  Last Name  Email  Hire Date  Job      Salary  Manager Id  department Id
            -----------  ---------  -----  ---------  -------  ------  ----------  -------------
                   1527  LN_1       EM_1   09-JUL-16  IT_PROG    1000                         10
                   1528  LN_2       EM_2   09-JUL-16  IT_PROG    2000        1527             10
                   1529  LN_3       EM_3   09-JUL-16  IT_PROG    3000        1527
                   1530  LN_4       EM_4   09-JUL-16  AD_ASST    4000        1527             10
                   1531  LN_5       EM_5   09-JUL-16  IT_PROG    5000        1527             20

        }
        =

        GROUP Where {
        =============

            Where
            --------------------------------
            department_name='Administration'

        }
        =

    OUTPUTS
    =======

        GROUP Select results: Actual = 2, Expected = 2 {
        ================================================

            F?  Name  Department      Manager  Salary  Salary Ratio (dep)  Salary Ratio (overall)
            --  ----  --------------  -------  ------  ------------------  ----------------------
                LN_1  Administration             1000                 .67                     .33
                LN_2  Administration  LN_1       2000                1.33                     .67

        } 0 failed, of 2: SUCCESS
        =========================

} 0 failed, of 2: SUCCESS
=========================

SCENARIO 4: DS-3, Salaries total 1500 (< threshold of 1600) {
=============================================================

    INPUTS
    ======

        GROUP Employee {
        ================

            Employee Id  Last Name  Email  Hire Date  Job      Salary  Manager Id  department Id
            -----------  ---------  -----  ---------  -------  ------  ----------  -------------
                   1532  LN_1       EM_1   09-JUL-16  IT_PROG     100                         10
                   1533  LN_2       EM_2   09-JUL-16  IT_PROG     200        1532             10
                   1534  LN_3       EM_3   09-JUL-16  IT_PROG     300        1532
                   1535  LN_4       EM_4   09-JUL-16  AD_ASST     400        1532             10
                   1536  LN_5       EM_5   09-JUL-16  IT_PROG     500        1532             20

        }
        =

        GROUP Where {
        =============

            Where
            -----


        }
        =

    OUTPUTS
    =======

        GROUP Select results: Actual = 0, Expected = 0: SUCCESS
        =======================================================

} 0 failed, of 1: SUCCESS
=========================

TIMING: Actual = 48, Expected <= 1: FAILURE
===========================================

SUMMARY for UT_View_Drivers.ut_HR_Test_View_V
=============================================

Scenario                                                                           # Failed  # Tests  Status
---------------------------------------------------------------------------------  --------  -------  -------
DS-1, testing inner, outer joins, analytic over dep, and global ratios with 1 dep         0        2  SUCCESS
DS-2, testing same as 1 but with extra emp in another dep                                 0        3  SUCCESS
DS-2, passing 'WHERE dep=10'                                                              0        2  SUCCESS
DS-3, Salaries total 1500 (< threshold of 1600)                                           0        1  SUCCESS
Timing                                                                                    1        1  FAILURE
---------------------------------------------------------------------------------  --------  -------  -------
Total                                                                                     1        9  FAILURE
---------------------------------------------------------------------------------  --------  -------  -------

Timer Set: UT_View_Drivers.ut_HR_Test_View_V, Constructed at 09 Jul 2016 13:32:42, written at 13:32:42
======================================================================================================
[Timer timed: Elapsed (per call): 0.01 (0.000013), CPU (per call): 0.02 (0.000020), calls: 1000, '***' denotes corrected line below]

Timer       Elapsed         CPU         Calls       Ela/Call       CPU/Call
-------  ----------  ----------  ------------  -------------  -------------
Setup          0.11        0.00             4        0.02675        0.00000
Caller         0.19        0.06             4        0.04750        0.01500
(Other)        0.03        0.03             1        0.02700        0.03000
-------  ----------  ----------  ------------  -------------  -------------
Total          0.32        0.09             9        0.03600        0.01000
-------  ----------  ----------  ------------  -------------  -------------

Suite Summary
=============

Package.Procedure                  Tests  Fails         ELA         CPU
---------------------------------  -----  -----  ----------  ----------
UT_Emp_WS.ut_AIP_Save_Emps            17      3        1.71        0.07
UT_View_Drivers.ut_HR_Test_View_V      9      1        0.32        0.09
---------------------------------  -----  -----  ----------  ----------
Total                                 26      4        2.03        0.16
---------------------------------  -----  -----  ----------  ----------
Others error in (): ORA-20001: Suite BRENDAN returned error status: ORA-06512: at "DP_7.UT_UTILS", line 140
ORA-06512: at "DP_7.UT_UTILS", line 807
ORA-06512: at line 5


476 rows selected.

SQL> SPOOL OFF

Notes on results section

  • Several improvements have been made in output formatting since the previous article
  • In the update of this article on 26 June 2016, the output has been completely re-formatted, including displaying individual fields rather than records as delimited strings, as well as the dependence on utPLSQL being removed
  • There is now only one main results section, with failed lines having the expected value printed directly under the actual
  • In a view test there is only one group, namely the selected data set
  • The output includes testing of the web service procedure discussed in the earlier articles

The second part of the article is here: Design Patterns for Database Unit Testing 4: Views - Code






 

Design Patterns for Database API Testing 1: Web Service Saving 1 - Design

Last October I gave a presentation on database unit testing with utPLSQL,Ā Oracle Unit Testing with utPLSQL. I mentioned design patterns as a way of reducing the effort of building unit tests and outlined some strategies for coding them effectively. In the current set of articles, I develop the ideas further and present a design pattern for web service 'save' procedures by means of a conceptual discussion, together with a working example of base code and unit test code for a procedure to save new employees in Oracle's well-known HR demonstration schema. The working example can be used as a template for real use cases, and I believe can simplify the development process. It can also be used as a basis for comparing other unit testing frameworks, by implementing the same testing in those frameworks.

Design patterns involve abstraction and conceptual separation of general features of a situation from the particular. Therefore we will start with a fairly abstract discussion of unit testing for the database, before proceeding to discuss the use case in question, describe the unit test cases, and show the results. The code isĀ presented in another article,Ā Design Patterns for Database API Testing 1: Web Service Saving 2 - Code. That article lists various extremely common antipatterns in database unit testing, and how they may be avoided. The code itself centralises as much as possible in order to make specific test code as small as possible, and is structured very differently from most unit testing code that I have seen.

Design Patterns for Database API Testing 3: Views - Design presents another design pattern following the same ideas.

Update, 26 June 2016:The code has been re-factored to improve formatting and remove the dependence on utPLSQL, TRAPIT - TRansactional API Testing in Oracle.
Update, 09 July 2016:I have added in scenario-level printing of all inputs.

General Discussion of Database Unit Testing

The underlying functionality for unit testing could be described logically as:

  • Given a list of test inputs, X and a list of expected outputs, E, for function F:
  • For each x in X, with e in E:
    • Apply y = F(x)
    • Assert y = e

As the Functional Programming community knows well, functions having well-defined parameter inputs, returning values as outputs, and with no 'side-effects', are the easiest to test reliably. The difficulty with database unit testing is that most use cases do not fall into that category; instead, database procedures can read from and write to the database as well as using input and output parameters. This means that theoretically the inputs and outputs could include the whole database (at least); furthermore the database is a shared resource, so other parties can alter the data we are dealing with. One important consequence of these facts is that much of the thinking on best practices for unit testing, coming as it does from the non-database world, is not applicable here. So what to do?

Pragmatic testing

To make progress, we note that our purpose with unit testing is not to formally prove that our programs work, but rather includes the following aims:

  • Improve code quality within a Test Driven Development (TDD) approach
  • Provide regression tests to allow safe code-refactoring
  • Detect quickly changes external to the code that cause it to fail, such as reference data changes

That being so, we can note the following guidelines:

  • Testing code is written, as part of TDD, by the developer of the base code, who can identify the relevant database inputs and outputs
  • Some, but not necessarily all, test data may be created in a setup step; static reference data that are required for the code to work usually should not be created in setup
  • Testing code should be instrumented and logged liberally
  • The base code should be timed and a time limit included in the testing; this will help to quickly identify issues such as necessary indexes being dropped
  • Consideration should be given to running the unit test suites in performance and other instances

Design Pattern Use Case for Web Service Save Procedure

  • Purpose of procedure is to save a set of new records to a database table
  • Surrogate primary key is generated
  • Input is an array of objects with the records to be saved
  • Output is an array of objects containing the new primary key plus a description
  • For records failing validation, zero is returned, plus the error message, and the valid records will still be saved

ERD of Input and Output Data Structures in Relation to Scenarios

Unit Testing - ERD

  • In the diagram, a scenario corresponds to a web service call with a set of input records
  • The result of the call can be described as a set of output groups, each having a set of records
  • In our case the output array and the base table form two output groups, with a global group for average call timing
  • The logical diagram in terms of sets of records can be translated into an array structure diagram

Unit Testing - ASD

If we follow a similarly generic approach at the coding level, it becomes very easy to extend a simple example by adding groups, fields and records as necessary.

General Unit Test Design Process

The design process involves two high level steps

  • Identify a good set of scenarios with corresponding input records
  • Identify the expected outputs for each output group identified, for each scenario (there will also be a global group, for timing)

Design Pattern Scenarios

Unit Testing - HR

The procedure inserts records in Oracle's HR employees table, and we identify four test scenarios:

  1. Passing a single valid record
  2. Passing a single invalid record
  3. Trying to pass a record with an invalid type
  4. Passing multiple valid records, and one invalid record

Design Pattern Output Groups

  • Records inserted into table employees
  • Records returned in output parameter array
  • Timing of average call

Unit Test Results Output

The output below is for a failing run, where the time limit is breached, and I also have deliberately entered incorrected expected values for two records, to show the difference in formatting between success and failureĀ output group records. I like to include the output tables on completion of development in my technical design document. The output below is generated by my own library packages, not directly by utPLSQL, whose output format I dislike.

UNIT TEST for UT_Emp_WS.ut_AIP_Save_Emps
========================================

SCENARIO 1: 1 valid record {
============================

    INPUTS
    ======

        GROUP Employee {
        ================

            Name  Email  Job      Salary
            ----  -----  -------  ------
            LN 1  EM 1   IT_PROG    1000

        }
        =

    OUTPUTS
    =======

        GROUP Employee: Actual = 1, Expected = 1 {
        ==========================================

            F?  Employee id  Name  Email  Job      Salary
            --  -----------  ----  -----  -------  ------
                       1513  LN 1  EM 1   IT_PROG    1000

        } 0 failed, of 1: SUCCESS
        =========================

        GROUP Output array: Actual = 1, Expected = 1 {
        ==============================================

            F?  Employee id  Description
            --  -----------  ----------------------------------
                       1513  ONE THOUSAND FIVE HUNDRED THIRTEEN

        } 0 failed, of 1: SUCCESS
        =========================

        GROUP Exception: Actual = 0, Expected = 0: SUCCESS
        ==================================================

} 0 failed, of 3: SUCCESS
=========================

SCENARIO 2: 1 invalid job id {
==============================

    INPUTS
    ======

        GROUP Employee {
        ================

            Name  Email  Job      Salary
            ----  -----  -------  ------
            LN 2  EM 2   NON_JOB    1500

        }
        =

    OUTPUTS
    =======

        GROUP Employee: Actual = 0, Expected = 0: SUCCESS
        =================================================

        GROUP Output array: Actual = 1, Expected = 1 {
        ==============================================

            F?  Employee id  Description
            --  -----------  -------------------------------------------------------------------
                          0  ORA-02291: integrity constraint (.) violated - parent key not found

        } 0 failed, of 1: SUCCESS
        =========================

        GROUP Exception: Actual = 0, Expected = 0: SUCCESS
        ==================================================

} 0 failed, of 3: SUCCESS
=========================

SCENARIO 3: 1 invalid number {
==============================

    INPUTS
    ======

        GROUP Employee {
        ================

            Name  Email  Job      Salary
            ----  -----  -------  ------
            LN 3  EM 3   IT_PROG   2000x

        }
        =

    OUTPUTS
    =======

        GROUP Employee: Actual = 0, Expected = 0: SUCCESS
        =================================================

        GROUP Output array: Actual = 0, Expected = 0: SUCCESS
        =====================================================

        GROUP Exception: Actual = 1, Expected = 1 {
        ===========================================

            F?  Error message
            --  -------------------------------------------------------------------------------
                ORA-06502: PL/SQL: numeric or value error: character to number conversion error

        } 0 failed, of 1: SUCCESS
        =========================

} 0 failed, of 3: SUCCESS
=========================

SCENARIO 4: 2 valid records, 1 invalid job id (2 deliberate errors) {
=====================================================================

    INPUTS
    ======

        GROUP Employee {
        ================

            Name  Email  Job      Salary
            ----  -----  -------  ------
            LN 4  EM 4   IT_PROG    3000
            LN 5  EM 5   NON_JOB    4000
            LN 6  EM 6   IT_PROG    5000

        }
        =

    OUTPUTS
    =======

        GROUP Employee: Actual = 2, Expected = 3 {
        ==========================================

            F?  Employee id  Name  Email  Job      Salary
            --  -----------  ----  -----  -------  ------
            F          1515  LN 4  EM 4   IT_PROG    3000
            >          1515  LN 4  EM 4   IT_PROG    1000
                       1517  LN 6  EM 6   IT_PROG    5000
            F
            >          1517  LN 6  EM 6   IT_PROG    5000

        } 2 failed, of 3: FAILURE
        =========================

        GROUP Output array: Actual = 3, Expected = 3 {
        ==============================================

            F?  Employee id  Description
            --  -----------  -------------------------------------------------------------------
                       1515  ONE THOUSAND FIVE HUNDRED FIFTEEN
                          0  ORA-02291: integrity constraint (.) violated - parent key not found
                       1517  ONE THOUSAND FIVE HUNDRED SEVENTEEN

        } 0 failed, of 3: SUCCESS
        =========================

        GROUP Exception: Actual = 0, Expected = 0: SUCCESS
        ==================================================

} 2 failed, of 7: FAILURE
=========================

TIMING: Actual = 222, Expected <= 2: FAILURE
============================================

SUMMARY for UT_Emp_WS.ut_AIP_Save_Emps
======================================

Scenario                                                 # Failed  # Tests  Status
-------------------------------------------------------  --------  -------  -------
1 valid record                                                  0        3  SUCCESS
1 invalid job id                                                0        3  SUCCESS
1 invalid number                                                0        3  SUCCESS
2 valid records, 1 invalid job id (2 deliberate errors)         2        7  FAILURE
Timing                                                          1        1  FAILURE
-------------------------------------------------------  --------  -------  -------
Total                                                           3       17  FAILURE
-------------------------------------------------------  --------  -------  -------

Timer Set: UT_Emp_WS.ut_AIP_Save_Emps, Constructed at 09 Jul 2016 13:32:40, written at 13:32:42
===============================================================================================
[Timer timed: Elapsed (per call): 0.01 (0.000013), CPU (per call): 0.02 (0.000020), calls: 1000, '***' denotes corrected line below]

Timer       Elapsed         CPU         Calls       Ela/Call       CPU/Call
-------  ----------  ----------  ------------  -------------  -------------
Setup          0.04        0.01             1        0.03700        0.01000
Caller         0.67        0.03             3        0.22200        0.01000
SELECT         0.60        0.00             3        0.19933        0.00000
(Other)        0.41        0.03             1        0.40500        0.03000
-------  ----------  ----------  ------------  -------------  -------------
Total          1.71        0.07             8        0.21325        0.00875
-------  ----------  ----------  ------------  -------------  -------------

Notes on output

  • In the event of the suite failing, as here, the utility code ensures that an Oracle error is generated. This can then be trapped by a calling Unix script from a scheduled Jenkins job to send out emails

Conclusions

  • A design pattern has been presented for database web service save procedures, with scenarios and output results
  • The implementation (presented in the part 2 article) was against an Oracle database publicly available demonstration schema, and used Brendan's database unit testing framework
  • The main ideas could be applied with any database technology and any testing framework
  • It is suggested that any proposed alternative unit testing framework be compared by implementing this design pattern, or similar
  • Further design patterns will be presented in future articles, including for testing of views (Design Patterns for Database Unit Testing 3: Views - Design)