Oracle PL/SQL API Demos Github Module

Posted on November 13, 2019 by BrendanP

This post is essentially the readme for my Github module, Oracle PL/SQL API Demos

This is a Github module demonstrating instrumentation and logging, code timing and unit testing of Oracle PL/SQL APIs.

PL/SQL procedures were written against Oracle’s HR demo schema to represent the different kinds of API across two axes: Setter/Getter and Real Time/Batch.

Mode          | Setter Example (S)          | Getter Example (G)
--------------|-----------------------------|----------------------------------
Real Time (R) | Web service saving          | Web service getting by ref cursor
Batch (B)     | Batch loading of flat files | View

The PL/SQL procedures and view were written originally to demonstrate unit testing, and are as follows:

RS: Emp_WS.Save_Emps – Save a list of new employees to database, returning list of ids with Julian dates; logging errors to err$ table
RG: Emp_WS.Get_Dept_Emps – For given department id, return department and employee details including salary ratios, excluding employees with job ‘AD_ASST’, and returning none if global salary total < 1600, via ref cursor
BS: Emp_Batch.Load_Emps – Load new/updated employees from file via external table
BG: hr_test_view_v – View returning department and employee details including salary ratios, excluding employees with job ‘AD_ASST’, and returning none if global salary total < 1600

Each of these is unit tested, as described below, and in addition there is a driver script, api_driver.sql, that calls each of them and lists the results of logging and code timing.

I presented on Writing Clean Code in PL/SQL and SQL at the Ireland Oracle User Group Conference on 4 April 2019 in Dublin. The modules demonstrated here are written in the style recommended in the presentation where, in particular:

‘functional’ code is preferred
object-oriented code is used only where necessary, using a package record array approach, rather than type bodies
record types, defaults and overloading used extensively to provide clean API interfaces

Screen Recordings on this Module

I initially made a series of screen recordings that are available at the links below, and later condensed each recording to a length that would upload directly to Twitter, i.e. less than 140 seconds. You can find the Twitter thread here. Both sets of recordings are also available in the recordings subfolder of the repository. The links below are to the initial, longer set of recordings.

1 Overview (6 recordings – 48m)

2 Prerequisite Tools (1 recording – 3m)

2.1 Prerequisite tools (3m)

3 Installation (3 recordings – 15m)

4 Running the scripts (4 recordings – 30m)

Unit Testing

The PL/SQL APIs are tested using the Math Function Unit Testing design pattern, with test results in HTML and text format included. The design pattern is based on the idea that all API testing programs can follow a universal design pattern, using the concept of a ‘pure’ function as a wrapper to manage the ‘impurity’ inherent in database APIs. I explained the concepts involved in a presentation at the Ireland Oracle User Group Conference in March 2018:

The Database API Viewed As A Mathematical Function: Insights into Testing

In this data-driven design pattern a driver program reads a set of scenarios from a JSON file, and loops over the scenarios calling the wrapper function with the scenario as input and obtaining the results as the return value. Utility functions from the Trapit module convert the input JSON into PL/SQL arrays, and, conversely, the output arrays into JSON text that is written to an output JSON file. This latter file contains all the input values and output values (expected and actual), as well as metadata describing the input and output groups. A separate nodejs module can be run to process the output files and create HTML files showing the results: Each unit test (say `pkg.prc`) has its own root page `pkg.prc.html` with links to a page for each scenario, located within a subfolder `pkg.prc`. Here, they have been copied into a subfolder test_output, as follows:

tt_emp_batch.load_emps
tt_emp_ws.get_dept_emps
tt_emp_ws.save_emps
tt_view_drivers.hr_test_view_v

Where the actual output record matches expected, just one is represented, while if the actual differs it is listed below the expected and with background colour red. The employee group in scenario 4 of tt_emp_ws.save_emps has two records deliberately not matching, the first by changing the expected salary and the second by adding a duplicate expected record.

Each of the `pkg.prc` subfolders also includes a JSON Structure Diagram, `pkg.prc.png`, showing the input/output structure of the pure unit test wrapper function. For example:

Running a test causes the actual values to be inserted to the JSON object, which is then formatted as HTML pages:

