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.