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.