Here is the output JSON for the 4’th scenario of the corresponding test:

    "2 valid records, 1 invalid job id (2 deliberate errors)":{
       "inp":{
          "Employee":[
             "LN 4|EM 4|IT_PROG|3000",
             "LN 5|EM 5|NON_JOB|4000",
             "LN 6|EM 6|IT_PROG|5000"
          ]
       },
       "out":{
          "Employee":{
             "exp":[
                "3|LN 4|EM 4|IT_PROG|1000",
                "5|LN 6|EM 6|IT_PROG|5000",
                "5|LN 6|EM 6|IT_PROG|5000"
             ],
             "act":[
                "3|LN 4|EM 4|IT_PROG|3000",
                "5|LN 6|EM 6|IT_PROG|5000"
             ]
          },
          "Output array":{
             "exp":[
                "3|LIKE /^[A-Z -]+[A-Z]$/",
                "0|ORA-02291: integrity constraint (.) violated - parent key not found",
                "5|LIKE /^[A-Z -]+[A-Z]$/"
             ],
             "act":[
                "3|ONE THOUSAND NINE HUNDRED NINETY-EIGHT",
                "0|ORA-02291: integrity constraint (.) violated - parent key not found",
                "5|TWO THOUSAND"
             ]
          },
          "Exception":{
             "exp":[
             ],
             "act":[
             ]
          }
       }
    }

Here are images of the unit test summary and 4’th scenario pages for the corresponding test:

Logging and Instrumentation

Program instrumentation means including lines of code to monitor the execution of a program, such as tracing lines covered, numbers of records processed, and timing information. Logging means storing such information, in database tables or elsewhere.

The Log_Set module allows for logging of various data in a lines table linked to a header for a given log, with the logging level configurable at runtime. The module also uses Oracle’s DBMS_Application_Info API to allow for logging in memory only with information accessible via the V$SESSION and V$SESSION_LONGOPS views.

The two web service-type APIs, Emp_WS.Save_Emps and Emp_WS.Get_Dept_Emps, use a configuration that logs only via DBMS_Application_Info, while the batch API, Emp_Batch.Load_Emps, also logs to the tables. The view of course does not do any logging itself but calling programs can log the results of querying it.

The driver script api_driver.sql calls all four of the demo APIs and performs its own logging of the calls and the results returned, including the DBMS_Application_Info on exit. The driver logs using a special DEBUG configuration where the log is constructed implicitly by the first Put, and there is no need to pass a log identifier when putting (so debug lines can be easily added in any called package). At the end of the script queries are run that list the contents of the logs created during the session in creation order, first normal logs, then a listing for error logs (of which one is created by deliberately raising an exception handled in WHEN OTHERS).

Here, for example, is the text logged by the driver script for the first call:

Call Emp_WS.Save_Emps to save a list of employees passed...
===========================================================
DBMS_Application_Info: Module = EMP_WS: Log id 127
...................... Action = Log id 127 closed at 12-Sep-2019 06:20:2
...................... Client Info = Exit: Save_Emps, 2 inserted
Print the records returned...
=============================
1862 - ONE THOUSAND EIGHT HUNDRED SIXTY-TWO
1863 - ONE THOUSAND EIGHT HUNDRED SIXTY-THREE

Code Timing

The code timing module Timer_Set is used by the driver script, api_driver.sql, to time the various calls, and at the end of the main block the results are logged using Log_Set.

The timing results are listed for illustration below:

Timer Set: api_driver, Constructed at 12 Sep 2019 06:20:28, written at 06:20:29
===============================================================================
Timer             Elapsed         CPU       Calls       Ela/Call       CPU/Call
-------------  ----------  ----------  ----------  -------------  -------------
Save_Emps            0.00        0.00           1        0.00100        0.00000
Get_Dept_Emps        0.00        0.00           1        0.00100        0.00000
Write_File           0.00        0.02           1        0.00300        0.02000
Load_Emps            0.22        0.15           1        0.22200        0.15000
Delete_File          0.00        0.00           1        0.00200        0.00000
View_To_List         0.00        0.00           1        0.00200        0.00000
(Other)              0.00        0.00           1        0.00000        0.00000
-------------  ----------  ----------  ----------  -------------  -------------
Total                0.23        0.17           7        0.03300        0.02429
-------------  ----------  ----------  ----------  -------------  -------------
[Timer timed (per call in ms): Elapsed: 0.00794, CPU: 0.00873]

Functional PL/SQL

The recordings 1.5 and 1.6 show examples of the functional style of PL/SQL used in the utility packages demonstrated, and here is a diagram from 1.6 illustrating a design pattern identified in refactoring the main subprogram of the unit test programs.

Installation

Install 1: Install pre-requisite tools

Oracle database with HR demo schema

The database installation requires a minimum Oracle version of 12.2, with Oracle’s HR demo schema installed Oracle Database Software Downloads.

If HR demo schema is not installed, it can be got from here: Oracle Database Sample Schemas.

Github Desktop

In order to clone the code as a git repository you need to have the git application installed. I recommend Github Desktop UI for managing repositories on windows. This depends on the git application, available here: git downloads, but can also be installed from within Github Desktop, according to these instructions:
How to install GitHub Desktop.

nodejs (Javascript backend)

nodejs is needed to run a program that turns the unit test output files into formatted HTML pages. It requires no javascript knowledge to run the program, and nodejs can be installed here.

Install 2: Clone git repository

The following steps will download the repository into a folder, oracle_plsql_api_demos, within your GitHub root folder:

Open Github desktop and click [File/Clone repository…]
Paste into the url field on the URL tab: https://github.com/BrenPatF/oracle_plsql_api_demos.git
Choose local path as folder where you want your GitHub root to be
Click [Clone]

Install 3: Install pre-requisite modules

The demo install depends on the pre-requisite modules Utils, Trapit, Log_Set, and Timer_Set, and `lib` and `app` schemas refer to the schemas in which Utils and examples are installed, respectively.

The pre-requisite modules can be installed by following the instructions for each module at the module root pages listed in the `See also` section below. This allows inclusion of the examples and unit tests for those modules. Alternatively, the next section shows how to install these modules directly without their examples or unit tests here.

[Schema: sys; Folder: install_prereq] Create lib and app schemas and Oracle directory

install_sys.sql creates an Oracle directory, `input_dir`, pointing to ‘c:\input’. Update this if necessary to a folder on the database server with read/write access for the Oracle OS user
Run script from slqplus:

SQL> @install_sys

[Schema: lib; Folder: install_prereq\lib] Create lib components

Run script from slqplus:

SQL> @install_lib_all

[Schema: app; Folder: install_prereq\app] Create app synonyms

Run script from slqplus:

SQL> @c_syns_all

[Folder: (npm root)] Install npm trapit package

The npm trapit package is a nodejs package used to format unit test results as HTML pages.

Open a DOS or Powershell window in the folder where you want to install npm packages, and, with nodejs installed, run:

$ npm install trapit

This should install the trapit nodejs package in a subfolder .\node_modules\trapit

Install 4: Create Oracle PL/SQL API Demos components

[Folder: (root)]

Copy the following files from the root folder to the server folder pointed to by the Oracle directory INPUT_DIR:
- tt_emp_ws.save_emps_inp.json
- tt_emp_ws.get_dept_emps_inp.json
- tt_emp_batch.load_emps_inp.json
- tt_view_drivers.hr_test_view_v_inp.json
There is also a bash script to do this, assuming C:\input as INPUT_DIR:

$ ./cp_json_to_input.sh

[Schema: lib; Folder: lib]

Run script from slqplus:

SQL> @install_jobs app

[Schema: hr; Folder: hr]

Run script from slqplus:

SQL> @install_hr app

[Schema: app; Folder: app]

Run script from slqplus:

SQL> @install_api_demos lib

Running Driver Script and Unit Tests

Running driver script

[Schema: app; Folder: app]

Run script from slqplus:

SQL> @api_driver

The output is in api_driver.log

Running unit tests

[Schema: app; Folder: app]

Run script from slqplus:

SQL> @r_tests

Testing is data-driven from the input JSON objects that are loaded from files into the table tt_units (at install time), and produces JSON output files in the INPUT_DIR folder, that contain arrays of expected and actual records by group and scenario. These files are:

tt_emp_batch.load_emps_out.json
tt_emp_ws.get_dept_emps_out.json
tt_emp_ws.save_emps_out.json
tt_view_drivers.hr_test_view_v_out.json

The output files are processed by a nodejs program that has to be installed separately, from the `npm` nodejs repository, as described in the Installation section above. The nodejs program produces listings of the results in HTML and/or text format, and result files are included in the subfolders below test_output. To run the processor (in Windows), open a DOS or Powershell window in the trapit package folder after placing the output JSON files in the subfolder ./examples/externals and run:

$ node ./examples/externals/test-externals

Operating System/Oracle Versions

Windows

Tested on Windows 10, should be OS-independent

Oracle

Tested on Oracle Database Version 19.3.0.0.0 (minimum required: 12.2)

License

MIT

Design Patterns for Database API Testing 4: REF Cursor Getter

Posted on October 1, 2016 by BrendanP

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, starting from the idea that all database APIs can be considered in terms of the axes:

direction (i.e. getter or setter, noting that setters can also ‘get’)
mode (i.e. real time or batch)

For each cell in the implied matrix, I construct an example API (or view) with specified requirements against Oracle’s HR demo schema, and use this example to construct a testing program with appropriate scenarios as a design pattern. Concepts and common patterns and anti-patterns in automated API testing are discussed throughout, and these are largely independent of testing framework used. However, the examples use my own lightweight independent framework that is designed to help avoid many API testing anti-patterns. The code is available on GitHub here, BrenPatF/trapit_oracle_tester, and includes both framework and design pattern examples.

Behind the four examples, there is an underlying design pattern that involves wrapping the API call in a ‘pure’ procedure, called once per scenario, with the output ‘actuals’ array including everything affected by the API, whether as output parameters, or on database tables, etc. The inputs are also extended from the API parameters to include any other effective inputs. Assertion takes place after all scenarios and is against the extended outputs, with extended inputs also listed. This concept of the ‘pure’ function, central to Functional Programming, has important advantages in automated testing. I explained the concepts involved in a presentation at the Oracle User Group Ireland Conference in March 2018:

The Database API Viewed As A Mathematical Function: Insights into Testing

The previous example is here: Design Patterns for Database API Testing 3: Batch Loading of Flat Files. 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 Utils_TT, 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

ERD

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.

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

Package Structure Diagram

Call Structure Table – TT_Emp_WS.tt_AIP_Get_Dept_Emps

TT_Emp_WS.tt_AIP_Get_Dept_Emps – Emp REF Cursor Test Procedure

Declare section

PROCEDURE tt_AIP_Get_Dept_Emps IS

  c_proc_name           CONSTANT VARCHAR2(61) := 'TT_Emp_WS.tt_AIP_Get_Dept_Emps';
  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_out_group_lis       CONSTANT L1_chr_arr := L1_chr_arr ('Select results');

  c_ln_pre              CONSTANT VARCHAR2(10) := DML_API_TT_HR.c_ln_pre;

  c_dep_lis             CONSTANT L1_chr_arr := L1_chr_arr (c_dep_id_1, c_dep_id_1, c_dep_id_2, NULL, c_dep_id_1);

  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')
                                               ),
                                               L1_chr_arr (
                                       Utils.List_Delim (c_ln_pre || '5',   c_dep_nm_2, c_ln_pre || '1', '5000',  '1',    '1.67')
                                               ),
                                               Utils_TT.c_empty_list,
                                               Utils_TT.c_empty_list
                        );

  c_scenario_ds_lis     CONSTANT L1_num_arr := L1_num_arr (1, 2, 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 (10) - pass dep 10',
                               'DS-2, testing same as 1 but with extra emp in another dep (20) - pass dep 10',
                               'DS-2, as second scenario, but - pass dep 20',
                               'DS-2, as second scenario, but - pass null dep',
                               'DS-3, Salaries total 1500 (< threshold of 1600, so return nothing) - pass dep 10');

  c_inp_group_lis       CONSTANT L1_chr_arr := L1_chr_arr ('Employee', 'Department Parameter');
  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',
                                                                'Updated'),
                                                        L1_chr_arr (
                                                                '*Department Id')
  );
  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();
  l_emp_csr                      SYS_REFCURSOR;

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_Array IS
  BEGIN

    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);
      l_inp_3lis (i)(2) := L1_chr_arr (c_dep_lis(i));

    END LOOP;

  END Setup_Array;

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

  Setup_DB: Create test records for a given scenario for testing AIP_Get_Dept_Emps

  ***************************************************************************************************/
  PROCEDURE Setup_DB (p_call_ind           PLS_INTEGER,   -- index of input dataset
                      x_inp_lis        OUT L1_chr_arr) IS -- input list, employees

    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_TT_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_DB;

Notes on setup section

c_dataset_3lis contains the data for all data sets indexed by (data set, record, field)
Setup_Array is called once to do some setup on the arrays
Setup_DB 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 current version of the utility code, this is not strictly necessary, because all the input data is printed out before the outputs

'Pure' API wrapper procedure

  PROCEDURE Purely_Wrap_API (p_scenario_ds      PLS_INTEGER,   -- index of input dataset
                             p_dep_id           PLS_INTEGER,   -- input department id
                             x_inp_lis      OUT L1_chr_arr,    -- generic inputs list (for scenario)
                             x_act_lis      OUT L1_chr_arr) IS -- generic actual values list (for scenario)
  BEGIN

    Setup_DB (p_scenario_ds, x_inp_lis);
    Timer_Set.Increment_Time (l_timer_set, Utils_TT.c_setup_timer);

    Emp_WS.AIP_Get_Dept_Emps (p_dep_id  => p_dep_id,
                              x_emp_csr => l_emp_csr);
    x_act_lis := Utils_TT.List_or_Empty (Utils_TT.Cursor_to_Array (x_csr => l_emp_csr));
    Timer_Set.Increment_Time (l_timer_set, Utils_TT.c_call_timer);
    ROLLBACK;

  END Purely_Wrap_API;

Notes on 'Pure' API wrapper procedure

Setup_DB is called to create the data set for the scenario
Emp_WS.AIP_Get_Dept_Emps returns the ref cursor
Utils_TT.Cursor_to_Array gets the ref cursor result set into an array (which may be empty)

Main section

BEGIN
--
-- Every testing main section should be similar to this, with array setup, then loop over scenarios
-- making a 'pure'(-ish) call to specific, local Purely_Wrap_API, with single assertion call outside
-- the loop
--
  l_timer_set := Utils_TT.Init (c_proc_name);
  Setup_Array;

  FOR i IN 1..c_exp_2lis.COUNT LOOP

    Purely_Wrap_API (c_scenario_ds_lis(i), c_where_lis(i), l_inp_3lis(i)(1), l_act_2lis(i));

  END LOOP;

  Utils_TT.Is_Deeply (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 tt_AIP_Get_Dept_Emps;

Notes on main section

It's quite short isn't it 🙂
Setup is called to do array setup
Main section loops over the scenarios calling Purely_Wrap_API
Is_Deeply is called to do all the assertions within nested loops, then print the results

Test Output

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         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 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                                                                                                       1        1  FAILURE
----------------------------------------------------------------------------------------------------  --------  -------  -------
Total                                                                                                        1        8  FAILURE
----------------------------------------------------------------------------------------------------  --------  -------  -------

Timer Set: TT_Emp_WS.tt_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
-------  ----------  ----------  ------------  -------------  -------------

TRAPIT – TRansactional API Testing in Oracle

Posted on June 25, 2016 by BrendanP

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:

Mode	Setter	Getter
Real Time	Web Service Saving	Web Service Getting by REF Cursor
Batch	Batch Loading of Flat Files	Views

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.

The framework is based on the idea that all API testing programs can follow a universal design pattern for testing APIs, using the concept of a ‘pure’ function as a wrapper to manage the ‘impurity’ inherent in database APIs. I explained the concepts involved in a presentation at the Oracle User Group Ireland Conference in March 2018:

The Database API Viewed As A Mathematical Function: Insights into Testing

The framework lists all inputs and outputs at scenario level (see box below), meaning that the API test logs 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.

The code is available on GitHub here, and includes both framework and design pattern examples: BrenPatF/trapit_oracle_tester

See also the following articles describing the four design patterns. The log from my demo suite is included below for the first example to illustrate the general format of the log.

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.

A Programmer Writes… (Brendan's Blog)

Tag Archives: design pattern

Oracle PL/SQL API Demos Github Module

Screen Recordings on this Module

1 Overview (6 recordings – 48m)

2 Prerequisite Tools (1 recording – 3m)

3 Installation (3 recordings – 15m)

4 Running the scripts (4 recordings – 30m)

Unit Testing

Logging and Instrumentation

Code Timing

Functional PL/SQL

Installation

Install 1: Install pre-requisite tools

Oracle database with HR demo schema

Github Desktop

nodejs (Javascript backend)

Install 2: Clone git repository

Install 3: Install pre-requisite modules

[Schema: sys; Folder: install_prereq] Create lib and app schemas and Oracle directory

[Schema: lib; Folder: install_prereq\lib] Create lib components

[Schema: app; Folder: install_prereq\app] Create app synonyms

[Folder: (npm root)] Install npm trapit package

Install 4: Create Oracle PL/SQL API Demos components

[Folder: (root)]

[Schema: lib; Folder: lib]

[Schema: hr; Folder: hr]

[Schema: app; Folder: app]

Running Driver Script and Unit Tests

Running driver script

[Schema: app; Folder: app]

Running unit tests

[Schema: app; Folder: app]

Operating System/Oracle Versions

Windows

Oracle

See also

License

Design Patterns for Database API Testing 4: REF Cursor Getter

TRAPIT – TRansactional API Testing in Oracle