TRAPIT – TRansactional API Testing in Oracle

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

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

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

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.






 

Design Patterns for Database API Testing 2: Views 2 – Code

Last October I gave a presentation on database unit testing with utPLSQL, Oracle Unit Testing with utPLSQL. I mentioned design patterns as a way of reducing the effort of building unit tests and outlined some strategies for coding them effectively.

In the current set of articles, I develop the ideas further, starting from the idea that all database APIs can be considered in terms of the axes:

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

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

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

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


In the first part of this two part article,Design Patterns for Database API Testing 2: Views 1 – Design I presented a design pattern for unit testing views, using an example based on Oracle’s HR demo schema, and here I list the code for the main test procedure and a couple of the utility procedures, with notes.

A structure diagram shows how the PL/SQL packages relate to each other, and sections of the code are listed with notes.

Package Structure Diagram

Call Structure Table

TT_View_Drivers.tt_HR_Test_View_V – View Test Procedure

Declare section

PROCEDURE tt_HR_Test_View_V IS

  c_view_name           CONSTANT VARCHAR2(61) := 'HR_Test_View_V';
  c_proc_name           CONSTANT VARCHAR2(61) := 'TT_View_Drivers.tt_' || c_view_name;
  c_dep_id_1            CONSTANT PLS_INTEGER := 10;
  c_dep_id_2            CONSTANT PLS_INTEGER := 20;
  c_dep_nm_1            CONSTANT VARCHAR2(100) := 'Administration';
  c_dep_nm_2            CONSTANT VARCHAR2(100) := 'Marketing';
  c_job_bad             CONSTANT VARCHAR2(100) := 'AD_ASST';
  c_job_good            CONSTANT VARCHAR2(100) := 'IT_PROG';
  c_base_sal            CONSTANT PLS_INTEGER := 1000;

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

  c_sel_lis             CONSTANT L1_chr_arr := L1_chr_arr ('last_name', 'department_name', 'manager', 'salary', 'sal_rat', 'sal_rat_g');
  c_where_lis           CONSTANT L1_chr_arr := L1_chr_arr (NULL, NULL, 'department_name=''Administration''', NULL);

  c_dataset_3lis        CONSTANT L3_chr_arr := L3_chr_arr (
                             L2_chr_arr (L1_chr_arr ('4 emps, 1 dep (10), emp-3 has no dep, emp-4 has bad job'),
--                                         dep           job          salary
                               L1_chr_arr (c_dep_id_1,   c_job_good,  '1000'),
                               L1_chr_arr (c_dep_id_1,   c_job_good,  '2000'),
                               L1_chr_arr (NULL,         c_job_good,  '3000'),
                               L1_chr_arr (c_dep_id_1,   c_job_bad,   '4000')
                                             ),
                             L2_chr_arr (L1_chr_arr ('As dataset 1 but with extra emp-5, in second dep (20)'),
                               L1_chr_arr (c_dep_id_1,   c_job_good,  '1000'),
                               L1_chr_arr (c_dep_id_1,   c_job_good,  '2000'),
                               L1_chr_arr (NULL,         c_job_good,  '3000'),
                               L1_chr_arr (c_dep_id_1,   c_job_bad,   '4000'),
                               L1_chr_arr (c_dep_id_2,   c_job_good,  '5000')
                                             ),
                             L2_chr_arr (L1_chr_arr ('As dataset 2 but with salaries * 0.1, total below reporting threshold of 1600'),
                               L1_chr_arr (c_dep_id_1,   c_job_good,  '100'),
                               L1_chr_arr (c_dep_id_1,   c_job_good,  '200'),
                               L1_chr_arr (NULL,         c_job_good,  '300'),
                               L1_chr_arr (c_dep_id_1,   c_job_bad,   '400'),
                               L1_chr_arr (c_dep_id_2,   c_job_good,  '500')
                                             )
                        );

  c_exp_2lis            CONSTANT L2_chr_arr := L2_chr_arr (
                                               L1_chr_arr (
                                       Utils.List_Delim (c_ln_pre || '1',   c_dep_nm_1, NULL,            '1000', '.67',   '.4'),
                                       Utils.List_Delim (c_ln_pre || '2',   c_dep_nm_1, c_ln_pre || '1', '2000',  '1.33', '.8')
                                               ),
                                               L1_chr_arr (
                                       Utils.List_Delim (c_ln_pre || '1',   c_dep_nm_1, NULL,            '1000', '.67',  '.33'),
                                       Utils.List_Delim (c_ln_pre || '2',   c_dep_nm_1, c_ln_pre || '1', '2000',  '1.33', '.67'),
                                       Utils.List_Delim (c_ln_pre || '5',   c_dep_nm_2, c_ln_pre || '1', '5000',  '1',    '1.67')
                                               ),
                                               L1_chr_arr (
                                       Utils.List_Delim (c_ln_pre || '1',   c_dep_nm_1, NULL,            '1000', '.67',   '.33'),
                                       Utils.List_Delim (c_ln_pre || '2',   c_dep_nm_1, c_ln_pre || '1', '2000',  '1.33', '.67')
                                               ),
                                               tt_Utils.c_empty_list
                        );

  c_scenario_ds_lis     CONSTANT L1_num_arr := L1_num_arr (1, 2, 2, 3);
  c_scenario_lis        CONSTANT L1_chr_arr := L1_chr_arr (
                               'DS-1, testing inner, outer joins, analytic over dep, and global ratios with 1 dep',
                               'DS-2, testing same as 1 but with extra emp in another dep',
                               'DS-2, passing ''WHERE dep=10''',
                               'DS-3, Salaries total 1500 (< threshold of 1600)');

  c_inp_group_lis       CONSTANT L1_chr_arr := L1_chr_arr ('Employee', 'Where');
  c_inp_field_2lis      CONSTANT L2_chr_arr := L2_chr_arr (
                                                        L1_chr_arr (
                                                                '*Employee Id',
                                                                'Last Name',
                                                                'Email',
                                                                'Hire Date',
                                                                'Job',
                                                                '*Salary',
                                                                '*Manager Id',
                                                                '*department Id'),
                                                        L1_chr_arr (
                                                                'Where')
  );
  c_out_field_2lis      CONSTANT L2_chr_arr :=  L2_chr_arr ( L1_chr_arr (
                                'Name',
                                'Department',
                                'Manager',
                                '*Salary',
                                '*Salary Ratio (dep)',
                                '*Salary Ratio (overall)'));

  l_act_2lis                      L2_chr_arr := L2_chr_arr();
  c_ms_limit            CONSTANT PLS_INTEGER := 1;
  l_timer_set                    PLS_INTEGER;
  l_inp_3lis                     L3_chr_arr := L3_chr_arr();

Notes on declare section

  • Data sets, scenarios, expected values etc. are stored in generic arrays, where:
    • L1_chr_arr is type of array of VARCHAR2(4000), same as standard type SYS.ODCIVarchar2List
    • L2_chr_arr is a type of array of L1_chr_arr
    • L3_chr_arr is a type of array of L2_chr_arr

Setup section

  PROCEDURE Setup_Array IS
  BEGIN

    l_act_2lis.EXTEND (c_exp_2lis.COUNT);
    l_inp_3lis.EXTEND (c_exp_2lis.COUNT);

    FOR i IN 1..c_exp_2lis.COUNT LOOP

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

    END LOOP;

  END Setup_Array;

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

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

  ***************************************************************************************************/
  PROCEDURE Setup_DB (p_call_ind           PLS_INTEGER,   -- scenario index
                      x_inp_lis        OUT L1_chr_arr) IS -- input list, first group, employees

    l_emp_id            PLS_INTEGER;
    l_mgr_id            PLS_INTEGER;
    l_len_lis           L1_num_arr := L1_num_arr (1, -11, -13, -10, 10, -10);

  BEGIN

    Utils.Heading ('Employees created in setup: DS-' || p_call_ind || ' - ' || c_dataset_3lis (p_call_ind)(1)(1));
    Utils.Col_Headers (L1_chr_arr ('#', 'Employee id', 'Department id', 'Manager', 'Job id', 'Salary'), l_len_lis);
    x_inp_lis := L1_chr_arr();
    x_inp_lis.EXTEND (c_dataset_3lis (p_call_ind).COUNT - 1);
    FOR i IN 2..c_dataset_3lis (p_call_ind).COUNT LOOP

      l_emp_id := DML_API_TT_HR.Ins_Emp (
                            p_emp_ind  => i - 1,
                            p_dep_id   => c_dataset_3lis (p_call_ind)(i)(1),
                            p_mgr_id   => l_mgr_id,
                            p_job_id   => c_dataset_3lis (p_call_ind)(i)(2),
                            p_salary   => c_dataset_3lis (p_call_ind)(i)(3),
                            x_rec      => x_inp_lis(i - 1));
      Utils.Pr_List_As_Line (L1_chr_arr ((i-1), l_emp_id, Nvl (c_dataset_3lis (p_call_ind)(i)(1), ' '), Nvl (To_Char(l_mgr_id), ' '), c_dataset_3lis (p_call_ind)(i)(2), c_dataset_3lis (p_call_ind)(i)(3)), l_len_lis);
      IF i = 2 THEN
        l_mgr_id := l_emp_id;
      END IF;

    END LOOP;

  END Setup_DB;

Notes on setup section

  • c_dataset_3lis contains the data for all data sets indexed by (data set, record, field)
  • Setup_Array is called once to do some setup on the arrays
  • Setup_DB is called for a single data set at a time in each scenario
  • Description of the data set is contained in the array and printed out
  • Data set is printed out in tabular format. In the most recent version of the utility code, this is not strictly necessary, because all the input data is printed out before the outputs

'Pure' API wrapper procedure

  PROCEDURE Purely_Wrap_API (p_scenario_ds      VARCHAR2,      -- index of input dataset
                             p_where            VARCHAR2,      -- input where clause for
                             x_inp_lis_1    OUT L1_chr_arr,    -- first input group, employees
                             x_act_lis      OUT L1_chr_arr) IS -- generic actual values list (for scenario)
  BEGIN

    Setup_DB (p_scenario_ds, x_inp_lis_1);

    Timer_Set.Increment_Time (l_timer_set, Utils_TT.c_setup_timer);
 
    x_act_lis := Utils_TT.Get_View (
                            p_view_name         => c_view_name,
                            p_sel_field_lis     => c_sel_lis,
                            p_where             => p_where,
                            p_timer_set         => l_timer_set);
    ROLLBACK;

  END Purely_Wrap_API;

Notes on 'Pure' API wrapper procedure

  • Setup_DB is called to create the data set for the scenario
  • Get_View returns the results of the query on the view as 2-level array
  • Get_View rolls back after getting the results, so the inserted test records are removed from the database

Main section

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

  FOR i IN 1..c_exp_2lis.COUNT LOOP

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

  END LOOP;

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

EXCEPTION

  WHEN OTHERS THEN
    Utils.Write_Other_Error;
    RAISE;

END tt_HR_Test_View_V;

Notes on main section

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

Utils_TT - Test Utility Procedures
We will include only one procedure from this package in the body of the article. See gitHub link for the full code.
Is_Deeply - to check results from testing

PROCEDURE Is_Deeply (p_proc_name                 VARCHAR2,      -- calling procedure
                     p_test_lis                  L1_chr_arr,    -- test descriptions
                     p_inp_3lis                  L3_chr_arr,    -- actual result strings
                     p_act_3lis                  L3_chr_arr,    -- actual result strings
                     p_exp_3lis                  L3_chr_arr,    -- expected result strings
                     p_timer_set                 PLS_INTEGER,   -- timer set index
                     p_ms_limit                  PLS_INTEGER,   -- call time limit in ms
                     p_inp_group_lis             L1_chr_arr,    -- input group names
                     p_inp_fields_2lis           L2_chr_arr,    -- input fields descriptions
                     p_out_group_lis             L1_chr_arr,    -- output group names
                     p_fields_2lis               L2_chr_arr) IS -- test fields descriptions

  l_num_fails_sce                L1_num_arr :=  L1_num_arr();
  l_num_tests_sce                L1_num_arr :=  L1_num_arr();
  l_tot_fails                    PLS_INTEGER := 0;
  l_tot_tests                    PLS_INTEGER := 0;

.
.
.
(private procedures - see gitHub project, https://github.com/BrenPatF/trapit_oracle_tester, for full code listings)
.
.
.
BEGIN

  Detail_Section (l_num_fails_sce, l_num_tests_sce);
  Summary_Section (l_num_fails_sce, l_num_tests_sce, l_tot_fails, l_tot_tests);
  Set_Global_Summary (l_tot_fails, l_tot_tests + 1);

END Is_Deeply;

Notes on Is_Deeply

  • This is the base version of Is_Deeply with 3-level arrays of expected and actuals
  • An inner loop asserts actual values (which are records) against expected
  • The final assertion is against average call time
  • It is expected that all assertion within a test procedure will be via a single call to one of the versions of this procedure, making a big reduction in code compared with traditional unit testing approaches
  • After final assertion a call is made to write out all the results, by scenario, with all inputs printed first, followed by actuals (and expected, where they differ); this means that the test outputs now become precise and accurate documents of what the program does

Get_View - run a query dynamically on a view and return result set as array of strings

FUNCTION Get_View (p_view_name         VARCHAR2,               -- name of view
                   p_sel_field_lis     L1_chr_arr,             -- list of fields to select
                   p_where             VARCHAR2 DEFAULT NULL,  -- optional where clause
                   p_timer_set         PLS_INTEGER)            -- timer set handle
                   RETURN              L1_chr_arr IS           -- list of delimited result records

  l_cur            SYS_REFCURSOR;
  l_sql_txt        VARCHAR2(32767) := 'SELECT Utils.List_Delim (L1_chr_arr (';
  l_result_lis     L1_chr_arr;
  l_len            PLS_INTEGER;

BEGIN

  FOR i IN 1..p_sel_field_lis.COUNT LOOP

    l_sql_txt := l_sql_txt || p_sel_field_lis(i) || ',';

  END LOOP;

  l_sql_txt := RTrim (l_sql_txt, ',') || ')) FROM ' || p_view_name || ' WHERE ' || Nvl (p_where, '1=1 ') || 'ORDER BY 1';

  OPEN l_cur FOR l_sql_txt;

  FETCH l_cur BULK COLLECT -- ut, small result set, hence no need for limit clause
   INTO l_result_lis;

  CLOSE l_cur;

  Timer_Set.Increment_Time (p_timer_set, Utils_TT.c_call_timer);
  ROLLBACK;
  RETURN tt_Utils.List_or_Empty (l_result_lis);

END Get_View;

Notes on Get_View

  • A query string is constructed from the input list of fields and optional where clause
  • The fields are concatenated with delimiters and returned into an array of strings
  • A rollback occurs to remove any test data created, so as not to interfere with any subsequent call
  • If no data was returned from the query, we return a default 1-record listing containing the string 'EMPTY'

Package block section

BEGIN

  DBMS_Application_Info.Set_Client_Info (client_info => 'TT');
  Utils.c_session_id_if_TT := SYS_Context ('userenv', 'sessionid');

END Utils_TT;

Notes on package block section

  • client_info is set to 'TT', meaning the session operates in test mode
  • The session id is stored in a package variable
  • This id is referenced in the testing views and in insertion of test records with utid column

Conclusions






 

Design Patterns for Database API Testing 2: Views 1 – Design

Last October I gave a presentation on database unit testing with utPLSQL, Oracle Unit Testing with utPLSQL. I mentioned design patterns as a way of reducing the effort of building unit tests and outlined some strategies for coding them effectively.

In the current set of articles, I develop the ideas further, starting from the idea that all database APIs can be considered in terms of the axes:

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

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

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

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


In this 2-part article, I present a design pattern for testing views. I start by discussing when and how to test views. Unlike in the design paatern of the first article in the series, Design Patterns for Database API Testing 1: Web Service Saving – Design, test data has to be created during testing of views, and a very general approach to creating and selecting the test data is proposed. The use case for the design pattern is described, and scenarios and sub-scenarios are defined conceptually. Finally, the output from the testing is presented, with notes.

The second post provides some code extracts, with notes: Design Patterns for Database API Testing 2: Views 2 – Code.

When to Test Views

Views can be simple or complex, or, as I categorise them in Brendan’s 2-Page Oracle Programming Standards, thin or thick, where thick views include table joins and thinviews don’t. Thin views do not normally require testing while it may or may not be appropriate to test thick views.

As explained in the second part of the first article mentioned above, method-based testing is a bad idea, and occurs when the test suite is based on testing all the methods in a package, rather than units of (external) behaviour (often corresponding to procedures prefixed AIP in a common naming convention). Similarly, we can consider views in the same way as methods and ask whether they represent testable units of behaviour or are merely internal code structures, which should not normally have individual automated tests for the reasons given there.

Good examples of views that should be tested would be those that form the basis of complex data extraction to file, by ETL tools such as Informatica, or those that form the basis of reporting tools such as Business Objects. In fact, it is very good practice to place SQL for these tools into views precisely so that they can be tested.

How to Test Views Using a PL/SQL Testing Framework

In order to leverage a PL/SQL API testing framework to also test views, the API test package procedures call a library procedure passing the name of the relevant view: The library procedure returns the result of querying the view as an array of delimited strings, and the API test procedures then compare the results against their own expected results.

Each API test procedure will have its own setup local procedure to create test data, and we need to discuss the issue of distinguishing test data from pre-existing data.

Test Data

In the earlier article on database save procedures, we did not create any test data within the testing code itself, but the base procedure did create data, and those were queried back for assertion. In order to select only the data created by the procedure call a prefix was used in one of the string fields which was assumed not to exist already. This is a workable approach in some cases, but may not be possible in general. Let us consider the different types of database data that may affect our testing:

  • Data created by the base code being tested
  • Data created by test code to be read by the base code
  • Data not created by test code to be read by base code

In order to verify that the program calls are giving results as expected, the test code needs to know all the data that influence the results, not necessarily just directly created data. Our view testing use case described below has an example where the results depend on an aggregate of all the records on the database. This is a problem when we have a shared database, where we cannot freeze the data at the time of test development. In order to handle this problem, we propose to borrow a technique used in Oracle’s ebusinees applications.

Partitioning Views with System Contexts
In Oracle ebusiness’s multi-org implementations, transactions are partitioned by a numeric identifier for the organization owning the transaction. This org_id value is stored in a column in the base table on transaction creation. Within the application code the base table is not queried directly, but through a view that restricts records returned to those for the organization corresponding to the current role of the application user, which is stored in the userenv system context (this is true up to release 11.5, but the mechanism changed in release 12.1).

See SYS_CONTEXT for information on the system context database feature, and Oracle E-Business Suite Multiple Organizations Implementation Guide (12.1) for release 12.1 multi-org implementation in Oracle ebusiness.

Partitioning Views for Testing
We propose to use views in a similar way to the multi-org views, to restrict records to those created in the testing session, by means of a ttid column on the base table that will hold the session id. The new optional column is added to those tables where this approach is required, and view are created on the tables. Our testing utility package Utils_TT sets a context variable to the value ‘TT’ to signify testing mode, and the session id is set to a package variable in the general utilities package Utils.

Any base code that inserts data into the tables has to check for test mode, and if set, put the session id into the ttid field, and if not, leave it blank. The views use the following clause:

 WHERE (ttid = SYS_Context ('userenv', 'sessionid') OR 
        Substr (Nvl (SYS_Context ('userenv', 'client_info'), 'XX'), 1, 2) != 'TT')

Both test code and base code now query the views instead of the base tables. As the base code to write to the tables has to account for the new column, it is necessary for the column to be added in all instances including production. If this seems a little drastic, consider the importance that you attach to testing, and bear in mind that the earlier, less general, approaches may suffice in many cases. In these design pattern demos I use the general solution.

Schema Structure

In the earlier articles, the base code and test packages were created in the HR schema, with utility packages kept in the custom brendan schema. However, it is more common to use separate schemas for code and data, so we will now place all packages and supporting objects in the brendan schema, and create the testing views there.

Design Pattern Use Case for Testing Views

Modern Oracle SQL is very powerful and can apply complex logic within a single statement, reducing the need for more complex procedural code. In order to show how to test SQL, we will devise a test view, HR_Test_V, having a range of features that we might want to test in general:

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

The view functionality can be described in words as:

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

The view SQL is:

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

Scenarios and Sub-scenarios

Scenario definition
Following our earlier article, we may define a scenario as being the set of all relevant records, both on the database and passed as parameters, to a single program call. API or view testing involves creating one or more scenarios, calling the program (or executing the process) for each scenario, and verifying that the output records are as expected.

Good testing is achieved when the scenarios are chosen to validate as wide a range of behaviours as possible. It is not always, or usually, necessary to create a new scenario for each aspect of behaviour to be tested.

Sub-scenario definition
Often, several features can be tested in the same program call by setting up different records in the scenario that will independently test the different features. For example, in our use case above we can create employees with and without a department, and with and without a manager in the same scenario to test the different types of join.

It may be helpful to think of these separate records, or fields within a record, as corresponding to sub-scenarios, and try to construct scenarios as efficiently as possible without making more calls than necessary.

View Test Output

Data setup section

SCENARIO 1: DS-1, testing inner, outer joins, analytic over dep, and global ratios with 1 dep, Employees created in setup: DS-1 - 4 emps, 1 dep (10), emp-3 has no dep, emp-4 has bad job
=========================================================================================================================================================================================

#  Employee id  Department id     Manager  Job id          Salary
-  -----------  -------------  ----------  ----------  ----------
1         1493             10              IT_PROG           1000
2         1494             10        1493  IT_PROG           2000
3         1495                       1493  IT_PROG           3000
4         1496             10        1493  AD_ASST           4000

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

#  Employee id  Department id     Manager  Job id          Salary
-  -----------  -------------  ----------  ----------  ----------
1         1497             10              IT_PROG           1000
2         1498             10        1497  IT_PROG           2000
3         1499                       1497  IT_PROG           3000
4         1500             10        1497  AD_ASST           4000
5         1501             20        1497  IT_PROG           5000

SCENARIO 3: DS-2, passing 'WHERE dep=10', Employees created in setup: DS-2 - As dataset 1 but with extra emp-5, in second dep (20)
==================================================================================================================================

#  Employee id  Department id     Manager  Job id          Salary
-  -----------  -------------  ----------  ----------  ----------
1         1502             10              IT_PROG           1000
2         1503             10        1502  IT_PROG           2000
3         1504                       1502  IT_PROG           3000
4         1505             10        1502  AD_ASST           4000
5         1506             20        1502  IT_PROG           5000

SCENARIO 4: DS-3, Salaries total 1500 (< threshold of 1600), Employees created in setup: DS-3 - As dataset 2 but with salaries * 0.1, total below reporting threshold of 1600
=============================================================================================================================================================================

#  Employee id  Department id     Manager  Job id          Salary
-  -----------  -------------  ----------  ----------  ----------
1         1507             10              IT_PROG            100
2         1508             10        1507  IT_PROG            200
3         1509                       1507  IT_PROG            300
4         1510             10        1507  AD_ASST            400
5         1511             20        1507  IT_PROG            500

Notes on data setup section

  • There are three data sets, and four scenarios, each of which references a data set
  • The call to set up the data for a scenario writes out all the data created
  • A header provides a description of the features (or sub-scenarios) in the data set
  • In the output above scenarios 2 and 3 use the same data set, DS-2

Results section

SQL>BEGIN

  Utils.Clear_Log;
  Utils_TT.Run_Suite (Utils_TT.c_tt_suite_bren);

EXCEPTION
  WHEN OTHERS THEN
    Utils.Write_Other_Error;
END;
/
SQL> @L_Log_Default

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


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         1518             10              IT_PROG           1000
2         1519             10        1518  IT_PROG           2000
3         1520                       1518  IT_PROG           3000
4         1521             10        1518  AD_ASST           4000

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

#  Employee id  Department id     Manager  Job id          Salary
-  -----------  -------------  ----------  ----------  ----------
1         1522             10              IT_PROG           1000
2         1523             10        1522  IT_PROG           2000
3         1524                       1522  IT_PROG           3000
4         1525             10        1522  AD_ASST           4000
5         1526             20        1522  IT_PROG           5000

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

#  Employee id  Department id     Manager  Job id          Salary
-  -----------  -------------  ----------  ----------  ----------
1         1527             10              IT_PROG           1000
2         1528             10        1527  IT_PROG           2000
3         1529                       1527  IT_PROG           3000
4         1530             10        1527  AD_ASST           4000
5         1531             20        1527  IT_PROG           5000

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

#  Employee id  Department id     Manager  Job id          Salary
-  -----------  -------------  ----------  ----------  ----------
1         1532             10              IT_PROG            100
2         1533             10        1532  IT_PROG            200
3         1534                       1532  IT_PROG            300
4         1535             10        1532  AD_ASST            400
5         1536             20        1532  IT_PROG            500

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

    INPUTS
    ======

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

            Employee Id  Last Name  Email  Hire Date  Job      Salary  Manager Id  department Id
            -----------  ---------  -----  ---------  -------  ------  ----------  -------------
                   1518  LN_1       EM_1   09-JUL-16  IT_PROG    1000                         10
                   1519  LN_2       EM_2   09-JUL-16  IT_PROG    2000        1518             10
                   1520  LN_3       EM_3   09-JUL-16  IT_PROG    3000        1518
                   1521  LN_4       EM_4   09-JUL-16  AD_ASST    4000        1518             10

        }
        =

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

            Where
            -----


        }
        =

    OUTPUTS
    =======

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

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

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

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

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

    INPUTS
    ======

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

            Employee Id  Last Name  Email  Hire Date  Job      Salary  Manager Id  department Id
            -----------  ---------  -----  ---------  -------  ------  ----------  -------------
                   1522  LN_1       EM_1   09-JUL-16  IT_PROG    1000                         10
                   1523  LN_2       EM_2   09-JUL-16  IT_PROG    2000        1522             10
                   1524  LN_3       EM_3   09-JUL-16  IT_PROG    3000        1522
                   1525  LN_4       EM_4   09-JUL-16  AD_ASST    4000        1522             10
                   1526  LN_5       EM_5   09-JUL-16  IT_PROG    5000        1522             20

        }
        =

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

            Where
            -----


        }
        =

    OUTPUTS
    =======

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

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

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

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

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

    INPUTS
    ======

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

            Employee Id  Last Name  Email  Hire Date  Job      Salary  Manager Id  department Id
            -----------  ---------  -----  ---------  -------  ------  ----------  -------------
                   1527  LN_1       EM_1   09-JUL-16  IT_PROG    1000                         10
                   1528  LN_2       EM_2   09-JUL-16  IT_PROG    2000        1527             10
                   1529  LN_3       EM_3   09-JUL-16  IT_PROG    3000        1527
                   1530  LN_4       EM_4   09-JUL-16  AD_ASST    4000        1527             10
                   1531  LN_5       EM_5   09-JUL-16  IT_PROG    5000        1527             20

        }
        =

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

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

        }
        =

    OUTPUTS
    =======

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

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

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

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

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

    INPUTS
    ======

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

            Employee Id  Last Name  Email  Hire Date  Job      Salary  Manager Id  department Id
            -----------  ---------  -----  ---------  -------  ------  ----------  -------------
                   1532  LN_1       EM_1   09-JUL-16  IT_PROG     100                         10
                   1533  LN_2       EM_2   09-JUL-16  IT_PROG     200        1532             10
                   1534  LN_3       EM_3   09-JUL-16  IT_PROG     300        1532
                   1535  LN_4       EM_4   09-JUL-16  AD_ASST     400        1532             10
                   1536  LN_5       EM_5   09-JUL-16  IT_PROG     500        1532             20

        }
        =

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

            Where
            -----


        }
        =

    OUTPUTS
    =======

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

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

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

SUMMARY for 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)                                           0        1  SUCCESS
Timing                                                                                    1        1  FAILURE
---------------------------------------------------------------------------------  --------  -------  -------
Total                                                                                     1        9  FAILURE
---------------------------------------------------------------------------------  --------  -------  -------

Timer Set: TT_View_Drivers.tt_HR_Test_View_V, Constructed at 09 Jul 2016 13:32:42, written at 13:32:42
======================================================================================================
[Timer timed: Elapsed (per call): 0.01 (0.000013), CPU (per call): 0.02 (0.000020), calls: 1000, '***' denotes corrected line below]

Timer       Elapsed         CPU         Calls       Ela/Call       CPU/Call
-------  ----------  ----------  ------------  -------------  -------------
Setup          0.11        0.00             4        0.02675        0.00000
Caller         0.19        0.06             4        0.04750        0.01500
(Other)        0.03        0.03             1        0.02700        0.03000
-------  ----------  ----------  ------------  -------------  -------------
Total          0.32        0.09             9        0.03600        0.01000
-------  ----------  ----------  ------------  -------------  -------------

Notes on results section

  • In a view test there is only one group, namely the selected data set

The second part of the article is here: Design Patterns for Database Unit Testing 2: Views 2 - Code






 

A Template Script for JDBC Calling of Oracle Procedures with Object Array Parameters

Some time ago I wrote an Oracle database package for a web service. The Java developer for the service told me that it was throwing an error when called from Java, although I had unit tested it from PL/SQL. He gave me a small Java driver script to demonstrate the issue, and this allowed the issue to be quickly identified: As both Java and PL/SQL have boolean data types I had considered that a boolean parameter would make sense to pass a boolean value. However, it turns out that this does not work in JDBC, and so I replaced it with an integer parameter.

It occurred to me then that it would be nice if the database developer was able in general to test JDBC compatibility of his or her procedure as a final step after unit testing. To this end I created a more generic example script based on a simple procedure that I wrote against Oracle’s HR demo schema, the same procedure that I used as an example of a unit testing design pattern Design Patterns for Database Unit Testing 2: Web Service Saving – Code

Update, 4 November 2017: I have made a self-contained project on GitHub with both Java and Oracle code, avoiding dependency on my testing project. JDBC Calling of Oracle Procedures with Object Array Parameters on GitHub. I have also added the Oracle code below.

The code below runs against any Oracle instance in which the standard Oracle HR demo schema has been installed. There is a video demonstration of how to install and run it at the end of this article. The procedure has one input and one output object array parameter, and can easily be extended as desired. It requires one jar file in the classpath, ojdbc6.jar, which is available in Oracle client or database installations, and can be run from an IDE such as Eclipse.

Java Code

package jdbcdemo;
/***************************************************************************************************
Name:        Driver.java
Description: This is a Java driver script for Brendan's HR demo web service procedure. It is
             designed to serve as a template for other web service procedures to allow a database
             developer to do a JDBC integration test easily, and can also be used as a starting point
             for Java development.

             The template procedure takes an input array of objects and has an output array of 
             objects. It is easy to update for any named object and array types, procedure and
             Oracle connection. Any other signature types would need additional changes.

	     See 'A Template Script for JDBC Calling of Oracle Procedures with Object Array Parameters'
             
A Template Script for JDBC Calling of Oracle Procedures with Object Array Parameters
Modification History Who When Which What -------------------- ----------- ----- ------------------------------------------------------------- Brendan Furey 14-May-2016 1.0 Created Brendan Furey 04-Nov-2017 1.1 Put into new GitHub project along with Oracle code ***************************************************************************************************/ import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Array; import java.sql.Struct; import oracle.jdbc.OracleTypes; import oracle.jdbc.OracleCallableStatement; import oracle.jdbc.OracleConnection; public class Driver { // Change section 1/2: Replace these constants with your own values private static final String DB_CONNECTION = "jdbc:oracle:thin:hr/hr@localhost:1521/orclpdb"; private static final String TY_IN_OBJ = "TY_EMP_IN_OBJ"; private static final String TY_IN_ARR = "TY_EMP_IN_ARR"; private static final String TY_OUT_ARR = "TY_EMP_OUT_ARR"; private static final String PROC_NAME = "Emp_WS.AIP_Save_Emps"; private static OracleConnection conn; public static void main(String[] argv) { try { getDBConnection (); prOutArray (callProc (inArray ())); } catch (SQLException e) { System.out.println(e.getMessage()); } } private static Array inArray () throws SQLException { // Change section 2/2: Replace [2] with number of test records, and the arrays with their values Struct[] struct = new Struct[2]; struct[0] = conn.createStruct (TY_IN_OBJ, new Object[] {"LN 1", "EM 1", "IT_PROG", 1000}); struct[1] = conn.createStruct (TY_IN_OBJ, new Object[] {"LN 2", "EM 2", "IT_PROG", 2000}); return conn.createARRAY (TY_IN_ARR, struct); } private static Array callProc (Array objArray) throws SQLException { OracleCallableStatement ocs = (OracleCallableStatement) conn.prepareCall ("BEGIN "+PROC_NAME+"(:1, :2); END;"); ocs.setArray (1, objArray); ocs.registerOutParameter (2, OracleTypes.ARRAY, TY_OUT_ARR); ocs.execute (); return ocs.getARRAY (2); } private static void prOutArray (Array arr) throws SQLException { Object[] objArr = (Object[]) arr.getArray(); int j = 0; for (Object rec : objArr) { Object[] objLis = ((Struct)rec).getAttributes (); int i = 0; String recStr = ""; for (Object fld : objLis) { if (i++ > 0) recStr = recStr + '/'; recStr = recStr + fld.toString(); } System.out.println ("Record "+(++j)+": "+recStr); } } private static void getDBConnection () throws SQLException { conn = (OracleConnection) DriverManager.getConnection (DB_CONNECTION); conn.setAutoCommit (false); System.out.println ("Connected..."); } }

Example output

Connected...
Record 1: 239/TWO HUNDRED THIRTY-NINE
Record 2: 240/TWO HUNDRED FORTY

Google Java Style

Oracle Code

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

Author:      Brendan Furey
Description: Script to create objects to demo JDBC procedure calls with object array parameters

         See 'A Template Script for JDBC Calling of Oracle Procedures with Object Array Parameters'
             
A Template Script for JDBC Calling of Oracle Procedures with Object Array Parameters
Modification History Who When Which What -------------------- ----------- ----- ------------------------------------------------------------- Brendan Furey 04-May-2016 1.0 Created Brendan Furey 04-Nov-2017 1.1 Extracted the JDBC demo code from the unit testing project, and put into new GitHub project along with Java code ***************************************************************************************************/ REM Run this script from Oracle's standard HR schema to create objects to demo JDBC procedure calls COLUMN "Database" FORMAT A20 COLUMN "Time" FORMAT A20 COLUMN "Version" FORMAT A30 COLUMN "Session" FORMAT 9999990 COLUMN "OS User" FORMAT A10 COLUMN "Machine" FORMAT A20 SELECT 'Start: ' || dbs.name "Database", To_Char (SYSDATE,'DD-MON-YYYY HH24:MI:SS') "Time", Replace (Substr(ver.banner, 1, Instr(ver.banner, '64')-4), 'Enterprise Edition Release ', '') "Version" FROM v$database dbs, v$version ver WHERE ver.banner LIKE 'Oracle%'; PROMPT Input types creation DROP TYPE ty_emp_in_arr / CREATE OR REPLACE TYPE ty_emp_in_obj AS OBJECT ( last_name VARCHAR2(25), email VARCHAR2(25), job_id VARCHAR2(10), salary NUMBER ) / CREATE TYPE ty_emp_in_arr AS TABLE OF ty_emp_in_obj / PROMPT Output types creation DROP TYPE ty_emp_out_arr / CREATE OR REPLACE TYPE ty_emp_out_obj AS OBJECT ( employee_id NUMBER, description VARCHAR2(500) ) / CREATE TYPE ty_emp_out_arr AS TABLE OF ty_emp_out_obj / CREATE OR REPLACE PACKAGE Emp_WS AS /*************************************************************************************************** Description: HR demo web service code. Procedure saves new employees list and returns primary key plus same in words, or zero plus error message in output list ***************************************************************************************************/ PROCEDURE AIP_Save_Emps (p_ty_emp_in_lis ty_emp_in_arr, x_ty_emp_out_lis OUT ty_emp_out_arr); END Emp_WS; / CREATE OR REPLACE PACKAGE BODY Emp_WS AS PROCEDURE AIP_Save_Emps (p_ty_emp_in_lis ty_emp_in_arr, -- list of employees to insert x_ty_emp_out_lis OUT ty_emp_out_arr) IS -- list of employee results l_ty_emp_out_lis ty_emp_out_arr; bulk_errors EXCEPTION; PRAGMA EXCEPTION_INIT (bulk_errors, -24381); n_err PLS_INTEGER := 0; BEGIN FORALL i IN 1..p_ty_emp_in_lis.COUNT SAVE EXCEPTIONS INSERT INTO employees ( employee_id, last_name, email, hire_date, job_id, salary ) VALUES ( employees_seq.NEXTVAL, p_ty_emp_in_lis(i).last_name, p_ty_emp_in_lis(i).email, SYSDATE, p_ty_emp_in_lis(i).job_id, p_ty_emp_in_lis(i).salary ) RETURNING ty_emp_out_obj (employee_id, To_Char(To_Date(employee_id,'J'),'JSP')) BULK COLLECT INTO x_ty_emp_out_lis; EXCEPTION WHEN bulk_errors THEN l_ty_emp_out_lis := x_ty_emp_out_lis; FOR i IN 1 .. sql%BULK_EXCEPTIONS.COUNT LOOP IF i > x_ty_emp_out_lis.COUNT THEN x_ty_emp_out_lis.Extend; END IF; x_ty_emp_out_lis (SQL%Bulk_Exceptions (i).Error_Index) := ty_emp_out_obj (0, SQLERRM (- (SQL%Bulk_Exceptions (i).Error_Code))); END LOOP; FOR i IN 1..p_ty_emp_in_lis.COUNT LOOP IF i > x_ty_emp_out_lis.COUNT THEN x_ty_emp_out_lis.Extend; END IF; IF x_ty_emp_out_lis(i).employee_id = 0 THEN n_err := n_err + 1; ELSE x_ty_emp_out_lis(i) := l_ty_emp_out_lis(i - n_err); END IF; END LOOP; END AIP_Save_Emps; END Emp_WS; /

Here is a demo of installing and running the code, Oracle and Java:






 

Design Patterns for Database API Testing 1: Web Service Saving 1 – Design

Last October I gave a presentation on database unit testing with utPLSQL, Oracle Unit Testing with utPLSQL. I mentioned design patterns as a way of reducing the effort of building unit tests and outlined some strategies for coding them effectively.

In the current set of articles, I develop the ideas further, starting from the idea that all database APIs can be considered in terms of the axes:

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

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

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

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


In this first example, I present a design pattern for web service ‘save’ procedures by means of a conceptual discussion, together with a working example of base code and test code for a procedure to save new employees in Oracle’s well-known HR demonstration schema.

Design patterns involve abstraction and conceptual separation of general features of a situation from the particular. Therefore we will start with a fairly abstract discussion of automated API testing for the database, before proceeding to discuss the use case in question, describe the test cases, and show the results. The code is presented in another article, Design Patterns for Database API Testing 1: Web Service Saving 2 – Code. That article lists various extremely common antipatterns in database unit testing, and how they may be avoided. The code itself centralises as much as possible in order to make specific test code as small as possible, and is structured very differently from most unit testing code that I have seen.

This is the first in a series of four design patterns, with the second described again in two parts, starting here, Design Patterns for Database API Testing 2: Views 1 – Design. For an overview of the testing framework see TRAPIT – TRansactional API Testing in Oracle.

General Discussion of Database Unit Testing

The underlying functionality for unit testing could be described logically as:

  • Given a list of test inputs, X and a list of expected outputs, E, for function F:
  • For each x in X, with e in E:
    • Apply y = F(x)
    • Assert y = e

As the Functional Programming community knows well, functions having well-defined parameter inputs, returning values as outputs, and with no ‘side-effects’, are the easiest to test reliably. The difficulty with database unit testing is that most use cases do not fall into that category; instead, database procedures can read from and write to the database as well as using input and output parameters. This means that theoretically the inputs and outputs could include the whole database (at least); furthermore the database is a shared resource, so other parties can alter the data we are dealing with. One important consequence of these facts is that much of the thinking on best practices for unit testing, coming as it does from the non-database world, is not applicable here. So what to do?

Pragmatic testing

To make progress, we note that our purpose with unit testing is not to formally prove that our programs work, but rather includes the following aims:

  • Improve code quality within a Test Driven Development (TDD) approach
  • Provide regression tests to allow safe code-refactoring
  • Detect quickly changes external to the code that cause it to fail, such as reference data changes

That being so, we can note the following guidelines:

  • Testing code is written, as part of TDD, by the developer of the base code, who can identify the relevant database inputs and outputs
  • Some, but not necessarily all, test data may be created in a setup step; static reference data that are required for the code to work usually should not be created in setup
  • Testing code should be instrumented and logged liberally
  • The base code should be timed and a time limit included in the testing; this will help to quickly identify issues such as necessary indexes being dropped
  • Consideration should be given to running the unit test suites in performance and other instances

Design Pattern Use Case for Web Service Save Procedure

  • Purpose of procedure is to save a set of new records to a database table
  • Surrogate primary key is generated
  • Input is an array of objects with the records to be saved
  • Output is an array of objects containing the new primary key plus a description
  • For records failing validation, zero is returned, plus the error message, and the valid records will still be saved

ERD of Input and Output Data Structures in Relation to Scenarios

Unit Testing - ERD

  • In the diagram, a scenario corresponds to a web service call with a set of input records
  • The result of the call can be described as a set of output groups, each having a set of records
  • In our case the output array and the base table form two output groups, with a global group for average call timing
  • The logical diagram in terms of sets of records can be translated into an array structure diagram

Unit Testing - ASD

If we follow a similarly generic approach at the coding level, it becomes very easy to extend a simple example by adding groups, fields and records as necessary.

General Unit Test Design Process

The design process involves two high level steps

  • Identify a good set of scenarios with corresponding input records
  • Identify the expected outputs for each output group identified, for each scenario (there will also be a global group, for timing)

Design Pattern Scenarios

Unit Testing - HR

The procedure inserts records in Oracle’s HR employees table, and we identify four test scenarios:

  1. Passing a single valid record
  2. Passing a single invalid record
  3. Trying to pass a record with an invalid type
  4. Passing multiple valid records, and one invalid record

Design Pattern Output Groups

  • Records inserted into table employees
  • Records returned in output parameter array
  • Timing of average call

Test Results Output

The output below is for a failing run, where the time limit is breached, and I also have deliberately entered incorrect expected values for two records, to show the difference in formatting between success and failure output group records. I like to include the output tables on completion of development in my technical design document.

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
            --  -----------  ----  -----  -------  ------
                       1513  LN 1  EM 1   IT_PROG    1000

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

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

            F?  Employee id  Description
            --  -----------  ----------------------------------
                       1513  ONE THOUSAND FIVE HUNDRED THIRTEEN

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

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

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

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

    INPUTS
    ======

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

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

        }
        =

    OUTPUTS
    =======

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

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

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

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

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

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

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

    INPUTS
    ======

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

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

        }
        =

    OUTPUTS
    =======

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

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

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

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

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

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

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

    INPUTS
    ======

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

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

        }
        =

    OUTPUTS
    =======

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

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

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

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

            F?  Employee id  Description
            --  -----------  -------------------------------------------------------------------
                       1515  ONE THOUSAND FIVE HUNDRED FIFTEEN
                          0  ORA-02291: integrity constraint (.) violated - parent key not found
                       1517  ONE THOUSAND FIVE HUNDRED SEVENTEEN

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

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

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

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

SUMMARY for 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                                                          1        1  FAILURE
-------------------------------------------------------  --------  -------  -------
Total                                                           3       17  FAILURE
-------------------------------------------------------  --------  -------  -------

Timer Set: TT_Emp_WS.tt_AIP_Save_Emps, Constructed at 09 Jul 2016 13:32:40, written at 13:32:42
===============================================================================================
[Timer timed: Elapsed (per call): 0.01 (0.000013), CPU (per call): 0.02 (0.000020), calls: 1000, '***' denotes corrected line below]

Timer       Elapsed         CPU         Calls       Ela/Call       CPU/Call
-------  ----------  ----------  ------------  -------------  -------------
Setup          0.04        0.01             1        0.03700        0.01000
Caller         0.67        0.03             3        0.22200        0.01000
SELECT         0.60        0.00             3        0.19933        0.00000
(Other)        0.41        0.03             1        0.40500        0.03000
-------  ----------  ----------  ------------  -------------  -------------
Total          1.71        0.07             8        0.21325        0.00875
-------  ----------  ----------  ------------  -------------  -------------

Notes on output

  • In the event of the suite failing, as here, the utility code ensures that an Oracle error is generated. This can then be trapped by a calling Unix script from a scheduled Jenkins job to send out emails

Conclusions

  • A design pattern has been presented for database web service save procedures, with scenarios and output results
  • The implementation (presented in the part 2 article) was against an Oracle database publicly available demonstration schema, and used Brendan's database API testing framework
  • The main ideas could be applied with any database technology and any testing framework
  • It is suggested that any proposed alternative testing framework can be compared by implementing this design pattern, or similar
  • Three more design patterns are presented in further articles in this series, the second being for testing of views (Design Patterns for Database Unit Testing 2: Views 1 - Design)






 

Design Patterns for Database API Testing 1: Web Service Saving 2 – Code

Last October I gave a presentation on database unit testing with utPLSQL, Oracle Unit Testing with utPLSQL. I mentioned design patterns as a way of reducing the effort of building unit tests and outlined some strategies for coding them effectively.

In the current set of articles, I develop the ideas further, starting from the idea that all database APIs can be considered in terms of the axes:

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

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

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

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


In this first example, I present a design pattern for web service ‘save’ procedures by means of a conceptual discussion, together with a working example of base code and test code for a procedure to save new employees in Oracle’s well-known HR demonstration schema.

In part 1, Design Patterns for Database API Testing 1: Web Service Saving 1 – Design I began with an abstract discussion, and progressed to an example based on Oracle’s HR demo schema. That article presented the test scenarios, logical data structures, and the example testing output. Here, I start by listing a number of extremely prevalent antipattern approaches to database API testing, and how to avoid them.

Following this, I list the code for the base procedure and the test package with notes against each section. There are three utility packages used, and I list the code for one procedure from those, that does all of the assertion and reporting from a single call. After the code listings, I provide some design material on the code packages used.

Design Patterns for Database API Testing 2: Views 1 – Design presents another design pattern following the same ideas.

API Testing Antipatterns

Automated unit testing of database APIs is often considered difficult and time-consuming. Unfortunately I believe it is made much worse by widespread following of antipattern approaches, some of which appear on popular database testing-related sites as examples to follow.

Here are a few antipatterns that I have identified, and which my code attached below avoids.

Test this then test this then…

Antipattern description

This occurs when the unit test code is written as a long sequence of method calls, each followed by assertions, and each usually having hard-coded data values in both method calls and assertions. It is a classic antipattern because it is widespread and developers often follow it thinking they are doing the right thing.

Antipattern consequence

The testing code becomes very long-winded and hard to follow, and tends to result in less rigorous testing.

Pattern alternative

Store all input data in arrays at the start, loop over the input scenarios array, accumulating the outputs in arrays, and loop over the output arrays for the assertions.

Method-based testing

Antipattern description

This occurs when the test suite is based on testing all the methods in a package, rather than units of behaviour, such as a procedure serving a web service.

Antipattern consequence

Ian Cooper explains very well in the video link below the adverse consequences of this antipattern from a Java perspective, but it applies equally to database testing.

  • It results in a great deal more testing code, with a lot of redundancy, which deters people from the whole concept of test-driven development
  • Re-factoring is more difficult because the unit test code tests the implementation
  • Shifting the focus of testing from the behavioural side is also unlikely to improve testing quality

TDD: Where Did It All Go Wrong?

Pattern alternative

Include in your test suite only tests of well-defined units of behaviour such as a web service entry point procedure, ignoring helper methods.

Field-level assertion

Antipattern description

This occurs when the individual fields written to the database or in output arrays have their own assertions.

Antipattern consequence

Real database applications often have tables with large numbers of fields, and the numbers of assertions can consequently become very large.

Pattern alternative

Assert at the record level by concatenating the fields in a record into one string.

Coupled tests

Antipattern description

This occurs when testing one scenario affects another; for example, when all the test data are created at once and not rolled back after each scenario and re-created as needed for the next.

This antipattern is strongly promoted by some popular testing frameworks, where package level setup and teardown are mandatory, at least by default. My own framework deliberately does not support these, preferring the test program to call its own private procedures as necessary at the appropriate level.

Antipattern consequence

The coding becomes more complex as it is necessary to distentangle what results from earlier test scenarios from that of the current scenario.

Pattern alternative

Set up test data at the scenario level, not at the procedure level, and roll it back at the end of the scenario testing.

Opaque output

Antipattern description

This occurs when the output does not show what was tested.

Antipattern consequence

It is harder to review the testing, especially when combined, as it usually is, with the Test this then test this then… antipattern. This results in lower quality.

Pattern alternative

Make the output self-documenting with clear scenario descriptions and listings of entities and fields that are being tested.

Emp_WS.AIP_Save_Emps – Base Procedure

PROCEDURE AIP_Save_Emps (p_emp_in_lis           ty_emp_in_arr,     -- list of employees to insert
                         x_emp_out_lis      OUT ty_emp_out_arr) IS -- list of employee results
 
  l_emp_out_lis        ty_emp_out_arr;
  bulk_errors          EXCEPTION;
  PRAGMA               EXCEPTION_INIT (bulk_errors, -24381);
  n_err PLS_INTEGER := 0;

BEGIN

  FORALL i IN 1..p_emp_in_lis.COUNT
    SAVE EXCEPTIONS
    INSERT INTO employees (
        employee_id,
        last_name,
        email,
        hire_date,
        job_id,
        salary
    ) VALUES (
        employees_seq.NEXTVAL,
        p_emp_in_lis(i).last_name,
        p_emp_in_lis(i).email,
        SYSDATE,
        p_emp_in_lis(i).job_id,
        p_emp_in_lis(i).salary
    )
    RETURNING ty_emp_out_obj (employee_id, To_Char(To_Date(employee_id,'J'),'JSP')) BULK COLLECT INTO x_emp_out_lis;

EXCEPTION
  WHEN bulk_errors THEN

    l_emp_out_lis := x_emp_out_lis;

    FOR i IN 1 .. sql%BULK_EXCEPTIONS.COUNT LOOP
      IF i > x_emp_out_lis.COUNT THEN
        x_emp_out_lis.Extend;
      END IF;
      x_emp_out_lis (SQL%Bulk_Exceptions (i).Error_Index) := ty_emp_out_obj (0, SQLERRM (- (SQL%Bulk_Exceptions (i).Error_Code)));
    END LOOP;

    FOR i IN 1..p_emp_in_lis.COUNT LOOP
      IF i > x_emp_out_lis.COUNT THEN
        x_emp_out_lis.Extend;
      END IF;
      IF x_emp_out_lis(i).employee_id = 0 THEN
        n_err := n_err + 1;
      ELSE
        x_emp_out_lis(i) := l_emp_out_lis(i - n_err);
      END IF;
    END LOOP;

END AIP_Save_Emps;

Notes on base procedure

  • There is a FORALL statement to insert a record into employees for each record in the input list in one batch
  • The RETURNING clause uses a BULK COLLECT to return the new id and its value in words to the output list
  • SAVE EXCEPTIONS means valid records will be inserted, while invalid ones will fall to the EXCEPTION block
  • Invalid records cause a zero and the error message to be interpolated into the output array
  • I tried to follow a similar principle here to that in Connor McDonald’s article, Assume the Best; Plan for the Worst – Oracle Magazine Article

TT_Emp_WS.tt_AIP_Save_Emps – Testing Package Procedure

Declaration section

PROCEDURE tt_AIP_Save_Emps IS

  c_proc_name CONSTANT  VARCHAR2(61) := 'TT_Emp_WS.tt_AIP_Save_Emps';

  c_ln_prefix             CONSTANT VARCHAR2(20) := 'LN ';
  c_em_prefix             CONSTANT VARCHAR2(20) := 'EM ';

  c_ln                    CONSTANT L1_chr_arr := L1_chr_arr (
        c_ln_prefix || '1', c_ln_prefix || '2', c_ln_prefix || '3', c_ln_prefix || '4', c_ln_prefix || '5', c_ln_prefix || '6');
  c_em                    CONSTANT L1_chr_arr := L1_chr_arr (
        c_em_prefix || '1', c_em_prefix || '2', c_em_prefix || '3', c_em_prefix || '4', c_em_prefix || '5', c_em_prefix || '6');
  c_job_id                CONSTANT VARCHAR2(20) := 'IT_PROG';
  c_job_id_invalid        CONSTANT VARCHAR2(20) := 'NON_JOB';

  c_salary                CONSTANT L1_chr_arr := L1_chr_arr ('1000', '1500', '2000x', '3000', '4000', '5000');

  c_params_3lis           CONSTANT L3_chr_arr := L3_chr_arr (
          L2_chr_arr (L1_chr_arr (c_ln (1),    c_em (1),        c_job_id,         c_salary (1))), -- valid
          L2_chr_arr (L1_chr_arr (c_ln (2),    c_em (2),        c_job_id_invalid, c_salary (2))), -- invalid
          L2_chr_arr (L1_chr_arr (c_ln (3),    c_em (3),        c_job_id,         c_salary (3))), -- invalid salary, nan
          L2_chr_arr (L1_chr_arr (c_ln (4),    c_em (4),        c_job_id,         c_salary (4)),  -- valid
                      L1_chr_arr (c_ln (5),    c_em (5),        c_job_id_invalid, c_salary (5)),  -- invalid job id
                      L1_chr_arr (c_ln (6),    c_em (6),        c_job_id,         c_salary (6)))  -- valid
  );
  g_exp_3lis                   L3_chr_arr;

  c_ws_ms_limit          CONSTANT PLS_INTEGER := 2;
  c_scenario_lis         CONSTANT L1_chr_arr := L1_chr_arr (
                               '1 valid record',
                               '1 invalid job id',
                               '1 invalid number',
                               '2 valid records, 1 invalid job id (2 deliberate errors)'
  );
  c_inp_group_lis       CONSTANT L1_chr_arr := L1_chr_arr ('Employee');
  c_inp_field_2lis      CONSTANT L2_chr_arr := L2_chr_arr (
                                                        L1_chr_arr (
                                                                'Name',
                                                                'Email',
                                                                'Job',
                                                                '*Salary')
  );
  c_out_group_lis       CONSTANT L1_chr_arr := L1_chr_arr ('Employee', 'Output array', 'Exception');
  c_out_field_2lis      CONSTANT L2_chr_arr :=  L2_chr_arr (
                                    L1_chr_arr ('*Employee id', 'Name', 'Email', 'Job', '*Salary'),
                                    L1_chr_arr ('*Employee id', 'Description'),
                                    L1_chr_arr ('Error message')
  );
  l_timer_set           PLS_INTEGER;
  l_inp_3lis            L3_chr_arr := L3_chr_arr();

  l_act_3lis         L3_chr_arr := L3_chr_arr();

Notes on declaration section

  • Observe that the declarations of l_inp_3lis and g_exp_3lis as 3-level generic lists map directly to Scenario Inputs and Scenario Outputs in the array structure diagram in Design Patterns for Database API Testing 1: Web Service Saving 1 – Design
  • c_scenario_lis, as a generic list maps directly to Scenario Descriptions
  • Adding or subtracting fields in the input object means simply adding or subtracting fields in the inner level of the list
  • Adding test scenarios (i.e. calls) amounts to adding outer level records in each list
  • Using the program as a template for other unit test programs is therefore very easy
  • All inputs and outputs are printed in the output, making the testing self-documenting

Setup

  PROCEDURE Setup_Array IS
    l_last_seq_val         PLS_INTEGER;
  BEGIN

    SELECT employees_seq.NEXTVAL
      INTO l_last_seq_val
      FROM DUAL;

    g_exp_3lis := L3_chr_arr ( -- each call results in a list of 2 output lists: first is the table records; second is the out array
                        L2_chr_arr (L1_chr_arr (Utils.List_Delim (To_Char(l_last_seq_val+1), c_ln (1), c_em (1), c_job_id, c_salary (1))), -- valid char, num pair
                                    L1_chr_arr (Utils.List_Delim (To_Char(l_last_seq_val+1), To_Char(To_Date(l_last_seq_val+1,'J'),'JSP'))),
                                    Utils_TT.c_empty_list
                        ),
                        L2_chr_arr (Utils_TT.c_empty_list,
                                    L1_chr_arr (Utils.List_Delim (0, 'ORA-02291: integrity constraint (.) violated - parent key not found')),
                                    Utils_TT.c_empty_list
                        ),
                        L2_chr_arr (Utils_TT.c_empty_list,
                                    Utils_TT.c_empty_list,
                                    L1_chr_arr ('ORA-06502: PL/SQL: numeric or value error: character to number conversion error')
                        ),
                        L2_chr_arr (L1_chr_arr (Utils.List_Delim (To_Char(l_last_seq_val+3), c_ln (4), c_em (4), c_job_id, c_salary (1)), -- c_salary (1) should be c_salary (4)
                                                Utils.List_Delim (To_Char(l_last_seq_val+5), c_ln (6), c_em (6), c_job_id, c_salary (6)),
                                                Utils.List_Delim (To_Char(l_last_seq_val+5), c_ln (6), c_em (6), c_job_id, c_salary (6))), -- duplicate record to generate error
                                    L1_chr_arr (Utils.List_Delim (To_Char(l_last_seq_val+3), To_Char(To_Date(l_last_seq_val+3,'J'),'JSP')),
                                                Utils.List_Delim (0, 'ORA-02291: integrity constraint (.) violated - parent key not found'),
                                                Utils.List_Delim (To_Char(l_last_seq_val+5), To_Char(To_Date(l_last_seq_val+5,'J'),'JSP'))),
                                    Utils_TT.c_empty_list
                        )
                     );

    l_act_3lis.EXTEND (c_params_3lis.COUNT);
    l_inp_3lis.EXTEND (c_params_3lis.COUNT);

    FOR i IN 1..c_params_3lis.COUNT LOOP

      l_inp_3lis(i) := L2_chr_arr();
      l_inp_3lis(i).EXTEND(1);
      l_inp_3lis(i)(1) := L1_chr_arr();
      l_inp_3lis(i)(1).EXTEND(c_params_3lis(i).COUNT);
      FOR j IN 1..c_params_3lis(i).COUNT LOOP

        l_inp_3lis(i)(1)(j) := Utils.List_Delim (c_params_3lis(i)(j)(1), c_params_3lis(i)(j)(2), c_params_3lis(i)(j)(3), c_params_3lis(i)(j)(4));

      END LOOP;

    END LOOP;

  END Setup_Array;

Notes on Setup

  • Setup gets a value from the sequence in order to determine what values are expected in the outputs
  • Any concurrent calls could invalidate this expectation, but scheduling the test runs may avoid this; if not, one could change the expectation to a category, of say positive integer in a certain range
  • The example has two output groups, corresponding to a table and an output array
  • Observe how easy it would be to extend to extra tables or arrays, just by adding records in the middle level of the list
  • c_empty_list is a 1-record list containing a string ‘EMPTY’, which is used to simplify assertion of empty lists
  • Setup in this case has no need to create test data, as we have chosen to reference pre-existing departments
  • There is no teardown as the inserts from the procedure call are rolled back after each call
  • The call to Write_Times writes out the report from the package level timer set, including setup timings (if any)

‘Pure’ API wrapper procedure

  PROCEDURE Purely_Wrap_API (p_inp_2lis        L2_chr_arr,       -- input list of lists (record, field)
                             x_act_2lis    OUT L2_chr_arr) IS    -- output list of lists (group, record)

    l_emp_out_lis       emp_out_arr;
    l_tab_lis           L1_chr_arr;
    l_arr_lis           L1_chr_arr;
    l_err_lis           L1_chr_arr;

    -- Do_Save makes the ws call and returns o/p array
    PROCEDURE Do_Save (x_emp_out_lis OUT emp_out_arr) IS
      l_emp_in_lis        emp_in_arr := emp_in_arr();
    BEGIN

      FOR i IN 1..p_inp_2lis.COUNT LOOP
        l_emp_in_lis.EXTEND;
        l_emp_in_lis (l_emp_in_lis.COUNT) := emp_in_rec (p_inp_2lis(i)(1), p_inp_2lis(i)(2), p_inp_2lis(i)(3), p_inp_2lis(i)(4));
      END LOOP;

      Timer_Set.Init_Time (p_timer_set_ind => l_timer_set);
      Emp_WS.AIP_Save_Emps (
                p_emp_in_lis        => l_emp_in_lis,
                x_emp_out_lis       => x_emp_out_lis);
      Timer_Set.Increment_Time (p_timer_set_ind => l_timer_set, p_timer_name => Utils_TT.c_call_timer);

    END Do_Save;

    -- Get_Tab_Lis: gets the database records inserted into a generic list of strings
    PROCEDURE Get_Tab_Lis (x_tab_lis OUT L1_chr_arr) IS
    BEGIN

      SELECT Utils.List_Delim (employee_id, last_name, email, job_id, salary)
        BULK COLLECT INTO x_tab_lis
        FROM employees
       WHERE ttid = Utils.c_session_id_if_TT
       ORDER BY employee_id;
      Timer_Set.Increment_Time (p_timer_set_ind => l_timer_set, p_timer_name => 'SELECT');

    EXCEPTION
      WHEN NO_DATA_FOUND THEN NULL;
    END Get_Tab_Lis;

    -- Get_Arr_Lis converts the ws output array into a generic list of strings
    PROCEDURE Get_Arr_Lis (p_emp_out_lis emp_out_arr, x_arr_lis OUT L1_chr_arr) IS
    BEGIN

      IF p_emp_out_lis IS NOT NULL THEN

        x_arr_lis := L1_chr_arr();
        x_arr_lis.EXTEND (p_emp_out_lis.COUNT);
        FOR i IN 1..p_emp_out_lis.COUNT LOOP

          x_arr_lis (i) := Utils.List_Delim (p_emp_out_lis(i).employee_id, p_emp_out_lis(i).description);

        END LOOP;

      END IF;

    END Get_Arr_Lis;

  BEGIN

    BEGIN

      Do_Save (x_emp_out_lis => l_emp_out_lis);
      Get_Tab_Lis (x_tab_lis => l_tab_lis);
      Get_Arr_Lis (p_emp_out_lis => l_emp_out_lis, x_arr_lis => l_arr_lis);

    EXCEPTION
      WHEN OTHERS THEN
        l_err_lis := L1_chr_arr (SQLERRM);
    END;

    x_act_2lis := L2_chr_arr (Utils_TT.List_or_Empty (l_tab_lis), Utils_TT.List_or_Empty (l_arr_lis), Utils_TT.List_or_Empty (l_err_lis));
    ROLLBACK;

  END Purely_Wrap_API;

Notes on main testing procedure

  • The call converts the generic input data into the objects required by the base procedure, then calls the procedure and sets the resulting records for each output group
  • Purely_Wrap_API has a very simple main section that calls local procedures to split the logic into three sections
  • Its EXCEPTION clause captures the scenario where an invalid type conversion is attempted
  • Its output is a generic 2-level list that is mapped to a record in the outer-level output list
  • Empty lists are converted to c_empty_list, the 1-record list mentioned earlier
  • The procedure ends with a rollback

Main section

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

  FOR i IN 1..c_params_3lis.COUNT LOOP

    Purely_Wrap_API (c_params_3lis(i), l_act_3lis(i));

  END LOOP;

  Utils_TT.Is_Deeply (c_proc_name, c_scenario_lis, l_inp_3lis, l_act_3lis, g_exp_3lis, l_timer_set, c_ws_ms_limit,
                      c_inp_group_lis, c_inp_field_2lis, c_out_group_lis, c_out_field_2lis);

EXCEPTION
  WHEN OTHERS THEN
    Utils.Write_Other_Error;
    RAISE;
END tt_AIP_Save_Emps;

Notes on main section

  • The main section is very simple: a local procedure Purely_Wrap_API is called for each input scenario
  • The call converts the generic input data into the objects required by the base procedure, then calls the procedure and sets the resulting records for each output group
  • Is_Deeply is a generic utility procedure that does all the assertions and calls the results reporting utility
  • This can be used without change for any number of output groups with any numbers of fields, of any types

Utils_TT – API Test Utilities Package
We will include only one procedure from this package in the body of the article. See gitHub link for the full code.
Is_Deeply – to check results from testing

PROCEDURE Is_Deeply (p_proc_name                 VARCHAR2,      -- calling procedure
                     p_test_lis                  L1_chr_arr,    -- test descriptions
                     p_inp_3lis                  L3_chr_arr,    -- actual result strings
                     p_act_3lis                  L3_chr_arr,    -- actual result strings
                     p_exp_3lis                  L3_chr_arr,    -- expected result strings
                     p_timer_set                 PLS_INTEGER,   -- timer set index
                     p_ms_limit                  PLS_INTEGER,   -- call time limit in ms
                     p_inp_group_lis             L1_chr_arr,    -- input group names
                     p_inp_fields_2lis           L2_chr_arr,    -- input fields descriptions
                     p_out_group_lis             L1_chr_arr,    -- output group names
                     p_fields_2lis               L2_chr_arr) IS -- test fields descriptions

  l_num_fails_sce                L1_num_arr :=  L1_num_arr();
  l_num_tests_sce                L1_num_arr :=  L1_num_arr();
  l_tot_fails                    PLS_INTEGER := 0;
  l_tot_tests                    PLS_INTEGER := 0;

.
.
.
(private procedures - see gitHub project, https://github.com/BrenPatF/trapit_oracle_tester, for full code listings)
.
.
.
BEGIN

  Detail_Section (l_num_fails_sce, l_num_tests_sce);
  Summary_Section (l_num_fails_sce, l_num_tests_sce, l_tot_fails, l_tot_tests);
  Set_Global_Summary (l_tot_fails, l_tot_tests + 1);

END Is_Deeply;

Notes on Is_Deeply

  • This is the base version of Is_Deeply with 3-level arrays of expected and actuals
  • An inner loop asserts actual values (which are records) against expected
  • The final assertion is against average call time
  • It is expected that all assertion within a test procedure will be via a single call to one of the versions of this procedure, making a big reduction in code compared with traditional unit testing approaches
  • After final assertion a call is made to write out all the results, by scenario, with all inputs printed first, followed by actuals (and expected, where they differ); this means that the test outputs now become precise and accurate documents of what the program does

Package Usages Diagram

Call Structure Table – TT_Emp_WS.tt_AIP_Save_Emps
Installation Instructions

See TRAPIT – TRansactional API Testing in Oracle for a link to the installation code for both the framework and the demo code on gitHub, and links to related articles.

Exercise
If you are interested in following a similar approach to testing, you might find this exercise an easy way to get going: Revise the base procedure to return error records as a second output array, with the first output returning only valid records. You do not need to define any new types for this. Update the  unit test code for the new signature.

Conclusions






Code Timing and Object Orientation and Zombies

This is a Word document that I wrote initially in November 2010 and uploaded to Scribd, Code Timing and Object Orientation and Zombies, with a big update in September 2012, about code timing in several languages.

I am going to upload a number of articles to this blog shortly that make use of an updated version of the Oracle code-timing package, and I am aware that many work networks disallow Scribd access, so I thought I would put it here first, Code Timing and Object Orientation and Zombies. It’s also embedded below (if that is accessible).

Here is the introduction:

This article proposes an object-oriented design for simple CPU and elapsed timing of computer programs by individual code section or subroutine. The object data structure is first described using a diagram/tabulation approach first used in A Perl Object for Flattened Master-Detail Data in Excel, followed by a section describing method usage, and including a diagram showing a typical call structure.

The object class is then translated from the Ur-language of design into the programming languages of Oracle, Perl and Java (one might say that our class of class is instantiated into specific object classes for each language). In each case the code is listed, an example driving program is briefly described, the run results are listed, and any interesting features are highlighted.

Oracle’s implementation of object-orientation is rather different from other languages, and a section of the article discusses how one can best obtain the advantages of object orientation in Oracle, suggesting that it’s often better to bypass the ‘official’ object structures. Both approaches have been implemented here to help readers judge for themselves.

Finally, some notes are collated on differences between the languages.






 

Recursive SQL for Network Analysis, and Duality

In March 2013 I wrote an article on the use of SQL to group network-structured records into their distinct connected subnetworks, SQL for Network Grouping. I looked at two solution approaches commonly put forward on Oracle forums for these types of problem, using Oracle’s Connect By recursion, and the more recent recursive subquery factoring, and also put forward a new solution of my own using the Model clause. I noted however that SQL solutions are generally very inefficent compared with a good PL/SQL solution, such as I posted here, PL/SQL Pipelined Function for Network Analysis. For the first two methods, I noted:

  1. Non-hierarchical networks have no root nodes, so the traversal needs to be repeated from every node in the network set
  2. Hierarchical queries retrieve all possible routes through a network

I also noted that Connect By is more inefficient than recursive subquery factoring, but did not say why, promising a more detailed explanation at a later date. In this article I illustrate the behaviour of both recursive SQL methods through a series of five elementary networks, followed by a simple combination of the five. I then use the foreign key network from Oracle’s HR demo (v12 version, with OE and PM schemas included) as a final example.

In this article I consider traversal of a single connected network from a given root node (or several if each root node is specified).

It is shown that the behaviour of Connect By can be understood best by considering it to traverse all paths through a network that is dual to the original network.

Dual Networks

Dual network definition

The dual network consists of a set of nodes and links (d-nodes and d-links say) defined thus:

  • the d-nodes correspond to each link in the original network that is adjacent (via a node) to at least one other link, including itself if its start and end nodes are the same
  • the d-links correspond to each pair of adjacent links where the ‘from’ link identifier is alphabetically smaller than that of the ‘to’ link, except for the case of links that are adjacent to themselves where a single d-link has the same ‘from’ and ‘to’ link

Dual network SQL

The d-node identifiers are just the link identifiers, while the d-link identifiers use the adjacency-defining node identifiers with a sequential number (partitioned by node) attached.

WITH dist_links AS (
SELECT	DISTINCT CASE WHEN lin_2.node_fr IN (lin_1.node_fr, lin_1.node_to) THEN lin_2.node_fr ELSE lin_2.node_to END link_node,
        lin_1.id node_fr_d,
	lin_2.id node_to_d
  FROM links lin_1
  JOIN links lin_2
    ON lin_2.node_fr IN (lin_1.node_fr, lin_1.node_to)
    OR lin_2.node_to IN (lin_1.node_fr, lin_1.node_to)
 WHERE lin_2.id >= lin_1.id
   AND (lin_2.id != lin_1.id OR lin_2.node_fr = lin_1.node_to)
)
SELECT Substr (link_node, 1, Length (link_node)-1) || Row_Number () OVER (PARTITION BY link_node
                            ORDER BY node_fr_d, node_to_d) || '-' || Substr (link_node, -1),
       node_fr_d,
       node_to_d
  FROM dist_links

Dual network characteristics

Dual networks defined as above are generally larger than the original networks and are usually more heavily looped, which explains the inferior performance of Connect by compared with recursive subquery factor solutions. The PL/SQL solution mentioned above, while traversing the entire network, does not traverse all possible routes through it and its performance is thus not adversely affected by the degree of looping.

SQL Queries

The recursive SQL queries return all routes through the network from the roots supplied. In my attached script I also have versions that filter out repeated links. The pipelined function query returns a single, exhaustive route through the network, distinguishing a set of tree links from loop-closing links; it also returns all subnetworks without requiring input roots.

Pipelined Function Query (PLF)

See PL/SQL Pipelined Function for Network Analysis for the Pl/SQL function.

SELECT root_node_id             "Network",
       Count (DISTINCT link_id) OVER (PARTITION BY root_node_id) - 1 "#Links",
       Count (DISTINCT node_id) OVER (PARTITION BY root_node_id) "#Nodes",
       LPad (dirn || ' ', 2*node_level, ' ') || node_id || loop_flag "Node",
       link_id || CASE WHEN link_id = 'ROOT' THEN '_' || Substr (root_node_id, -1) END "Link",
       node_level               "Lev"
  FROM TABLE (Net_Pipe.All_Nets)
 ORDER BY line_no

Recursive Subquery Factor Query (RSF)

WITH rsf (node_id, prefix, id, lev) AS (
SELECT node_id, '', 'ROOT_' || Substr (node_id, 4, 1), 0
  FROM nodes_v
 WHERE Substr (node_id, 2, 1) = '1'
 UNION ALL
SELECT CASE WHEN l.node_id_to = r.node_id THEN l.node_id_fr ELSE l.node_id_to END,
       CASE WHEN l.node_id_fr = l.node_id_to THEN '= ' WHEN l.node_id_fr = r.node_id THEN '> ' ELSE '< ' END,
       l.link_id id, lev + 1
  FROM rsf r
  JOIN links_v l
    ON (l.node_id_fr = r.node_id OR l.node_id_to = r.node_id)
   AND l.link_id != Nvl (r.id, '0')
) SEARCH DEPTH FIRST BY node_id SET line_no
CYCLE node_id SET is_cycle TO '*' DEFAULT ' '
SELECT LPad (r.prefix || ' ', 2*r.lev) || r.node_id || is_cycle "Node",
        r.id "Link",
        line_no
  FROM rsf r
 ORDER BY line_no

Connect By Query (CBY)

SELECT node_id_fr || ' > ' || node_id_to  "Nodes",
       LPad (' ', 2 * (LEVEL-1)) || link_id || CASE WHEN CONNECT_BY_ISCYCLE = 1 THEN '*' ELSE ' ' END "Link Path"
  FROM links_v
CONNECT BY NOCYCLE ((node_id_fr = PRIOR node_id_to OR node_id_to = PRIOR node_id_fr OR
                     node_id_fr = PRIOR node_id_fr OR node_id_to = PRIOR node_id_to) /*AND link_id != PRIOR link_id*/)
 START WITH Substr (node_id_fr, 2, 1) = '1' AND Substr (node_id_to, 2, 1) = '2'
 ORDER SIBLINGS BY node_id_to

Five Elementary Networks

Oracle’s two forms of SQL recursion treat cycles differently

Connect By Cycles

The CONNECT_BY_ISCYCLE pseudocolumn returns 1 if the current row has a child which is also its ancestor. Otherwise it returns 0

Connect By queries do not return loop-closing nodes, and the prior node is marked as the cycle node.

Recursive Subquery Factor Cycles

A row is considered to form a cycle if one of its ancestor rows has the same values for the cycle columns.

Recursive Subquery Factor queries do return loop-closing nodes, and these nodes are marked as the cycle nodes.

We will see this differing behaviour clearly in the following examples. We will also see that the Connect By output on the original network has exactly the same structure as recursive subquery factor output on the dual network if the loop-closing rows are disregarded. Cycle nodes on both definitions are marked with a ‘*’ in the outputs below.

Network 1: 3 nodes in line

Dual Network, 1.3 - net-1

Network 2: Simple fork

Dual Network, 1.3 - net-2

Network 3: 2-node loop

Dual Network, 1.3 - net-3

Network 4: 3-node loop

Dual Network, 1.3 - net-4

Network 5: 2 nodes with a self-loop

Dual Network, 1.3 - net-5

Combination of Elementary Networks

Combination Network 6

Dual Network, 1.3 - net-6

This network has 10 links with 3 loops.

Combination Network 6: PLF Output

Node              Link
----------------- ----------
N1-6
> N2-6            L12-6
  = N2-6*         L22-6
  > N3-6          L23-6
    > N4-6        L34-6
      > N6-6      L46-6
        > N4-6*   L64-6
    > N5-6        L35-6
      > N7-6      L57-6
        > N8-6    L78-6
          < N5-6* L58-6

Combination Network 6: RSF Output

Node              Link
----------------- ----------
N1-6
> N2-6            L12-6
 =  N2-6*         L22-6
 >  N3-6          L23-6
   >  N4-6        L34-6
     <  N6-6      L64-6
       <  N4-6*   L46-6
     >  N6-6      L46-6
       >  N4-6*   L64-6
   >  N5-6        L35-6
     >  N7-6      L57-6
       >  N8-6    L78-6
         <  N5-6* L58-6
     >  N8-6      L58-6
       <  N7-6    L78-6
         <  N5-6* L57-6

Combination Network 6: CBY Output

Nodes           Link Path
--------------- --------------------
N1-6 > N2-6     L12-6*
N2-6 > N2-6       L22-6*
N2-6 > N3-6         L23-6*
N3-6 > N4-6           L34-6*
N6-6 > N4-6             L64-6*
N4-6 > N6-6               L46-6*
N3-6 > N5-6             L35-6*
N5-6 > N7-6               L57-6*
N5-6 > N8-6                 L58-6*
N7-6 > N8-6                   L78-6*
N7-6 > N8-6                 L78-6*
N5-6 > N8-6                   L58-6*
N5-6 > N8-6               L58-6*
N5-6 > N7-6                 L57-6*
N7-6 > N8-6                   L78-6*
N7-6 > N8-6                 L78-6*
N5-6 > N7-6                   L57-6*
N4-6 > N6-6             L46-6*
N6-6 > N4-6               L64-6*
N3-6 > N5-6           L35-6*
N3-6 > N4-6             L34-6*
N6-6 > N4-6               L64-6*
N4-6 > N6-6                 L46-6*
N4-6 > N6-6               L46-6*
N6-6 > N4-6                 L64-6*
N5-6 > N7-6             L57-6*
N5-6 > N8-6               L58-6*
N7-6 > N8-6                 L78-6*
N7-6 > N8-6               L78-6*
N5-6 > N8-6                 L58-6*
N5-6 > N8-6             L58-6*
N5-6 > N7-6               L57-6*
N7-6 > N8-6                 L78-6*
N7-6 > N8-6               L78-6*
N5-6 > N7-6                 L57-6*
N2-6 > N3-6       L23-6*
N2-6 > N2-6         L22-6*
N3-6 > N4-6         L34-6*
N6-6 > N4-6           L64-6*
N4-6 > N6-6             L46-6*
N3-6 > N5-6           L35-6*
N5-6 > N7-6             L57-6*
N5-6 > N8-6               L58-6*
N7-6 > N8-6                 L78-6*
N7-6 > N8-6               L78-6*
N5-6 > N8-6                 L58-6*
N5-6 > N8-6             L58-6*
N5-6 > N7-6               L57-6*
N7-6 > N8-6                 L78-6*
N7-6 > N8-6               L78-6*
N5-6 > N7-6                 L57-6*
N4-6 > N6-6           L46-6*
N6-6 > N4-6             L64-6*
N3-6 > N5-6         L35-6*
N3-6 > N4-6           L34-6*
N6-6 > N4-6             L64-6*
N4-6 > N6-6               L46-6*
N4-6 > N6-6             L46-6*
N6-6 > N4-6               L64-6*
N5-6 > N7-6           L57-6*
N5-6 > N8-6             L58-6*
N7-6 > N8-6               L78-6*
N7-6 > N8-6             L78-6*
N5-6 > N8-6               L58-6*
N5-6 > N8-6           L58-6*
N5-6 > N7-6             L57-6*
N7-6 > N8-6               L78-6*
N7-6 > N8-6             L78-6*
N5-6 > N7-6               L57-6*



Dual Combination Network 6

Dual Network, 1.3 - net-6-D

This network has 15 links with 6 loops, whereas the original had 10 links with 3 loops.

Dual Combination Network 6: PLF Output

Node                      Link
------------------------- ------
L12-6
> L22-6                   N2-1-6
  = L22-6*                N2-3-6
  > L23-6                 N2-4-6
    < L12-6*              N2-2-6
    > L34-6               N3-1-6
      > L35-6             N3-3-6
        < L23-6*          N3-2-6
        > L57-6           N5-1-6
          > L58-6         N5-3-6
            < L35-6*      N5-2-6
            > L78-6       N8-1-6
              < L57-6*    N7-1-6
      > L46-6             N4-1-6
        > L64-6           N6-1-6
          < L34-6*        N4-2-6

Dual Combination Network 6: RSF Output

Node                      Link
------------------------- ------
L12-6
> L22-6                   N2-1-6
 =  L22-6*                N2-3-6
 >  L23-6                 N2-4-6
   <  L12-6*              N2-2-6
   >  L34-6               N3-1-6
     >  L35-6             N3-3-6
       <  L23-6*          N3-2-6
       >  L57-6           N5-1-6
         >  L58-6         N5-3-6
           <  L35-6*      N5-2-6
           >  L78-6       N8-1-6
             <  L57-6*    N7-1-6
         >  L78-6         N7-1-6
           <  L58-6       N8-1-6
             <  L35-6*    N5-2-6
             <  L57-6*    N5-3-6
       >  L58-6           N5-2-6
         <  L57-6         N5-3-6
           <  L35-6*      N5-1-6
           >  L78-6       N7-1-6
             <  L58-6*    N8-1-6
         >  L78-6         N8-1-6
           <  L57-6       N7-1-6
             <  L35-6*    N5-1-6
             >  L58-6*    N5-3-6
     >  L46-6             N4-1-6
       >  L64-6           N6-1-6
         <  L34-6*        N4-2-6
     >  L64-6             N4-2-6
       <  L46-6           N6-1-6
         <  L34-6*        N4-1-6
   >  L35-6               N3-2-6
     <  L34-6             N3-3-6
       <  L23-6*          N3-1-6
       >  L46-6           N4-1-6
         >  L64-6         N6-1-6
           <  L34-6*      N4-2-6
       >  L64-6           N4-2-6
         <  L46-6         N6-1-6
           <  L34-6*      N4-1-6
     >  L57-6             N5-1-6
       >  L58-6           N5-3-6
         <  L35-6*        N5-2-6
         >  L78-6         N8-1-6
           <  L57-6*      N7-1-6
       >  L78-6           N7-1-6
         <  L58-6         N8-1-6
           <  L35-6*      N5-2-6
           <  L57-6*      N5-3-6
     >  L58-6             N5-2-6
       <  L57-6           N5-3-6
         <  L35-6*        N5-1-6
         >  L78-6         N7-1-6
           <  L58-6*      N8-1-6
       >  L78-6           N8-1-6
         <  L57-6         N7-1-6
           <  L35-6*      N5-1-6
           >  L58-6*      N5-3-6
> L23-6                   N2-2-6
 <  L22-6                 N2-4-6
   <  L12-6*              N2-1-6
   =  L22-6*              N2-3-6
 >  L34-6                 N3-1-6
   >  L35-6               N3-3-6
     <  L23-6*            N3-2-6
     >  L57-6             N5-1-6
       >  L58-6           N5-3-6
         <  L35-6*        N5-2-6
         >  L78-6         N8-1-6
           <  L57-6*      N7-1-6
       >  L78-6           N7-1-6
         <  L58-6         N8-1-6
           <  L35-6*      N5-2-6
           <  L57-6*      N5-3-6
     >  L58-6             N5-2-6
       <  L57-6           N5-3-6
         <  L35-6*        N5-1-6
         >  L78-6         N7-1-6
           <  L58-6*      N8-1-6
       >  L78-6           N8-1-6
         <  L57-6         N7-1-6
           <  L35-6*      N5-1-6
           >  L58-6*      N5-3-6
   >  L46-6               N4-1-6
     >  L64-6             N6-1-6
       <  L34-6*          N4-2-6
   >  L64-6               N4-2-6
     <  L46-6             N6-1-6
       <  L34-6*          N4-1-6
 >  L35-6                 N3-2-6
   <  L34-6               N3-3-6
     <  L23-6*            N3-1-6
     >  L46-6             N4-1-6
       >  L64-6           N6-1-6
         <  L34-6*        N4-2-6
     >  L64-6             N4-2-6
       <  L46-6           N6-1-6
         <  L34-6*        N4-1-6
   >  L57-6               N5-1-6
     >  L58-6             N5-3-6
       <  L35-6*          N5-2-6
       >  L78-6           N8-1-6
         <  L57-6*        N7-1-6
     >  L78-6             N7-1-6
       <  L58-6           N8-1-6
         <  L35-6*        N5-2-6
         <  L57-6*        N5-3-6
   >  L58-6               N5-2-6
     <  L57-6             N5-3-6
       <  L35-6*          N5-1-6
       >  L78-6           N7-1-6
         <  L58-6*        N8-1-6
     >  L78-6             N8-1-6
       <  L57-6           N7-1-6
         <  L35-6*        N5-1-6
         >  L58-6*        N5-3-6


Combination Network 6: CBY Original with RSF Dual Output

In the output below I deleted all the loop rows from the RSF output for the dual network and placed the result beside the output for CBY for the original network, using a column-wise copy and paste. It's easy to see then their equivalent structure. Both have 69 rows.

Network 6: CBY                         Dual Network 6: RSF with loop rows deleted
==============                         ==========================================
Nodes           Link Path              Node                      Link
--------------- --------------------   ------------------------- ------
N1-6 > N2-6     L12-6*                 L12-6
N2-6 > N2-6       L22-6*	       > L22-6                   N2-1-6
N2-6 > N3-6         L23-6*	        >  L23-6                 N2-4-6
N3-6 > N4-6           L34-6*	          >  L34-6               N3-1-6
N6-6 > N4-6             L64-6*	            >  L35-6             N3-3-6
N4-6 > N6-6               L46-6*              >  L57-6           N5-1-6
N3-6 > N5-6             L35-6*	                >  L58-6         N5-3-6
N5-6 > N7-6               L57-6*                  >  L78-6       N8-1-6
N5-6 > N8-6                 L58-6*              >  L78-6         N7-1-6
N7-6 > N8-6                   L78-6*              <  L58-6       N8-1-6
N7-6 > N8-6                 L78-6*            >  L58-6           N5-2-6
N5-6 > N8-6                   L58-6*            <  L57-6         N5-3-6
N5-6 > N8-6               L58-6*                  >  L78-6       N7-1-6
N5-6 > N7-6                 L57-6*              >  L78-6         N8-1-6
N7-6 > N8-6                   L78-6*              <  L57-6       N7-1-6
N7-6 > N8-6                 L78-6*          >  L46-6             N4-1-6
N5-6 > N7-6                   L57-6*          >  L64-6           N6-1-6
N4-6 > N6-6             L46-6*	            >  L64-6             N4-2-6
N6-6 > N4-6               L64-6*              <  L46-6           N6-1-6
N3-6 > N5-6           L35-6*	          >  L35-6               N3-2-6
N3-6 > N4-6             L34-6*	            <  L34-6             N3-3-6
N6-6 > N4-6               L64-6*              >  L46-6           N4-1-6
N4-6 > N6-6                 L46-6*              >  L64-6         N6-1-6
N4-6 > N6-6               L46-6*              >  L64-6           N4-2-6
N6-6 > N4-6                 L64-6*              <  L46-6         N6-1-6
N5-6 > N7-6             L57-6*	            >  L57-6             N5-1-6
N5-6 > N8-6               L58-6*              >  L58-6           N5-3-6
N7-6 > N8-6                 L78-6*              >  L78-6         N8-1-6
N7-6 > N8-6               L78-6*              >  L78-6           N7-1-6
N5-6 > N8-6                 L58-6*              <  L58-6         N8-1-6
N5-6 > N8-6             L58-6*	            >  L58-6             N5-2-6
N5-6 > N7-6               L57-6*              <  L57-6           N5-3-6
N7-6 > N8-6                 L78-6*              >  L78-6         N7-1-6
N7-6 > N8-6               L78-6*              >  L78-6           N8-1-6
N5-6 > N7-6                 L57-6*              <  L57-6         N7-1-6
N2-6 > N3-6       L23-6*	       > L23-6                   N2-2-6
N2-6 > N2-6         L22-6*	        <  L22-6                 N2-4-6
N3-6 > N4-6         L34-6*	        >  L34-6                 N3-1-6
N6-6 > N4-6           L64-6*	          >  L35-6               N3-3-6
N4-6 > N6-6             L46-6*	            >  L57-6             N5-1-6
N3-6 > N5-6           L35-6*	              >  L58-6           N5-3-6
N5-6 > N7-6             L57-6*	                >  L78-6         N8-1-6
N5-6 > N8-6               L58-6*              >  L78-6           N7-1-6
N7-6 > N8-6                 L78-6*              <  L58-6         N8-1-6
N7-6 > N8-6               L78-6*            >  L58-6             N5-2-6
N5-6 > N8-6                 L58-6*            <  L57-6           N5-3-6
N5-6 > N8-6             L58-6*	                >  L78-6         N7-1-6
N5-6 > N7-6               L57-6*              >  L78-6           N8-1-6
N7-6 > N8-6                 L78-6*              <  L57-6         N7-1-6
N7-6 > N8-6               L78-6*          >  L46-6               N4-1-6
N5-6 > N7-6                 L57-6*          >  L64-6             N6-1-6
N4-6 > N6-6           L46-6*	          >  L64-6               N4-2-6
N6-6 > N4-6             L64-6*	            <  L46-6             N6-1-6
N3-6 > N5-6         L35-6*	        >  L35-6                 N3-2-6
N3-6 > N4-6           L34-6*	          <  L34-6               N3-3-6
N6-6 > N4-6             L64-6*	            >  L46-6             N4-1-6
N4-6 > N6-6               L46-6*              >  L64-6           N6-1-6
N4-6 > N6-6             L46-6*	            >  L64-6             N4-2-6
N6-6 > N4-6               L64-6*              <  L46-6           N6-1-6
N5-6 > N7-6           L57-6*	          >  L57-6               N5-1-6
N5-6 > N8-6             L58-6*	            >  L58-6             N5-3-6
N7-6 > N8-6               L78-6*              >  L78-6           N8-1-6
N7-6 > N8-6             L78-6*	            >  L78-6             N7-1-6
N5-6 > N8-6               L58-6*              <  L58-6           N8-1-6
N5-6 > N8-6           L58-6*	          >  L58-6               N5-2-6
N5-6 > N7-6             L57-6*	            <  L57-6             N5-3-6
N7-6 > N8-6               L78-6*              >  L78-6           N7-1-6
N7-6 > N8-6             L78-6*	            >  L78-6             N8-1-6
N5-6 > N7-6               L57-6*              <  L57-6           N7-1-6


Dual Combination Network 6: CBY Output

34547 rows selected.

[See attached file if interested in detail.]

Oracle's HR/OE/PM Demo Network

Original Demo Network

Dual Network, 1.3 - HR

This network has 21 links with 6 loops.

Original Demo Network: PLF Output

Node                                          Link                                 Lev
--------------------------------------------- ----------------------------------- ----
COUNTRIES|HR                                  ROOT                                   0
< LOCATIONS|HR                                loc_c_id_fk|HR                         1
  < DEPARTMENTS|HR                            dept_loc_fk|HR                         2
    > EMPLOYEES|HR                            dept_mgr_fk|HR                         3
      < CUSTOMERS|OE                          customers_account_manager_fk|OE        4
        < ORDERS|OE                           orders_customer_id_fk|OE               5
          > EMPLOYEES|HR*                     orders_sales_rep_fk|OE                 6
          < ORDER_ITEMS|OE                    order_items_order_id_fk|OE             6
            > PRODUCT_INFORMATION|OE          order_items_product_id_fk|OE           7
              < INVENTORIES|OE                inventories_product_id_fk|OE           8
                > WAREHOUSES|OE               inventories_warehouses_fk|OE           9
                  > LOCATIONS|HR*             warehouses_location_fk|OE             10
              < ONLINE_MEDIA|PM               loc_c_id_fk|PM                         8
              < PRINT_MEDIA|PM                printmedia_fk|PM                       8
              < PRODUCT_DESCRIPTIONS|OE       pd_product_id_fk|OE                    8
      > DEPARTMENTS|HR*                       emp_dept_fk|HR                         4
      = EMPLOYEES|HR*                         emp_manager_fk|HR                      4
      > JOBS|HR                               emp_job_fk|HR                          4
        < JOB_HISTORY|HR                      jhist_job_fk|HR                        5
          > DEPARTMENTS|HR*                   jhist_dept_fk|HR                       6
          > EMPLOYEES|HR*                     jhist_emp_fk|HR                        6
> REGIONS|HR                                  countr_reg_fk|HR                       1

22 rows selected.

Elapsed: 00:00:00.15



Original Demo Network: RSF Output

Node                                          Link
--------------------------------------------- -----------------------------------
COUNTRIES|HR
< LOCATIONS|HR                                loc_c_id_fk|HR
  < DEPARTMENTS|HR                            dept_loc_fk|HR
    < EMPLOYEES|HR                            emp_dept_fk|HR
      < CUSTOMERS|OE                          customers_account_manager_fk|OE
        < ORDERS|OE                           orders_customer_id_fk|OE
          > EMPLOYEES|HR*                     orders_sales_rep_fk|OE
          < ORDER_ITEMS|OE                    order_items_order_id_fk|OE
            > PRODUCT_INFORMATION|OE          order_items_product_id_fk|OE
              < INVENTORIES|OE                inventories_product_id_fk|OE
                > WAREHOUSES|OE               inventories_warehouses_fk|OE
                  > LOCATIONS|HR*             warehouses_location_fk|OE
              < ONLINE_MEDIA|PM               loc_c_id_fk|PM
              < PRINT_MEDIA|PM                printmedia_fk|PM
              < PRODUCT_DESCRIPTIONS|OE       pd_product_id_fk|OE
      < DEPARTMENTS|HR*                       dept_mgr_fk|HR
      = EMPLOYEES|HR*                         emp_manager_fk|HR
      > JOBS|HR                               emp_job_fk|HR
        < JOB_HISTORY|HR                      jhist_job_fk|HR
          > DEPARTMENTS|HR*                   jhist_dept_fk|HR
          > EMPLOYEES|HR*                     jhist_emp_fk|HR
      < JOB_HISTORY|HR                        jhist_emp_fk|HR
        > DEPARTMENTS|HR*                     jhist_dept_fk|HR
        > JOBS|HR                             jhist_job_fk|HR
          < EMPLOYEES|HR*                     emp_job_fk|HR
      < ORDERS|OE                             orders_sales_rep_fk|OE
        > CUSTOMERS|OE                        orders_customer_id_fk|OE
          > EMPLOYEES|HR*                     customers_account_manager_fk|OE
        < ORDER_ITEMS|OE                      order_items_order_id_fk|OE
          > PRODUCT_INFORMATION|OE            order_items_product_id_fk|OE
            < INVENTORIES|OE                  inventories_product_id_fk|OE
              > WAREHOUSES|OE                 inventories_warehouses_fk|OE
                > LOCATIONS|HR*               warehouses_location_fk|OE
            < ONLINE_MEDIA|PM                 loc_c_id_fk|PM
            < PRINT_MEDIA|PM                  printmedia_fk|PM
            < PRODUCT_DESCRIPTIONS|OE         pd_product_id_fk|OE
    > EMPLOYEES|HR                            dept_mgr_fk|HR
      < CUSTOMERS|OE                          customers_account_manager_fk|OE
        < ORDERS|OE                           orders_customer_id_fk|OE
          > EMPLOYEES|HR*                     orders_sales_rep_fk|OE
          < ORDER_ITEMS|OE                    order_items_order_id_fk|OE
            > PRODUCT_INFORMATION|OE          order_items_product_id_fk|OE
              < INVENTORIES|OE                inventories_product_id_fk|OE
                > WAREHOUSES|OE               inventories_warehouses_fk|OE
                  > LOCATIONS|HR*             warehouses_location_fk|OE
              < ONLINE_MEDIA|PM               loc_c_id_fk|PM
              < PRINT_MEDIA|PM                printmedia_fk|PM
              < PRODUCT_DESCRIPTIONS|OE       pd_product_id_fk|OE
      > DEPARTMENTS|HR*                       emp_dept_fk|HR
      = EMPLOYEES|HR*                         emp_manager_fk|HR
      > JOBS|HR                               emp_job_fk|HR
        < JOB_HISTORY|HR                      jhist_job_fk|HR
          > DEPARTMENTS|HR*                   jhist_dept_fk|HR
          > EMPLOYEES|HR*                     jhist_emp_fk|HR
      < JOB_HISTORY|HR                        jhist_emp_fk|HR
        > DEPARTMENTS|HR*                     jhist_dept_fk|HR
        > JOBS|HR                             jhist_job_fk|HR
          < EMPLOYEES|HR*                     emp_job_fk|HR
      < ORDERS|OE                             orders_sales_rep_fk|OE
        > CUSTOMERS|OE                        orders_customer_id_fk|OE
          > EMPLOYEES|HR*                     customers_account_manager_fk|OE
        < ORDER_ITEMS|OE                      order_items_order_id_fk|OE
          > PRODUCT_INFORMATION|OE            order_items_product_id_fk|OE
            < INVENTORIES|OE                  inventories_product_id_fk|OE
              > WAREHOUSES|OE                 inventories_warehouses_fk|OE
                > LOCATIONS|HR*               warehouses_location_fk|OE
            < ONLINE_MEDIA|PM                 loc_c_id_fk|PM
            < PRINT_MEDIA|PM                  printmedia_fk|PM
            < PRODUCT_DESCRIPTIONS|OE         pd_product_id_fk|OE
    < JOB_HISTORY|HR                          jhist_dept_fk|HR
      > EMPLOYEES|HR                          jhist_emp_fk|HR
        < CUSTOMERS|OE                        customers_account_manager_fk|OE
          < ORDERS|OE                         orders_customer_id_fk|OE
            > EMPLOYEES|HR*                   orders_sales_rep_fk|OE
            < ORDER_ITEMS|OE                  order_items_order_id_fk|OE
              > PRODUCT_INFORMATION|OE        order_items_product_id_fk|OE
                < INVENTORIES|OE              inventories_product_id_fk|OE
                  > WAREHOUSES|OE             inventories_warehouses_fk|OE
                    > LOCATIONS|HR*           warehouses_location_fk|OE
                < ONLINE_MEDIA|PM             loc_c_id_fk|PM
                < PRINT_MEDIA|PM              printmedia_fk|PM
                < PRODUCT_DESCRIPTIONS|OE     pd_product_id_fk|OE
        < DEPARTMENTS|HR*                     dept_mgr_fk|HR
        > DEPARTMENTS|HR*                     emp_dept_fk|HR
        = EMPLOYEES|HR*                       emp_manager_fk|HR
        > JOBS|HR                             emp_job_fk|HR
          < JOB_HISTORY|HR*                   jhist_job_fk|HR
        < ORDERS|OE                           orders_sales_rep_fk|OE
          > CUSTOMERS|OE                      orders_customer_id_fk|OE
            > EMPLOYEES|HR*                   customers_account_manager_fk|OE
          < ORDER_ITEMS|OE                    order_items_order_id_fk|OE
            > PRODUCT_INFORMATION|OE          order_items_product_id_fk|OE
              < INVENTORIES|OE                inventories_product_id_fk|OE
                > WAREHOUSES|OE               inventories_warehouses_fk|OE
                  > LOCATIONS|HR*             warehouses_location_fk|OE
              < ONLINE_MEDIA|PM               loc_c_id_fk|PM
              < PRINT_MEDIA|PM                printmedia_fk|PM
              < PRODUCT_DESCRIPTIONS|OE       pd_product_id_fk|OE
      > JOBS|HR                               jhist_job_fk|HR
        < EMPLOYEES|HR                        emp_job_fk|HR
          < CUSTOMERS|OE                      customers_account_manager_fk|OE
            < ORDERS|OE                       orders_customer_id_fk|OE
              > EMPLOYEES|HR*                 orders_sales_rep_fk|OE
              < ORDER_ITEMS|OE                order_items_order_id_fk|OE
                > PRODUCT_INFORMATION|OE      order_items_product_id_fk|OE
                  < INVENTORIES|OE            inventories_product_id_fk|OE
                    > WAREHOUSES|OE           inventories_warehouses_fk|OE
                      > LOCATIONS|HR*         warehouses_location_fk|OE
                  < ONLINE_MEDIA|PM           loc_c_id_fk|PM
                  < PRINT_MEDIA|PM            printmedia_fk|PM
                  < PRODUCT_DESCRIPTIONS|OE   pd_product_id_fk|OE
          < DEPARTMENTS|HR*                   dept_mgr_fk|HR
          > DEPARTMENTS|HR*                   emp_dept_fk|HR
          = EMPLOYEES|HR*                     emp_manager_fk|HR
          < JOB_HISTORY|HR*                   jhist_emp_fk|HR
          < ORDERS|OE                         orders_sales_rep_fk|OE
            > CUSTOMERS|OE                    orders_customer_id_fk|OE
              > EMPLOYEES|HR*                 customers_account_manager_fk|OE
            < ORDER_ITEMS|OE                  order_items_order_id_fk|OE
              > PRODUCT_INFORMATION|OE        order_items_product_id_fk|OE
                < INVENTORIES|OE              inventories_product_id_fk|OE
                  > WAREHOUSES|OE             inventories_warehouses_fk|OE
                    > LOCATIONS|HR*           warehouses_location_fk|OE
                < ONLINE_MEDIA|PM             loc_c_id_fk|PM
                < PRINT_MEDIA|PM              printmedia_fk|PM
                < PRODUCT_DESCRIPTIONS|OE     pd_product_id_fk|OE
  < WAREHOUSES|OE                             warehouses_location_fk|OE
    < INVENTORIES|OE                          inventories_warehouses_fk|OE
      > PRODUCT_INFORMATION|OE                inventories_product_id_fk|OE
        < ONLINE_MEDIA|PM                     loc_c_id_fk|PM
        < ORDER_ITEMS|OE                      order_items_product_id_fk|OE
          > ORDERS|OE                         order_items_order_id_fk|OE
            > CUSTOMERS|OE                    orders_customer_id_fk|OE
              > EMPLOYEES|HR                  customers_account_manager_fk|OE
                < DEPARTMENTS|HR              dept_mgr_fk|HR
                  < EMPLOYEES|HR*             emp_dept_fk|HR
                  < JOB_HISTORY|HR            jhist_dept_fk|HR
                    > EMPLOYEES|HR*           jhist_emp_fk|HR
                    > JOBS|HR                 jhist_job_fk|HR
                      < EMPLOYEES|HR*         emp_job_fk|HR
                  > LOCATIONS|HR*             dept_loc_fk|HR
                > DEPARTMENTS|HR              emp_dept_fk|HR
                  > EMPLOYEES|HR*             dept_mgr_fk|HR
                  < JOB_HISTORY|HR            jhist_dept_fk|HR
                    > EMPLOYEES|HR*           jhist_emp_fk|HR
                    > JOBS|HR                 jhist_job_fk|HR
                      < EMPLOYEES|HR*         emp_job_fk|HR
                  > LOCATIONS|HR*             dept_loc_fk|HR
                = EMPLOYEES|HR*               emp_manager_fk|HR
                > JOBS|HR                     emp_job_fk|HR
                  < JOB_HISTORY|HR            jhist_job_fk|HR
                    > DEPARTMENTS|HR          jhist_dept_fk|HR
                      < EMPLOYEES|HR*         emp_dept_fk|HR
                      > EMPLOYEES|HR*         dept_mgr_fk|HR
                      > LOCATIONS|HR*         dept_loc_fk|HR
                    > EMPLOYEES|HR*           jhist_emp_fk|HR
                < JOB_HISTORY|HR              jhist_emp_fk|HR
                  > DEPARTMENTS|HR            jhist_dept_fk|HR
                    < EMPLOYEES|HR*           emp_dept_fk|HR
                    > EMPLOYEES|HR*           dept_mgr_fk|HR
                    > LOCATIONS|HR*           dept_loc_fk|HR
                  > JOBS|HR                   jhist_job_fk|HR
                    < EMPLOYEES|HR*           emp_job_fk|HR
                < ORDERS|OE*                  orders_sales_rep_fk|OE
            > EMPLOYEES|HR                    orders_sales_rep_fk|OE
              < CUSTOMERS|OE                  customers_account_manager_fk|OE
                < ORDERS|OE*                  orders_customer_id_fk|OE
              < DEPARTMENTS|HR                dept_mgr_fk|HR
                < EMPLOYEES|HR*               emp_dept_fk|HR
                < JOB_HISTORY|HR              jhist_dept_fk|HR
                  > EMPLOYEES|HR*             jhist_emp_fk|HR
                  > JOBS|HR                   jhist_job_fk|HR
                    < EMPLOYEES|HR*           emp_job_fk|HR
                > LOCATIONS|HR*               dept_loc_fk|HR
              > DEPARTMENTS|HR                emp_dept_fk|HR
                > EMPLOYEES|HR*               dept_mgr_fk|HR
                < JOB_HISTORY|HR              jhist_dept_fk|HR
                  > EMPLOYEES|HR*             jhist_emp_fk|HR
                  > JOBS|HR                   jhist_job_fk|HR
                    < EMPLOYEES|HR*           emp_job_fk|HR
                > LOCATIONS|HR*               dept_loc_fk|HR
              = EMPLOYEES|HR*                 emp_manager_fk|HR
              > JOBS|HR                       emp_job_fk|HR
                < JOB_HISTORY|HR              jhist_job_fk|HR
                  > DEPARTMENTS|HR            jhist_dept_fk|HR
                    < EMPLOYEES|HR*           emp_dept_fk|HR
                    > EMPLOYEES|HR*           dept_mgr_fk|HR
                    > LOCATIONS|HR*           dept_loc_fk|HR
                  > EMPLOYEES|HR*             jhist_emp_fk|HR
              < JOB_HISTORY|HR                jhist_emp_fk|HR
                > DEPARTMENTS|HR              jhist_dept_fk|HR
                  < EMPLOYEES|HR*             emp_dept_fk|HR
                  > EMPLOYEES|HR*             dept_mgr_fk|HR
                  > LOCATIONS|HR*             dept_loc_fk|HR
                > JOBS|HR                     jhist_job_fk|HR
                  < EMPLOYEES|HR*             emp_job_fk|HR
        < PRINT_MEDIA|PM                      printmedia_fk|PM
        < PRODUCT_DESCRIPTIONS|OE             pd_product_id_fk|OE
> REGIONS|HR                                  countr_reg_fk|HR

199 rows selected.

Elapsed: 00:00:00.30

The output above shows that RSF returned 199 rows unfiltered in 0.3s.

Original Demo Network: CBY Output

One tree by Connect By

Nodes                                              Link Path
-------------------------------------------------- ----------------------------------------------------------------------
COUNTRIES|HR > REGIONS|HR                          countr_reg_fk|HR*
LOCATIONS|HR > COUNTRIES|HR                          loc_c_id_fk|HR*
DEPARTMENTS|HR > LOCATIONS|HR                          dept_loc_fk|HR*
EMPLOYEES|HR > DEPARTMENTS|HR                            emp_dept_fk|HR*
JOB_HISTORY|HR > DEPARTMENTS|HR                            jhist_dept_fk|HR*
DEPARTMENTS|HR > EMPLOYEES|HR                                dept_mgr_fk|HR*
EMPLOYEES|HR > EMPLOYEES|HR                                    emp_manager_fk|HR*
CUSTOMERS|OE > EMPLOYEES|HR                                      customers_account_manager_fk|OE*
ORDERS|OE > CUSTOMERS|OE                                           orders_customer_id_fk|OE*
ORDERS|OE > EMPLOYEES|HR                                             orders_sales_rep_fk|OE*
JOB_HISTORY|HR > EMPLOYEES|HR                                          jhist_emp_fk|HR*
EMPLOYEES|HR > JOBS|HR                                                   emp_job_fk|HR*
JOB_HISTORY|HR > JOBS|HR                                                   jhist_job_fk|HR*
JOB_HISTORY|HR > JOBS|HR                                                 jhist_job_fk|HR*
EMPLOYEES|HR > JOBS|HR                                                     emp_job_fk|HR*
EMPLOYEES|HR > JOBS|HR                                                 emp_job_fk|HR*
JOB_HISTORY|HR > EMPLOYEES|HR                                            jhist_emp_fk|HR*
JOB_HISTORY|HR > JOBS|HR                                                   jhist_job_fk|HR*
JOB_HISTORY|HR > JOBS|HR                                                 jhist_job_fk|HR*
JOB_HISTORY|HR > EMPLOYEES|HR                                              jhist_emp_fk|HR*
ORDER_ITEMS|OE > ORDERS|OE                                             order_items_order_id_fk|OE*
ORDER_ITEMS|OE > PRODUCT_INFORMATION|OE                                  order_items_product_id_fk|OE*
INVENTORIES|OE > PRODUCT_INFORMATION|OE                                    inventories_product_id_fk|OE*
PRINT_MEDIA|PM > PRODUCT_INFORMATION|OE                                      printmedia_fk|PM*
.
.
.
ORDERS|OE > CUSTOMERS|OE                                                           orders_customer_id_fk|OE*
EMPLOYEES|HR > EMPLOYEES|HR                                                        emp_manager_fk|HR*
PRINT_MEDIA|PM > PRODUCT_INFORMATION|OE                        printmedia_fk|PM*
ONLINE_MEDIA|PM > PRODUCT_INFORMATION|OE                         loc_c_id_fk|PM*
PRODUCT_DESCRIPTIONS|OE > PRODUCT_INFORMATION|OE                   pd_product_id_fk|OE*
PRODUCT_DESCRIPTIONS|OE > PRODUCT_INFORMATION|OE                 pd_product_id_fk|OE*
ONLINE_MEDIA|PM > PRODUCT_INFORMATION|OE                           loc_c_id_fk|PM*
ONLINE_MEDIA|PM > PRODUCT_INFORMATION|OE                       loc_c_id_fk|PM*
PRINT_MEDIA|PM > PRODUCT_INFORMATION|OE                          printmedia_fk|PM*
PRODUCT_DESCRIPTIONS|OE > PRODUCT_INFORMATION|OE                   pd_product_id_fk|OE*
PRODUCT_DESCRIPTIONS|OE > PRODUCT_INFORMATION|OE                 pd_product_id_fk|OE*
PRINT_MEDIA|PM > PRODUCT_INFORMATION|OE                            printmedia_fk|PM*
PRODUCT_DESCRIPTIONS|OE > PRODUCT_INFORMATION|OE               pd_product_id_fk|OE*
PRINT_MEDIA|PM > PRODUCT_INFORMATION|OE                          printmedia_fk|PM*
ONLINE_MEDIA|PM > PRODUCT_INFORMATION|OE                           loc_c_id_fk|PM*
ONLINE_MEDIA|PM > PRODUCT_INFORMATION|OE                         loc_c_id_fk|PM*
PRINT_MEDIA|PM > PRODUCT_INFORMATION|OE                            printmedia_fk|PM*

4414420 rows selected.

Elapsed: 00:29:33.41

One tree by Connect By filtered

Nodes                                              Link Path                                                              LINK_COUNT
-------------------------------------------------- ---------------------------------------------------------------------- ----------
COUNTRIES|HR > REGIONS|HR                          countr_reg_fk|HR*                                                               1
LOCATIONS|HR > COUNTRIES|HR                          loc_c_id_fk|HR*                                                               1
DEPARTMENTS|HR > LOCATIONS|HR                          dept_loc_fk|HR*                                                        214178
EMPLOYEES|HR > DEPARTMENTS|HR                            emp_dept_fk|HR*                                                      169932
JOB_HISTORY|HR > DEPARTMENTS|HR                            jhist_dept_fk|HR*                                                  272162
DEPARTMENTS|HR > EMPLOYEES|HR                                dept_mgr_fk|HR*                                                  169932
EMPLOYEES|HR > EMPLOYEES|HR                                    emp_manager_fk|HR*                                             207910
CUSTOMERS|OE > EMPLOYEES|HR                                      customers_account_manager_fk|OE*                             132490
ORDERS|OE > CUSTOMERS|OE                                           orders_customer_id_fk|OE*                                   85298
ORDERS|OE > EMPLOYEES|HR                                             orders_sales_rep_fk|OE*                                   72234
JOB_HISTORY|HR > EMPLOYEES|HR                                          jhist_emp_fk|HR*                                       164660
EMPLOYEES|HR > JOBS|HR                                                   emp_job_fk|HR*                                       182784
JOB_HISTORY|HR > JOBS|HR                                                   jhist_job_fk|HR*                                   333192
ORDER_ITEMS|OE > ORDERS|OE                                             order_items_order_id_fk|OE*                             26804
ORDER_ITEMS|OE > PRODUCT_INFORMATION|OE                                  order_items_product_id_fk|OE*                         26804
INVENTORIES|OE > PRODUCT_INFORMATION|OE                                    inventories_product_id_fk|OE*                      428354
PRINT_MEDIA|PM > PRODUCT_INFORMATION|OE                                      printmedia_fk|PM*                                428384
ONLINE_MEDIA|PM > PRODUCT_INFORMATION|OE                                       loc_c_id_fk|PM*                                428384
PRODUCT_DESCRIPTIONS|OE > PRODUCT_INFORMATION|OE                                 pd_product_id_fk|OE*                         428384
INVENTORIES|OE > WAREHOUSES|OE                                               inventories_warehouses_fk|OE*                    428354
WAREHOUSES|OE > LOCATIONS|HR                                                   warehouses_location_fk|OE*                     214178

21 rows selected.

Elapsed: 00:03:03.16

The output above shows that CBY returned 4,414,420 rows unfiltered in 29m33s. Adding filtering reduced the time to 3m03s.

Dual Demo Network

Dual Network, 1.3 - HR-D

This network has 52 links with 32 loops, whereas the original had 21 links with 6 loops.

Dual Demo Network: PLF Output

Node                                                           Link
-------------------------------------------------------------- -------------------------
countr_reg_fk|HR                                               ROOT
> loc_c_id_fk|HR                                               COUNTRIES|HR-1
  < dept_loc_fk|HR                                             LOCATIONS|HR-1
    > dept_mgr_fk|HR                                           DEPARTMENTS|HR-1
      < customers_account_manager_fk|OE                        EMPLOYEES|HR-1
        > emp_dept_fk|HR                                       EMPLOYEES|HR-2
          < dept_loc_fk|HR*                                    DEPARTMENTS|HR-2
          < dept_mgr_fk|HR*                                    EMPLOYEES|HR-7
          > emp_job_fk|HR                                      EMPLOYEES|HR-12
            < customers_account_manager_fk|OE*                 EMPLOYEES|HR-3
            < dept_mgr_fk|HR*                                  EMPLOYEES|HR-8
            > emp_manager_fk|HR                                EMPLOYEES|HR-16
              < customers_account_manager_fk|OE*               EMPLOYEES|HR-4
              < dept_mgr_fk|HR*                                EMPLOYEES|HR-9
              < emp_dept_fk|HR*                                EMPLOYEES|HR-13
              = emp_manager_fk|HR*                             EMPLOYEES|HR-19
              > jhist_emp_fk|HR                                EMPLOYEES|HR-20
                < customers_account_manager_fk|OE*             EMPLOYEES|HR-5
                < dept_mgr_fk|HR*                              EMPLOYEES|HR-10
                < emp_dept_fk|HR*                              EMPLOYEES|HR-14
                < emp_job_fk|HR*                               EMPLOYEES|HR-17
                < jhist_dept_fk|HR                             JOB_HISTORY|HR-1
                  < dept_loc_fk|HR*                            DEPARTMENTS|HR-3
                  < dept_mgr_fk|HR*                            DEPARTMENTS|HR-4
                  < emp_dept_fk|HR*                            DEPARTMENTS|HR-5
                  > jhist_job_fk|HR                            JOB_HISTORY|HR-2
                    < emp_job_fk|HR*                           JOBS|HR-1
                    < jhist_emp_fk|HR*                         JOB_HISTORY|HR-3
                > orders_sales_rep_fk|OE                       EMPLOYEES|HR-22
                  < customers_account_manager_fk|OE*           EMPLOYEES|HR-6
                  < dept_mgr_fk|HR*                            EMPLOYEES|HR-11
                  < emp_dept_fk|HR*                            EMPLOYEES|HR-15
                  < emp_job_fk|HR*                             EMPLOYEES|HR-18
                  < emp_manager_fk|HR*                         EMPLOYEES|HR-21
                  < order_items_order_id_fk|OE                 ORDERS|OE-2
                    > order_items_product_id_fk|OE             ORDER_ITEMS|OE-1
                      < inventories_product_id_fk|OE           PRODUCT_INFORMATION|OE-2
                        > inventories_warehouses_fk|OE         INVENTORIES|OE-1
                          > warehouses_location_fk|OE          WAREHOUSES|OE-1
                            < dept_loc_fk|HR*                  LOCATIONS|HR-2
                            < loc_c_id_fk|HR*                  LOCATIONS|HR-3
                        > loc_c_id_fk|PM                       PRODUCT_INFORMATION|OE-1
                          > order_items_product_id_fk|OE*      PRODUCT_INFORMATION|OE-5
                          > pd_product_id_fk|OE                PRODUCT_INFORMATION|OE-6
                            < inventories_product_id_fk|OE*    PRODUCT_INFORMATION|OE-3
                            < order_items_product_id_fk|OE*    PRODUCT_INFORMATION|OE-8
                            > printmedia_fk|PM                 PRODUCT_INFORMATION|OE-10
                              < inventories_product_id_fk|OE*  PRODUCT_INFORMATION|OE-4
                              < loc_c_id_fk|PM*                PRODUCT_INFORMATION|OE-7
                              < order_items_product_id_fk|OE*  PRODUCT_INFORMATION|OE-9
                    > orders_customer_id_fk|OE                 ORDERS|OE-1
                      < customers_account_manager_fk|OE*       CUSTOMERS|OE-1
                      > orders_sales_rep_fk|OE*                ORDERS|OE-3

53 rows selected.

Elapsed: 00:00:00.27



Dual Demo Network: RSF and CBY Results

Neither of the two SQL recursion methods completed within a period of an hour and had to be terminated. The result for CBY on the original network suggests that RSF on the dual network should return somewhere above 4,414,420 rows.

Conclusions

  • We have shown by examples how network traversal by the Connect By (CBY) approach in SQL corresponds to traversal of all routes in a type of dual version of the original network
  • This dual version, which has forks converted to loops, tends to be larger and more heavily looped, resulting in worse performance compared with solution by recursive subquery factors (RSF)
  • The examples illustrate the different treatment of loop-closing links between the two types of SQL recursion
  • The RSF solutions on the dual network in the simpler examples where it completes is seen to be equivalent to the CBY solution on the original network, after allowing for the different treatment of loop-closing links
  • On the foreign key network for Oracle's HR/OE/PM demo, which has 21 links, RSF returns 199 rows while CBY returns 4,414,420 rows
  • On the dual version of the foreign key network for Oracle's HR/OE/PM demo, which has 52 links, RSF and CBY fail to complete in reasonable times
  • The pipelined function method returns the solution on both original and dual in a small fraction of a second

SQL files: SQL for network duality
Output files: Output for network duality

Oracle version used: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production






SQL Developer: Importing Unit Test Repository via Data Modeler

Recently I started looking at the unit testing functionality within Oracle’s SQL Developer, as a possible alternative to ut/PLSQL. Oracle’s Jeff Smith has a useful starter page on this, Unit Testing Your PL/SQL with Oracle SQL Developer. As he notes, the first thing you need to do is set up a unit test repository, which is essentially a schema within an Oracle database that SQL Developer will create for you for the unit test metadata. The schema contains 24 tables, and when I set it up I thought it would be nice to see an entity-relationship diagram for it.

I often draw these myself after running an analysis query on the foreign key network, as described in a recent post, PL/SQL Pipelined Function for Network Analysis. However, in this case I remembered that SQL Developer also has a data modeler component, and I thought it would be a good opportunity to learn how to use this tool to reverse-engineer a diagram from the schema. It’s actually very easy, and in this article I will show the result (see another Jeff Smith post for learning resources on the data modeler, Data Modeling). For good measure, I will include outputs from my own metadata queries, and add similar for Oracle’s HR demo schema (and linked schemas, in version 12.1).

12 July 2015I added a section on the Oracle v11.2 Apex schema, APEX_040000, which has 425 tables, to see how the data modeler handles larger schemas.

Unit Test Repository Schema

I created the diagram by following the wizard from File/Import/Data Dictionary – see the link above for more information on how to use the Data Modeler.

Data Modeler Diagram

UT_REPOS

Network Analysis Output

Network                      #Links  #Nodes Lev  Node                                                     Link
---------------------------  ------  ------ ---  -------------------------------------------------------  --------------------------------------
UT_LIB_DYN_QUERIES|UT_REPOS      30      20   0  UT_LIB_DYN_QUERIES|UT_REPOS                              ROOT
                                              1  < UT_TEST_IMPL|UT_REPOS                                  ut_test_lib_dyn_queries_fk1|ut_repos
                                              2    < UT_TEST_COVERAGE_STATS|UT_REPOS                      ut_test_cov_stats_ut_t_fk1|ut_repos                                               3      > UT_TEST_IMPL_RESULTS|UT_REPOS                      ut_test_cov_stats_ut_t_fk2|ut_repos
                                              4        < UT_TEST_IMPL_VAL_RESULTS|UT_REPOS                ut_test_impl_val_res_fk3|ut_repos                                               5          > UT_TEST_IMPL|UT_REPOS*                         ut_test_impl_val_res_fk2|ut_repos
                                              5          > UT_VALIDATIONS|UT_REPOS                        ut_test_impl_val_res_fk1|ut_repos
                                              6            > UT_LIB_VALIDATIONS|UT_REPOS                  ut_validations_lib_validn_fk1|ut_repos
                                              6            > UT_TEST_IMPL|UT_REPOS*                       ut_validations_test_impl_fk1|ut_repos
                                              4        > UT_TEST_IMPL|UT_REPOS*                           ut_test_impl_results_ut_t_fk1|ut_repos
                                              4        > UT_TEST_RESULTS|UT_REPOS                         ut_test_impl_results_ut_t_fk2|ut_repos
                                              5          < UT_SUITE_ITEM_RESULTS|UT_REPOS                 ut_suite_item_results_fk2|ut_repos                                               6            > UT_SUITE_RESULTS|UT_REPOS                    ut_suite_item_results_fk1|ut_repos
                                              7              < UT_SUITE_ITEM_RESULTS|UT_REPOS*            ut_suite_item_results_fk3|ut_repos                                               7              > UT_SUITE|UT_REPOS                          ut_suite_results_fk1|ut_repos
                                              8                < UT_STARTUPS|UT_REPOS                     ut_startups_suite_fk1|ut_repos                                               9                  > UT_LIB_STARTUPS|UT_REPOS               ut_startups_lib_validn_fk1|ut_repos
                                              9                  > UT_TEST|UT_REPOS                       ut_startups_test_fk1|ut_repos
                                              0                    < UT_SUITE_ITEMS|UT_REPOS              ut_suite_items_fk2|ut_repos                                               1                      > UT_SUITE|UT_REPOS*                 ut_suite_items_fk1|ut_repos
                                              1                      > UT_SUITE|UT_REPOS*                 ut_suite_items_fk3|ut_repos
                                              0                    < UT_TEARDOWNS|UT_REPOS                ut_teardowns_test_fk1|ut_repos                                               1                      > UT_LIB_TEARDOWNS|UT_REPOS          ut_teardowns_lib_validn_fk1|ut_repos
                                              1                      > UT_SUITE|UT_REPOS*                 ut_teardowns_suite_fk1|ut_repos
                                              0                    < UT_TEST_ARGUMENTS|UT_REPOS           ut_test_arguments_fk|ut_repos
                                              1                      < UT_TEST_IMPL_ARGUMENTS|UT_REPOS    ut_test_impl_arguments_ut_fk1|ut_repos                                               2                        > UT_TEST_IMPL|UT_REPOS*           ut_test_impl_arguments_ut_fk2|ut_repos
                                              1                      < UT_TEST_IMPL_ARG_RESULTS|UT_REPOS  ut_test_impl_arg_results__fk2|ut_repos                                               2                        > UT_TEST_IMPL|UT_REPOS*           ut_test_impl_arg_results__fk1|ut_repos
                                              0                    < UT_TEST_IMPL|UT_REPOS*               ut_test_impl_ut_test_fk1|ut_repos
                                              0                    < UT_TEST_RESULTS|UT_REPOS*            ut_test_results_ut_test_fk1|ut_repos
UT_LOOKUP_CATEGORIES|UT_REPOS      2       3  0  UT_LOOKUP_CATEGORIES|UT_REPOS                            ROOT
                                              1  < UT_LOOKUP_DATATYPES|UT_REPOS                           ut_lookup_datatypes_ut_lo_fk1|ut_repos
                                              2    < UT_LOOKUP_VALUES|UT_REPOS                            ut_lookup_values_ut_looku_fk1|ut_repos

There is one table not shown as it has no links to any other table.

Schema Metadata Analysis Report

SQL> SELECT 'Start: '||dbs.name "Database", ses.sid "Session", ses.osuser "OS User", ses.machine "Machine", To_Char (SYSDATE,'DD-MON-YYYY HH24:MI:SS') "Time",
  2   Replace (Substr(ver.banner, 1, Instr(ver.banner, '64')-4), 'Enterprise Edition Release ', '') "Version"
  3    FROM v$database dbs, v$version ver, v$session ses
  4   WHERE ver.banner LIKE 'Oracle%'
  5     AND audsid = USERENV('sessionid');

Database              Session OS User    Machine              Time                 Version
-------------------- -------- ---------- -------------------- -------------------- ------------------------------
Start: XE                 137 HP-Brendan WORKGROUP\HP-BRENDAN 11-JUL-2015 13:21:25 Oracle Database 11g Express Ed
                              \Brend_000                                           ition Release 11.2.0.2.0


SQL> 
SQL> PROMPT '&owner' "Schema", '&tab' "Prefix"
'UT_REPOS' "Schema", '%' "Prefix"
SQL> PROMPT Tables Summary (excluding like '&nottab1', '&nottab2', '$' )
Tables Summary (excluding like '?', '?', '$' )
SQL> SELECT atc.table_name, Nvl2 (atc_w.column_name, 'Yes', NULL)    "Who?",
  2          CASE att.n_att WHEN 0 THEN To_Number(NULL) ELSE att.n_att END  "Attrs",
  3          ind_tot.n_ind "Indexes", Count(atc.column_name)      "Cols"
  4    FROM all_tab_columns   atc
  5      JOIN all_tables atb
  6        ON atb.table_name      = atc.table_name
  7       AND atb.owner     = atc.owner                             -- Join index count
  8        LEFT JOIN (SELECT i.table_owner, i.table_name, Count (i.index_name) n_ind
  9        FROM all_indexes     i
 10       GROUP BY i.table_owner, i.table_name)     ind_tot
 11          ON ind_tot.table_name    = atb.table_name
 12         AND ind_tot.table_owner   = atb.owner       -- Join attribute count
 13        LEFT JOIN (SELECT atc_att.table_name, atc_att.owner, Count(atc_att.column_name) n_att
 14              FROM all_tab_columns     atc_att
 15             WHERE atc_att.column_name   LIKE '%&notcol%'
 16             GROUP BY atc_att.table_name, atc_att.owner)   att
 17          ON att.table_name    = atb.table_name
 18         AND att.owner     = atb.owner
 19    LEFT JOIN all_tab_columns    atc_w         -- Join the Who column if it exists
 20           ON atc_w.table_name   = atb.table_name
 21          AND atc_w.owner      = atb.owner
 22          AND atc_w.column_name    = 'CREATED_BY'        -- Join index with its sequence for table
 23   WHERE atc.table_name      LIKE Upper('&tab'||'%')
 24     AND atc.table_name      NOT LIKE '%$%'
 25     AND atb.table_name      NOT LIKE '%&nottab1%'
 26     AND atb.table_name      NOT LIKE '%&nottab2%'
 27     AND atb.owner     LIKE Upper('&owner')
 28   GROUP BY atc.table_name,  Nvl2 (atc_w.column_name, 'Yes', NULL),
 29   CASE att.n_att WHEN 0 THEN To_Number(NULL) ELSE att.n_att END,
 30          ind_tot.n_ind
 31   ORDER BY 1;

TABLE_NAME                     Who?  Attrs Indexes Cols
------------------------------ ----- ----- ------- ----
UT_LIB_DYN_QUERIES             Yes               2    8
UT_LIB_STARTUPS                Yes               2    8
UT_LIB_TEARDOWNS               Yes               2    8
UT_LIB_VALIDATIONS             Yes               2    8
UT_LOOKUP_CATEGORIES           Yes               2    6
UT_LOOKUP_DATATYPES            Yes               3    8
UT_LOOKUP_VALUES               Yes               2    7
UT_METADATA                                      1    2
UT_STARTUPS                    Yes               4   10
UT_SUITE                       Yes               1    7
UT_SUITE_ITEMS                 Yes               4   10
UT_SUITE_ITEM_RESULTS          Yes               4    8
UT_SUITE_RESULTS               Yes               2   21
UT_TEARDOWNS                   Yes               4   10
UT_TEST                        Yes               2   12
UT_TEST_ARGUMENTS              Yes               2   30
UT_TEST_COVERAGE_STATS         Yes               3   13
UT_TEST_IMPL                   Yes               4   11
UT_TEST_IMPL_ARGUMENTS         Yes               3    9
UT_TEST_IMPL_ARG_RESULTS       Yes               4   12
UT_TEST_IMPL_RESULTS           Yes               3   29
UT_TEST_IMPL_VAL_RESULTS       Yes               4   15
UT_TEST_RESULTS                Yes               2   13
UT_VALIDATIONS                 Yes               3   10

24 rows selected.

SQL> 
SQL> PROMPT Triggers
Triggers
SQL> COLUMN "Trigger"  FORMAT A30
SQL> COLUMN "Type"   FORMAT A30
SQL> COLUMN "Event"    FORMAT A30
SQL> 
SQL> SELECT  trg.table_name,
  2   trg.trigger_name    "Trigger",
  3   trg.trigger_type    "Type",
  4   trg.triggering_event    "Event"
  5    FROM all_triggers trg
  6   WHERE trg.table_name      LIKE Upper('&tab'||'%')
  7     AND trg.table_name      NOT LIKE '%$%'
  8     AND trg.table_name      NOT LIKE '%&nottab1%'
  9     AND trg.table_name      NOT LIKE '%&nottab2%'
 10     AND trg.owner     LIKE Upper('&owner')
 11   ORDER BY 1, 2;

TABLE_NAME                     Trigger                        Type                           Event
------------------------------ ------------------------------ ------------------------------ ------------------------------
UT_LIB_DYN_QUERIES             UT_LIB_DYN_QUERIES             BEFORE EACH ROW                INSERT
                               UT_LIB_DYN_QUERIES_UP_TRG      BEFORE EACH ROW                UPDATE
UT_LIB_STARTUPS                UT_LIB_STARTUPS                BEFORE EACH ROW                INSERT
                               UT_LIB_STARTUPS_UP_TRG         BEFORE EACH ROW                UPDATE
UT_LIB_TEARDOWNS               UT_LIB_TEARDOWNS               BEFORE EACH ROW                INSERT
                               UT_LIB_TEARDOWNS_UP_TRG        BEFORE EACH ROW                UPDATE
UT_LIB_VALIDATIONS             UT_LIB_VALIDATIONS             BEFORE EACH ROW                INSERT
                               UT_LIB_VALIDATIONS_UP_TRG      BEFORE EACH ROW                UPDATE
UT_LOOKUP_CATEGORIES           UT_LOOKUP_CAT_TRG              BEFORE EACH ROW                INSERT
                               UT_LOOKUP_CAT_UP_TRG           BEFORE EACH ROW                UPDATE
UT_LOOKUP_DATATYPES            UT_LOOKUP_DATATYPES_UP_TRG     BEFORE EACH ROW                UPDATE
                               UT_LOOKUP_DATA_TRG             BEFORE EACH ROW                INSERT
UT_LOOKUP_VALUES               UT_LOOKUP_VALUES_UP_TRG        BEFORE EACH ROW                UPDATE
                               UT_LOOKUP_VALUE_TRG            BEFORE EACH ROW                INSERT
UT_STARTUPS                    UT_STARTUPS_TRG                BEFORE EACH ROW                INSERT
                               UT_STARTUPS_UP_TRG             BEFORE EACH ROW                UPDATE
UT_SUITE                       UT_SUITE_TRG                   BEFORE EACH ROW                INSERT
                               UT_SUITE_UP_TRG                BEFORE EACH ROW                UPDATE
UT_SUITE_ITEMS                 UT_SUITE_ITEMS_TRG             BEFORE EACH ROW                INSERT
                               UT_SUITE_ITEMS_UP_TRG          BEFORE EACH ROW                UPDATE
UT_SUITE_ITEM_RESULTS          UT_SUITE_ITEM_RESULTS_TRG      BEFORE EACH ROW                INSERT
                               UT_SUITE_ITEM_RESULTS_UP_TRG   BEFORE EACH ROW                UPDATE
UT_SUITE_RESULTS               UT_SUITE_RESULTS_TRG           BEFORE EACH ROW                INSERT
                               UT_SUITE_RESULTS_UP_TRG        BEFORE EACH ROW                UPDATE
UT_TEARDOWNS                   UT_TEARDOWNS_TRG               BEFORE EACH ROW                INSERT
                               UT_TEARDOWNS_UP_TRG            BEFORE EACH ROW                UPDATE
UT_TEST                        UT_TEST_TRG                    BEFORE EACH ROW                INSERT
                               UT_TEST_UP_TRG                 BEFORE EACH ROW                UPDATE
UT_TEST_ARGUMENTS              UT_TEST_ARGUMENTS_TRG          BEFORE EACH ROW                INSERT
                               UT_TEST_ARGUMENTS_UP_TRG       BEFORE EACH ROW                UPDATE
UT_TEST_COVERAGE_STATS         UT_TEST_COVERAGE_STATS_TRG     BEFORE EACH ROW                INSERT
                               UT_TEST_COVERAGE_STATS_UP_TRG  BEFORE EACH ROW                UPDATE
UT_TEST_IMPL                   UT_TEST_IMPL_TRG               BEFORE EACH ROW                INSERT
                               UT_TEST_IMPL_UP_TRG            BEFORE EACH ROW                UPDATE
UT_TEST_IMPL_ARGUMENTS         UT_TEST_IMPL_ARGUMENTS_TRG     BEFORE EACH ROW                INSERT
                               UT_TEST_IMPL_ARGUMENTS_UP_TRG  BEFORE EACH ROW                UPDATE
UT_TEST_IMPL_ARG_RESULTS       UT_TEST_IMPL_ARG_RESULTS_TRG   BEFORE EACH ROW                INSERT
                               UT_TEST_IMPL_ARG_RES_UP_TRG    BEFORE EACH ROW                UPDATE
UT_TEST_IMPL_RESULTS           UT_TEST_IMPL_RESULTS_TRG       BEFORE EACH ROW                INSERT
                               UT_TEST_IMPL_RESULTS_UP_TRG    BEFORE EACH ROW                UPDATE
UT_TEST_IMPL_VAL_RESULTS       UT_TEST_IMPL_VAL_RESULTS_TRG   BEFORE EACH ROW                INSERT
                               UT_TEST_IMPL_VAL_RES_UP_TRG    BEFORE EACH ROW                UPDATE
UT_TEST_RESULTS                UT_TEST_RESULTS_TRG            BEFORE EACH ROW                INSERT
                               UT_TEST_RESULTS_UP_TRG         BEFORE EACH ROW                UPDATE
UT_VALIDATIONS                 UT_VALIDATIONS_TRG             BEFORE EACH ROW                INSERT
                               UT_VALIDATIONS_UP_TRG          BEFORE EACH ROW                UPDATE

46 rows selected.

SQL> 
SQL> PROMPT Foreign Keys
Foreign Keys
SQL> COLUMN "From Table" FORMAT A30
SQL> COLUMN "To Table" FORMAT A30
SQL> COLUMN "Constraint" FORMAT A30
SQL> COLUMN "Column"   FORMAT A30
SQL> COLUMN "Seq"    FORMAT 990
SQL> 
SQL> BREAK ON "From Table" ON "To Table" ON "Constraint"
SQL> 
SQL> SELECT  con_f.table_name    "From Table",
  2   con_t.table_name    "To Table",
  3   con_f.constraint_name   "Constraint",
  4   col_f.position      "Seq",
  5   col_f.column_name   "Column"
  6    FROM all_constraints     con_f
  7    JOIN all_constraints     con_t
  8      ON con_t.constraint_name   = con_f.r_constraint_name
  9     AND con_t.owner     = con_f.r_owner
 10    JOIN all_cons_columns    col_f
 11      ON con_f.constraint_type   = 'R'
 12     AND col_f.constraint_name   = con_f.constraint_name
 13     AND col_f.owner     = con_f.owner
 14   WHERE
 15      (
 16        (con_f.table_name        LIKE Upper('&tab'||'%')
 17     AND con_f.table_name        NOT LIKE '%$%'
 18     AND con_f.table_name        NOT LIKE '%&nottab1%'
 19     AND con_f.table_name        NOT LIKE '%&nottab2%'
 20     AND con_f.owner       LIKE Upper('&owner')
 21         ) OR
 22        (con_t.table_name        LIKE Upper('&tab'||'%')
 23     AND con_t.table_name        NOT LIKE '%$%'
 24     AND con_t.table_name        NOT LIKE '%&nottab1%'
 25     AND con_t.table_name        NOT LIKE '%&nottab2%'
 26     AND con_t.owner       LIKE Upper('&owner')
 27        ))
 28   ORDER BY 1, 2, 3, 4;

From Table                     To Table                       Constraint                      Seq Column
------------------------------ ------------------------------ ------------------------------ ---- ------------------------------
UT_LOOKUP_DATATYPES            UT_LOOKUP_CATEGORIES           UT_LOOKUP_DATATYPES_UT_LO_FK1     1 CAT_ID
UT_LOOKUP_VALUES               UT_LOOKUP_DATATYPES            UT_LOOKUP_VALUES_UT_LOOKU_FK1     1 DATA_ID
UT_STARTUPS                    UT_LIB_STARTUPS                UT_STARTUPS_LIB_VALIDN_FK1        1 LIB_STARTUP_ID
                               UT_SUITE                       UT_STARTUPS_SUITE_FK1             1 UT_SID
                               UT_TEST                        UT_STARTUPS_TEST_FK1              1 UT_ID
UT_SUITE_ITEMS                 UT_SUITE                       UT_SUITE_ITEMS_FK1                1 UT_SID
                                                              UT_SUITE_ITEMS_FK3                1 UT_NSID
                               UT_TEST                        UT_SUITE_ITEMS_FK2                1 UT_ID
UT_SUITE_ITEM_RESULTS          UT_SUITE_RESULTS               UT_SUITE_ITEM_RESULTS_FK1         1 UTSR_ID
                                                              UT_SUITE_ITEM_RESULTS_FK3         1 UTR_NSID
                               UT_TEST_RESULTS                UT_SUITE_ITEM_RESULTS_FK2         1 UTR_ID
UT_SUITE_RESULTS               UT_SUITE                       UT_SUITE_RESULTS_FK1              1 UT_SID
UT_TEARDOWNS                   UT_LIB_TEARDOWNS               UT_TEARDOWNS_LIB_VALIDN_FK1       1 LIB_TEARDOWN_ID
                               UT_SUITE                       UT_TEARDOWNS_SUITE_FK1            1 UT_SID
                               UT_TEST                        UT_TEARDOWNS_TEST_FK1             1 UT_ID
UT_TEST_ARGUMENTS              UT_TEST                        UT_TEST_ARGUMENTS_FK              1 UT_ID
UT_TEST_COVERAGE_STATS         UT_TEST_IMPL                   UT_TEST_COV_STATS_UT_T_FK1        1 UTI_ID
                               UT_TEST_IMPL_RESULTS           UT_TEST_COV_STATS_UT_T_FK2        1 UTIR_ID
UT_TEST_IMPL                   UT_LIB_DYN_QUERIES             UT_TEST_LIB_DYN_QUERIES_FK1       1 LIB_DYN_QUERY_ID
                               UT_TEST                        UT_TEST_IMPL_UT_TEST_FK1          1 UT_ID
UT_TEST_IMPL_ARGUMENTS         UT_TEST_ARGUMENTS              UT_TEST_IMPL_ARGUMENTS_UT_FK1     1 ARG_ID
                               UT_TEST_IMPL                   UT_TEST_IMPL_ARGUMENTS_UT_FK2     1 UTI_ID
UT_TEST_IMPL_ARG_RESULTS       UT_TEST_ARGUMENTS              UT_TEST_IMPL_ARG_RESULTS__FK2     1 ARG_ID
                               UT_TEST_IMPL                   UT_TEST_IMPL_ARG_RESULTS__FK1     1 UTI_ID
UT_TEST_IMPL_RESULTS           UT_TEST_IMPL                   UT_TEST_IMPL_RESULTS_UT_T_FK1     1 UTI_ID
                               UT_TEST_RESULTS                UT_TEST_IMPL_RESULTS_UT_T_FK2     1 UTR_ID
UT_TEST_IMPL_VAL_RESULTS       UT_TEST_IMPL                   UT_TEST_IMPL_VAL_RES_FK2          1 UTI_ID
                               UT_TEST_IMPL_RESULTS           UT_TEST_IMPL_VAL_RES_FK3          1 UTIR_ID
                               UT_VALIDATIONS                 UT_TEST_IMPL_VAL_RES_FK1          1 VAL_ID
UT_TEST_RESULTS                UT_TEST                        UT_TEST_RESULTS_UT_TEST_FK1       1 UT_ID
UT_VALIDATIONS                 UT_LIB_VALIDATIONS             UT_VALIDATIONS_LIB_VALIDN_FK1     1 LIB_VALIDATION_ID
                               UT_TEST_IMPL                   UT_VALIDATIONS_TEST_IMPL_FK1      1 UTI_ID

32 rows selected.

SQL> 
SQL> BREAK ON table_name ON "Who?" ON "Attrs" ON "Indexes"
SQL> PROMPT Tables and Columns (omitting Who and %&notcol%)
Tables and Columns (omitting Who and %?%)
SQL> SELECT atc.table_name, Nvl2 (atc_w.column_name, 'Yes', NULL)      "Who?",
  2          CASE att.n_att WHEN 0 THEN To_Number(NULL) ELSE att.n_att END    "Attrs",
  3          ind_tot.n_ind                "Indexes",
  4          Lower (atc.column_name)|| CASE atc.nullable WHEN 'N' THEN '*' END  column_name,
  5   atc.data_type,
  6   atc.data_length               "Length",
  7                  Max(CASE ind.rn WHEN 1 THEN To_Char(aic.column_position) ELSE ' ' END) ||
  8                  Max(CASE ind.rn WHEN 2 THEN To_Char(aic.column_position) ELSE ' ' END) ||
  9                  Max(CASE ind.rn WHEN 3 THEN To_Char(aic.column_position) ELSE ' ' END) ||
 10                  Max(CASE ind.rn WHEN 4 THEN To_Char(aic.column_position) ELSE ' ' END) ||
 11                  Max(CASE ind.rn WHEN 5 THEN To_Char(aic.column_position) ELSE ' ' END) ||
 12                  Max(CASE ind.rn WHEN 6 THEN To_Char(aic.column_position) ELSE ' ' END) ||
 13                  Max(CASE ind.rn WHEN 7 THEN To_Char(aic.column_position) ELSE ' ' END) ||
 14                  Max(CASE ind.rn WHEN 8 THEN To_Char(aic.column_position) ELSE ' ' END) ||
 15                  Max(CASE ind.rn WHEN 9 THEN To_Char(aic.column_position) ELSE ' ' END) "Index Pos"
 16    FROM all_tab_columns     atc
 17      JOIN all_tables      atb
 18        ON atb.table_name      = atc.table_name
 19       AND atb.owner     = atc.owner                             -- Join index count
 20      LEFT JOIN (SELECT i.table_owner, i.table_name, Count (i.index_name) n_ind
 21              FROM all_indexes       i
 22           GROUP BY i.table_owner, i.table_name)   ind_tot
 23           ON ind_tot.table_name   = atb.table_name
 24          AND ind_tot.table_owner    = atb.owner       -- Join attribute count
 25    LEFT JOIN (SELECT atc_att.table_name, atc_att.owner, Count(atc_att.column_name) n_att
 26          FROM all_tab_columns       atc_att
 27         WHERE atc_att.column_name LIKE '%&notcol%'
 28         GROUP BY atc_att.table_name, atc_att.owner) att
 29           ON att.table_name   = atb.table_name
 30          AND att.owner      = atb.owner
 31    LEFT JOIN all_tab_columns atc_w            -- Join the Who column if it exists
 32           ON atc_w.table_name   = atb.table_name
 33          AND atc_w.owner      = atb.owner
 34          AND atc_w.column_name    = 'CREATED_BY'        -- Join index with its sequence for table
 35    LEFT JOIN (SELECT Row_Number () OVER (PARTITION BY i.table_name, i.table_owner
 36           ORDER BY i.table_name, i.table_owner, i.index_name) rn,
 37         i.table_name, i.table_owner, i.owner, i.index_name
 38          FROM all_indexes i)        ind
 39      ON ind.table_name      = atb.table_name
 40     AND ind.table_owner     = atb.owner
 41          LEFT JOIN all_ind_columns  aic         -- Join columns for the index
 42            ON aic.index_owner   = ind.owner
 43           AND aic.index_name    = ind.index_name
 44           AND aic.column_name   = atc.column_name
 45   WHERE atc.column_name     NOT LIKE '%&notcol%'
 46     AND atc.table_name      LIKE Upper('&tab'||'%')
 47     AND atc.table_name      NOT LIKE '%$%'
 48     AND atb.table_name      NOT LIKE '%&nottab1%'
 49     AND atb.table_name      NOT LIKE '%&nottab2%'
 50     AND atb.owner     LIKE Upper('&owner')
 51     AND atc.column_name     NOT IN ('CREATED_BY', 'CREATION_DATE', 'LAST_UPDATED_BY', 'LAST_UPDATE_DATE')
 52   GROUP BY atc.table_name, Nvl2 (atc_w.column_name, 'Yes', NULL), Lower (atc.column_name)||CASE atc.nullable WHEN 'N' THEN '*' END,
 53          CASE att.n_att WHEN 0 THEN To_Number(NULL) ELSE att.n_att END,
 54          ind_tot.n_ind, atc.data_type, atc.data_length
 55   ORDER BY 1, 2;

TABLE_NAME                     Who?  Attrs Indexes COLUMN_NAME                    DATA_TYPE     Length Index Pos
------------------------------ ----- ----- ------- ------------------------------ ------------- ------ ----------
UT_LIB_DYN_QUERIES             Yes               2 created_on*                    TIMESTAMP(6)      11
                                                   lib_dyn_query*                 CLOB            4000
                                                   lib_dyn_query_class*           VARCHAR2         120
                                                   lib_dyn_query_id*              VARCHAR2          40  1
                                                   lib_dyn_query_name*            VARCHAR2         120 1
                                                   updated_by*                    VARCHAR2         120
                                                   updated_on*                    TIMESTAMP(6)      11
UT_LIB_STARTUPS                Yes               2 created_on*                    TIMESTAMP(6)      11
                                                   lib_startup*                   CLOB            4000
                                                   lib_startup_class*             VARCHAR2         120
                                                   lib_startup_id*                VARCHAR2          40  1
                                                   lib_startup_name*              VARCHAR2         120 1
                                                   updated_by*                    VARCHAR2         120
                                                   updated_on*                    TIMESTAMP(6)      11
UT_LIB_TEARDOWNS               Yes               2 created_on*                    TIMESTAMP(6)      11
                                                   lib_teardown*                  CLOB            4000
                                                   lib_teardown_class*            VARCHAR2         120
                                                   lib_teardown_id*               VARCHAR2          40  1
                                                   lib_teardown_name*             VARCHAR2         120 1
                                                   updated_by*                    VARCHAR2         120
                                                   updated_on*                    TIMESTAMP(6)      11
UT_LIB_VALIDATIONS             Yes               2 created_on*                    TIMESTAMP(6)      11
                                                   lib_validation*                CLOB            4000
                                                   lib_validation_class*          VARCHAR2         120
                                                   lib_validation_id*             VARCHAR2          40  1
                                                   lib_validation_name*           VARCHAR2         120 1
                                                   updated_by*                    VARCHAR2         120
                                                   updated_on*                    TIMESTAMP(6)      11
UT_LOOKUP_CATEGORIES           Yes               2 created_on*                    TIMESTAMP(6)      11
                                                   id*                            VARCHAR2          40 1
                                                   name                           VARCHAR2         120  1
                                                   updated_by*                    VARCHAR2         120
                                                   updated_on*                    TIMESTAMP(6)      11
UT_LOOKUP_DATATYPES            Yes               3 cat_id                         VARCHAR2          40  11
                                                   created_on*                    TIMESTAMP(6)      11
                                                   id*                            VARCHAR2          40 1
                                                   type_id                        NUMBER            22
                                                   type_string                    VARCHAR2         120  2
                                                   updated_by*                    VARCHAR2         120
                                                   updated_on*                    TIMESTAMP(6)      11
UT_LOOKUP_VALUES               Yes               2 created_on*                    TIMESTAMP(6)      11
                                                   data_id                        VARCHAR2          40  1
                                                   id*                            VARCHAR2          40 1
                                                   updated_by*                    VARCHAR2         120
                                                   updated_on*                    TIMESTAMP(6)      11
                                                   value                          CLOB            4000
UT_METADATA                                      1 name*                          VARCHAR2         120 1
                                                   value*                         VARCHAR2        2000
UT_STARTUPS                    Yes               4 created_on*                    TIMESTAMP(6)      11
                                                   index_no*                      NUMBER            22
                                                   lib_startup_id                 VARCHAR2          40 1
                                                   startup                        CLOB            4000
                                                   startup_id*                    VARCHAR2          40  1
                                                   updated_by*                    VARCHAR2         120
                                                   updated_on*                    TIMESTAMP(6)      11
                                                   ut_id                          VARCHAR2          40    1
                                                   ut_sid                         VARCHAR2          40   1
UT_SUITE                       Yes               1 coverage                       NUMBER            22
                                                   created_on*                    TIMESTAMP(6)      11
                                                   name*                          VARCHAR2         120
                                                   updated_by*                    VARCHAR2         120
                                                   updated_on*                    TIMESTAMP(6)      11
                                                   ut_sid*                        VARCHAR2          40 1
UT_SUITE_ITEMS                 Yes               4 created_on*                    TIMESTAMP(6)      11
                                                   run_start*                     VARCHAR2           1
                                                   run_tear*                      VARCHAR2           1
                                                   sequence*                      NUMBER            22
                                                   updated_by*                    VARCHAR2         120
                                                   updated_on*                    TIMESTAMP(6)      11
                                                   ut_id                          VARCHAR2          40   12
                                                   ut_nsid                        VARCHAR2          40  1 3
                                                   ut_sid*                        VARCHAR2          40 1  1
UT_SUITE_ITEM_RESULTS          Yes               4 created_on*                    TIMESTAMP(6)      11
                                                   sequence*                      NUMBER            22
                                                   updated_by*                    VARCHAR2         120
                                                   updated_on*                    TIMESTAMP(6)      11
                                                   utr_id                         VARCHAR2          40  1 2
                                                   utr_nsid                       VARCHAR2          40   13
                                                   utsr_id*                       VARCHAR2          40 1  1
UT_SUITE_RESULTS               Yes               2 coverage*                      NUMBER            22
                                                   created_on*                    TIMESTAMP(6)      11
                                                   message                        VARCHAR2        2000
                                                   name*                          VARCHAR2         120
                                                   run_date*                      TIMESTAMP(6)      11
                                                   startup_duration               NUMBER            22
                                                   startup_end_time               VARCHAR2          20
                                                   startup_message                VARCHAR2        2000
                                                   startup_start_time             VARCHAR2          20
                                                   startup_status                 VARCHAR2          20
                                                   status*                        VARCHAR2          20
                                                   teardown_duration              NUMBER            22
                                                   teardown_end_time              VARCHAR2          20
                                                   teardown_message               VARCHAR2        2000
                                                   teardown_start_time            VARCHAR2          20
                                                   teardown_status                VARCHAR2          20
                                                   updated_by*                    VARCHAR2         120
                                                   updated_on*                    TIMESTAMP(6)      11
                                                   ut_sid*                        VARCHAR2          40 1
                                                   utsr_id*                       VARCHAR2          40  1
UT_TEARDOWNS                   Yes               4 created_on*                    TIMESTAMP(6)      11
                                                   index_no*                      NUMBER            22
                                                   lib_teardown_id                VARCHAR2          40 1
                                                   teardown                       CLOB            4000
                                                   teardown_id*                   VARCHAR2          40  1
                                                   updated_by*                    VARCHAR2         120
                                                   updated_on*                    TIMESTAMP(6)      11
                                                   ut_id                          VARCHAR2          40    1
                                                   ut_sid                         VARCHAR2          40   1
UT_TEST                        Yes               2 connection_name                VARCHAR2         120
                                                   coverage                       NUMBER            22
                                                   created_on*                    TIMESTAMP(6)      11
                                                   name*                          VARCHAR2         120  1
                                                   object_call                    VARCHAR2         120
                                                   object_name*                   VARCHAR2         120
                                                   object_owner*                  VARCHAR2         120
                                                   object_type*                   VARCHAR2         120
                                                   updated_by*                    VARCHAR2         120
                                                   updated_on*                    TIMESTAMP(6)      11
                                                   ut_id*                         VARCHAR2          40 1
UT_TEST_ARGUMENTS              Yes               2 arg_id*                        VARCHAR2          40  1
                                                   argument_name                  VARCHAR2          30
                                                   char_length                    NUMBER            22
                                                   char_used                      VARCHAR2           1
                                                   character_set_name             VARCHAR2          44
                                                   created_on*                    TIMESTAMP(6)      11
                                                   data_length                    NUMBER            22
                                                   data_level*                    NUMBER            22
                                                   data_precision                 NUMBER            22
                                                   data_scale                     NUMBER            22
                                                   data_type                      VARCHAR2          30
                                                   default_length                 NUMBER            22
                                                   in_out                         VARCHAR2           9
                                                   object_id*                     NUMBER            22
                                                   object_name                    VARCHAR2          30
                                                   overload                       VARCHAR2          40
                                                   owner*                         VARCHAR2          30
                                                   package_name                   VARCHAR2          30
                                                   pls_type                       VARCHAR2          30
                                                   position*                      NUMBER            22
                                                   radix                          NUMBER            22
                                                   sequence*                      NUMBER            22
                                                   type_link                      VARCHAR2         128
                                                   type_name                      VARCHAR2          30
                                                   type_owner                     VARCHAR2          30
                                                   type_subname                   VARCHAR2          30
                                                   updated_by*                    VARCHAR2         120
                                                   updated_on*                    TIMESTAMP(6)      11
                                                   ut_id                          VARCHAR2          40 1
UT_TEST_COVERAGE_STATS         Yes               3 created_on*                    TIMESTAMP(6)      11
                                                   line                           NUMBER            22
                                                   text                           VARCHAR2        4000
                                                   total_occur                    NUMBER            22
                                                   total_time                     NUMBER            22
                                                   unit_name                      VARCHAR2          30
                                                   unit_owner                     VARCHAR2          30
                                                   updated_by*                    VARCHAR2         120
                                                   updated_on*                    TIMESTAMP(6)      11
                                                   utc_id*                        VARCHAR2          40 1
                                                   uti_id                         VARCHAR2          40  1
                                                   utir_id                        VARCHAR2          40   1
UT_TEST_IMPL                   Yes               4 created_on*                    TIMESTAMP(6)      11
                                                   dynamic_value_query            CLOB            4000
                                                   expected_return                VARCHAR2          20
                                                   expected_return_error          VARCHAR2         200
                                                   lib_dyn_query_id               VARCHAR2          40    1
                                                   name*                          VARCHAR2         120  2
                                                   updated_by*                    VARCHAR2         120
                                                   updated_on*                    TIMESTAMP(6)      11
                                                   ut_id*                         VARCHAR2          40  11
                                                   uti_id*                        VARCHAR2          40 1
UT_TEST_IMPL_ARGUMENTS         Yes               3 arg_id*                        VARCHAR2          40 21
                                                   created_on*                    TIMESTAMP(6)      11
                                                   input_value                    CLOB            4000
                                                   output_value                   CLOB            4000
                                                   test_outval*                   NUMBER            22
                                                   updated_by*                    VARCHAR2         120
                                                   updated_on*                    TIMESTAMP(6)      11
                                                   uti_id*                        VARCHAR2          40 1 1
UT_TEST_IMPL_ARG_RESULTS       Yes               4 arg_id*                        VARCHAR2          40  1
                                                   created_on*                    TIMESTAMP(6)      11
                                                   message                        VARCHAR2        2000
                                                   name                           VARCHAR2         120
                                                   run_date*                      TIMESTAMP(6)      11
                                                   status*                        VARCHAR2          20
                                                   updated_by*                    VARCHAR2         120
                                                   updated_on*                    TIMESTAMP(6)      11
                                                   uti_id*                        VARCHAR2          40 1
                                                   utiar_id*                      VARCHAR2          40    1
                                                   utir_id                        VARCHAR2          40   1
UT_TEST_IMPL_RESULTS           Yes               3 created_on*                    TIMESTAMP(6)      11
                                                   duration                       NUMBER            22
                                                   end_time                       VARCHAR2          20
                                                   message                        VARCHAR2        2000
                                                   name*                          VARCHAR2         120
                                                   op_call_duration               NUMBER            22
                                                   op_call_end_time               VARCHAR2          20
                                                   op_call_message                VARCHAR2        2000
                                                   op_call_start_time             VARCHAR2          20
                                                   op_call_status                 VARCHAR2          20
                                                   run_date*                      TIMESTAMP(6)      11
                                                   start_time                     VARCHAR2          20
                                                   startup_duration               NUMBER            22
                                                   startup_end_time               VARCHAR2          20
                                                   startup_message                VARCHAR2        2000
                                                   startup_start_time             VARCHAR2          20
                                                   startup_status                 VARCHAR2          20
                                                   status*                        VARCHAR2          20
                                                   teardown_duration              NUMBER            22
                                                   teardown_end_time              VARCHAR2          20
                                                   teardown_message               VARCHAR2        2000
                                                   teardown_start_time            VARCHAR2          20
                                                   teardown_status                VARCHAR2          20
                                                   updated_by*                    VARCHAR2         120
                                                   updated_on*                    TIMESTAMP(6)      11
                                                   uti_id*                        VARCHAR2          40  1
                                                   utir_id*                       VARCHAR2          40 1
                                                   utr_id*                        VARCHAR2          40   1
UT_TEST_IMPL_VAL_RESULTS       Yes               4 created_on*                    TIMESTAMP(6)      11
                                                   message                        VARCHAR2        2000
                                                   run_date                       TIMESTAMP(6)      11
                                                   status*                        VARCHAR2          20
                                                   updated_by*                    VARCHAR2         120
                                                   updated_on*                    TIMESTAMP(6)      11
                                                   uti_id*                        VARCHAR2          40  1
                                                   utir_id                        VARCHAR2          40   1
                                                   utivr_id*                      VARCHAR2          40    1
                                                   val_duration                   NUMBER            22
                                                   val_end_time                   VARCHAR2          20
                                                   val_id*                        VARCHAR2          40 1
                                                   val_start_time                 VARCHAR2          20
                                                   val_type*                      VARCHAR2          40
UT_TEST_RESULTS                Yes               2 connection_name                VARCHAR2         120
                                                   coverage*                      NUMBER            22
                                                   created_on*                    TIMESTAMP(6)      11
                                                   message                        VARCHAR2        2000
                                                   name*                          VARCHAR2         120
                                                   run_date*                      TIMESTAMP(6)      11
                                                   status*                        VARCHAR2          20
                                                   test_user_name                 VARCHAR2         120
                                                   updated_by*                    VARCHAR2         120
                                                   updated_on*                    TIMESTAMP(6)      11
                                                   ut_id*                         VARCHAR2          40  1
                                                   utr_id*                        VARCHAR2          40 1
UT_VALIDATIONS                 Yes               3 apply_validation*              NUMBER            22
                                                   created_on*                    TIMESTAMP(6)      11
                                                   index_no*                      NUMBER            22
                                                   lib_validation_id              VARCHAR2          40 1
                                                   updated_by*                    VARCHAR2         120
                                                   updated_on*                    TIMESTAMP(6)      11
                                                   uti_id*                        VARCHAR2          40   1
                                                   validation                     CLOB            4000
                                                   validation_id*                 VARCHAR2          40  1

252 rows selected.

SQL> 
SQL> BREAK ON table_name ON index_name
SQL> PROMPT Indexes
Indexes
SQL> SELECT  atb.table_name, ind.index_name||CASE ind.uniqueness WHEN 'UNIQUE' THEN '*' END index_name,
  2   aic.column_position "Seq", Lower(aic.column_name) column_name
  3    FROM all_tables      atb
  4    LEFT JOIN all_indexes      ind
  5           ON ind.table_name   = atb.table_name
  6          AND ind.table_owner    = atb.owner
  7         LEFT JOIN all_ind_columns aic
  8                ON aic.index_name    = ind.index_name
  9               AND aic.index_owner = ind.owner
 10   WHERE atb.table_name      LIKE Upper('&tab'||'%')
 11     AND atb.table_name      NOT LIKE '%$%'
 12     AND atb.table_name      NOT LIKE '%&nottab1%'
 13     AND atb.table_name      NOT LIKE '%&nottab2%'
 14     AND atb.owner     LIKE Upper('&owner')
 15   ORDER BY 1, 2, 3;

TABLE_NAME                     INDEX_NAME                       Seq COLUMN_NAME
------------------------------ ------------------------------- ---- ------------------------------
UT_LIB_DYN_QUERIES             UT_LIB_DYN_QUERIES_NAME*           1 lib_dyn_query_name
                               UT_LIB_DYN_QUERIES_PK*             1 lib_dyn_query_id
UT_LIB_STARTUPS                UT_LIB_STARTUPS_NAME*              1 lib_startup_name
                               UT_LIB_STARTUPS_PK*                1 lib_startup_id
UT_LIB_TEARDOWNS               UT_LIB_TEARDOWNS_NAME*             1 lib_teardown_name
                               UT_LIB_TEARDOWNS_PK*               1 lib_teardown_id
UT_LIB_VALIDATIONS             UT_LIB_VALIDATIONS_NAME*           1 lib_validation_name
                               UT_LIB_VALIDATIONS_PK*             1 lib_validation_id
UT_LOOKUP_CATEGORIES           UT_LOOKUP_CATEGORIES_PK*           1 id
                               UT_LOOKUP_CATEGORIES_UK1*          1 name
UT_LOOKUP_DATATYPES            UT_LOOKUP_DATATYPES_PK*            1 id
                               UT_LOOKUP_DATATYPES_UK1*           1 cat_id
                                                                  2 type_string
                               UT_LOOKUP_DTS_CAT_ID_IX            1 cat_id
UT_LOOKUP_VALUES               UT_LOOKUP_VALUES_PK*               1 id
                               UT_LOOKUP_VALUES_UT_LOOKU_IX       1 data_id
UT_METADATA                    UT_METADATA_PK*                    1 name
UT_STARTUPS                    UT_STARTUPS_LIB_VALIDN_IX          1 lib_startup_id
                               UT_STARTUPS_PK*                    1 startup_id
                               UT_STARTUPS_SUITE_IX               1 ut_sid
                               UT_STARTUPS_TEST_IX                1 ut_id
UT_SUITE                       UT_SUITE_PK*                       1 ut_sid
UT_SUITE_ITEMS                 UT_SUITE_ITEMS_IX1                 1 ut_sid
                               UT_SUITE_ITEMS_IX2                 1 ut_nsid
                               UT_SUITE_ITEMS_IX3                 1 ut_id
                               UT_SUITE_ITEMS_UK*                 1 ut_sid
                                                                  2 ut_id
                                                                  3 ut_nsid
UT_SUITE_ITEM_RESULTS          UT_SUITE_ITEM_RESULTS_FK1          1 utsr_id
                               UT_SUITE_ITEM_RESULTS_FK2          1 utr_id
                               UT_SUITE_ITEM_RESULTS_FK3          1 utr_nsid
                               UT_SUITE_ITEM_RESULTS_UK*          1 utsr_id
                                                                  2 utr_id
                                                                  3 utr_nsid
UT_SUITE_RESULTS               UT_SUITE_RESULTS_IX                1 ut_sid
                               UT_SUITE_RESULTS_PK*               1 utsr_id
UT_TEARDOWNS                   UT_TEARDOWNS_LIB_VALIDN_IX         1 lib_teardown_id
                               UT_TEARDOWNS_PK*                   1 teardown_id
                               UT_TEARDOWNS_SUITE_IX              1 ut_sid
                               UT_TEARDOWNS_TEST_IX               1 ut_id
UT_TEST                        UT_TEST_PK*                        1 ut_id
                               UT_TEST_UK1*                       1 name
UT_TEST_ARGUMENTS              UT_TEST_ARGUMENTS_IX               1 ut_id
                               UT_TEST_ARGUMENTS_PK*              1 arg_id
UT_TEST_COVERAGE_STATS         UT_TEST_COVERAGE_STATS_PK*         1 utc_id
                               UT_TEST_COV_STATS_UT_T_IX1         1 uti_id
                               UT_TEST_COV_STATS_UT_T_IX2         1 utir_id
UT_TEST_IMPL                   UT_TEST_IMPL_PK*                   1 uti_id
                               UT_TEST_IMPL_UK1*                  1 ut_id
                                                                  2 name
                               UT_TEST_IMPL_UT_TEST_IX            1 ut_id
                               UT_TEST_LIB_DYN_QUERIES_IX         1 lib_dyn_query_id
UT_TEST_IMPL_ARGUMENTS         UT_TEST_IMPL_ARGUMENTS_PK*         1 uti_id
                                                                  2 arg_id
                               UT_TEST_IMPL_ARGUMENTS_UT_IX1      1 arg_id
                               UT_TEST_IMPL_ARGUMENTS_UT_IX2      1 uti_id
UT_TEST_IMPL_ARG_RESULTS       UT_TEST_IMPL_ARG_RESULTS_IX1       1 uti_id
                               UT_TEST_IMPL_ARG_RESULTS_IX2       1 arg_id
                               UT_TEST_IMPL_ARG_RESULTS_IX3       1 utir_id
                               UT_TEST_IMPL_ARG_RESULTS_PK*       1 utiar_id
UT_TEST_IMPL_RESULTS           UT_TEST_IMPL_RESULTS_PK*           1 utir_id
                               UT_TEST_IMPL_RESULTS_UT_T_IX1      1 uti_id
                               UT_TEST_IMPL_RESULTS_UT_T_IX2      1 utr_id
UT_TEST_IMPL_VAL_RESULTS       UT_TEST_IMPL_VAL_RES_IX1           1 val_id
                               UT_TEST_IMPL_VAL_RES_IX2           1 uti_id
                               UT_TEST_IMPL_VAL_RES_IX3           1 utir_id
                               UT_TEST_IMPL_VAL_RES_PK*           1 utivr_id
UT_TEST_RESULTS                UT_TEST_RESULTS_PK*                1 utr_id
                               UT_TEST_RESULTS_UT_TEST_IX1        1 ut_id
UT_VALIDATIONS                 UT_VALIDATIONS_LIB_VALIDN_IX       1 lib_validation_id
                               UT_VALIDATIONS_PK*                 1 validation_id
                               UT_VALIDATIONS_TEST_IMPL_IX        1 uti_id

72 rows selected.

SQL> 
SQL> PROMPT Tables with no Who columns / No unique indexes/ Id only unique indexes
Tables with no Who columns / No unique indexes/ Id only unique indexes
SQL> SELECT atb.table_name,  CASE WHEN atc_w.column_name IS NULL THEN 'X' END "Who?",
  2                          CASE WHEN uni.maxind IS NULL THEN 'X' END "No UID?",
  3                          CASE WHEN uni.maxind = 1 THEN 'X' END "Id UID?"
  4    FROM all_tables      atb
  5    LEFT JOIN all_tab_columns    atc_w
  6           ON atc_w.table_name   = atb.table_name
  7          AND atc_w.owner      = atb.owner
  8          AND atc_w.column_name    = 'CREATED_BY'
  9    LEFT JOIN (SELECT ind.table_name, ind.table_owner, Max(CASE WHEN Substr(aic.column_name, Length(aic.column_name)-2) IS NULL THEN 0
 10                     WHEN Substr(aic.column_name, Length(aic.column_name)-2) = '_ID' THEN 1
 11                     ELSE 2 END) maxind
 12                 FROM all_indexes ind
 13                 LEFT JOIN all_ind_columns aic
 14                        ON aic.index_name         = ind.index_name
 15                       AND aic.index_owner        = ind.owner
 16                       AND aic.column_position    = 1
 17                WHERE ind.uniqueness   = 'UNIQUE'
 18                GROUP BY ind.table_name, ind.table_owner) uni
 19           ON uni.table_name     = atb.table_name
 20          AND uni.table_owner      = atb.owner
 21   WHERE atb.table_name      LIKE Upper('&tab'||'%')
 22     AND atb.table_name      NOT LIKE '%$%'
 23     AND atb.table_name      NOT LIKE '%&nottab1%'
 24     AND atb.table_name      NOT LIKE '%&nottab2%'
 25     AND atb.owner     LIKE Upper('&owner')
 26     AND (atc_w.column_name    IS NULL OR uni.maxind IN (0, 1))
 27   ORDER BY 1;

TABLE_NAME                     Who?  No UID? Id UID?
------------------------------ ----- ------- -------
UT_METADATA                    X
UT_STARTUPS                                  X
UT_SUITE_ITEM_RESULTS                        X
UT_SUITE_RESULTS                             X
UT_TEARDOWNS                                 X
UT_TEST_ARGUMENTS                            X
UT_TEST_COVERAGE_STATS                       X
UT_TEST_IMPL                                 X
UT_TEST_IMPL_ARGUMENTS                       X
UT_TEST_IMPL_ARG_RESULTS                     X
UT_TEST_IMPL_RESULTS                         X
UT_TEST_IMPL_VAL_RESULTS                     X
UT_TEST_RESULTS                              X
UT_VALIDATIONS                               X

14 rows selected.

SQL> SELECT 'End: '||name "Database", '&owner' "Schema", '&tab' "Prefix", To_Char(SYSDATE,'DD-MON-YYYY HH24:MI:SS') FROM v$database;

Database             Schema          Prefix     TO_CHAR(SYSDATE,'DD-MON-YYYYH
-------------------- --------------- ---------- -----------------------------
End: XE              UT_REPOS        %          11-JUL-2015 13:21:35

HR Demo Schemas (v12)

I had to import the three schemas (HR, OE and PM) one at a time, merging with the previous one, am not sure if you can do it in one go, or if the diagram is affected by the order of import.

Data Modeler Diagram

HR

Manual Visio Diagram

For comparison, here is a manual diagram, deliberately omitting column and other detail. It was done as an illustration of the network analysis program output, and I therefore did not include useful information such as on relationship optionality.

Networks - HR, v1.0

Network Analysis Output

I copied this from my article above.

Network       #Links  #Nodes Lev  Node                                     Link
------------  ------  ------ ---  ---------------------------------------  -------------------------------
COUNTRIES|HR      21      16   0  COUNTRIES|HR                             ROOT
                               1  < LOCATIONS|HR                           loc_c_id_fk|hr
                               2    < DEPARTMENTS|HR                       dept_loc_fk|hr                                3      > EMPLOYEES|HR                       dept_mgr_fk|hr
                               4        < CUSTOMERS|OE                     customers_account_manager_fk|oe
                               5          < ORDERS|OE                      orders_customer_id_fk|oe                                6            > EMPLOYEES|HR*                orders_sales_rep_fk|oe
                               6            < ORDER_ITEMS|OE               order_items_order_id_fk|oe                                7              > PRODUCT_INFORMATION|OE     order_items_product_id_fk|oe
                               8                < INVENTORIES|OE           inventories_product_id_fk|oe                                9                  > WAREHOUSES|OE          inventories_warehouses_fk|oe
                              10                    > LOCATIONS|HR*        warehouses_location_fk|oe
                               8                < ONLINE_MEDIA|PM          loc_c_id_fk|pm
                               8                < PRINT_MEDIA|PM           printmedia_fk|pm
                               8                < PRODUCT_DESCRIPTIONS|OE  pd_product_id_fk|oe                                4        > DEPARTMENTS|HR*                  emp_dept_fk|hr
                               4        = EMPLOYEES|HR*                    emp_manager_fk|hr
                               4        > JOBS|HR                          emp_job_fk|hr
                               5          < JOB_HISTORY|HR                 jhist_job_fk|hr                                6            > DEPARTMENTS|HR*              jhist_dept_fk|hr
                               6            > EMPLOYEES|HR*                jhist_emp_fk|hr
                               1  > REGIONS|HR                             countr_reg_fk|hr

Schema Metadata Analysis Report – not included

Apex Schema – APEX_040000 (v11.22)
This imported quickly, with 425 tables. However, the diagram was not so useful. Trying to print it to .png or .jpg (via File/Print Diagram/To Image File) silently failed; printing to .pdf worked, but the reader opens at a zoom level of 1.41% and it’s not practical to navigate the links. Maybe text-based analysis reports are more useful for the larger schemas.

Data Modeler Diagram

Here is a screenshot of the modeler diagram:
APEX_040000_DM-Shot
Network Analysis Output

I include my standard summary listings in this case, showing that the schema splits into 21 sub-networks, with 298 tables having foreign key links, the remaining 127 being thereby excluded from this report.

SQL> @..\sql\R_Net
Network detail

Network                                     #Links  #Nodes    Lev Node                                                                   Link
------------------------------------------ ------- ------- ------ ---------------------------------------------------------------------- ------------------------------------------
APEX$_WS_FILES|APEX_040000                       4       5      0 APEX$_WS_FILES|APEX_040000                                             ROOT
                                                                1 > APEX$_WS_ROWS|APEX_040000                                            apex$_ws_files_fk|apex_040000
                                                                2   < APEX$_WS_LINKS|APEX_040000                                         apex$_ws_links_fk|apex_040000
                                                                2   < APEX$_WS_NOTES|APEX_040000                                         apex$_ws_notes_fk|apex_040000
                                                                2   < APEX$_WS_TAGS|APEX_040000                                          apex$_ws_tags_fk|apex_040000
WWV_FLOWS|APEX_040000                          286     236      0 WWV_FLOWS|APEX_040000                                                  ROOT
                                                                1 < WWV_FLOW_ALTERNATE_CONFIG|APEX_040000                                wwv_flow_alt_cfg_fk|apex_040000
                                                                2   < WWV_FLOW_ALT_CONFIG_DETAIL|APEX_040000                             wwv_flow_alt_cfg_d_fk|apex_040000
                                                                1 < WWV_FLOW_APP_COMMENTS|APEX_040000                                    wwv_flow_app_comments_fk|apex_040000
                                                                1 < WWV_FLOW_BANNER|APEX_040000                                          wwv_flow_banner_fk|apex_040000
                                                                1 < WWV_FLOW_BUTTON_TEMPLATES|APEX_040000                                wwv_flow_buttont_fk|apex_040000
                                                                1 < WWV_FLOW_CALS|APEX_040000                                            wwv_flow_cal_to_flow_fk|apex_040000
                                                                2   > WWV_FLOW_PAGE_PLUGS|APEX_040000                                    wwv_flow_plug_calendar_fk|apex_040000
                                                                3     > WWV_FLOWS|APEX_040000*                                           wwv_flow_plug_to_flow_fk|apex_040000
                                                                3     < WWV_FLOW_FLASH_CHARTS_5|APEX_040000                              wwv_flow_flash_charts_5_fk2|apex_040000
                                                                4       > WWV_FLOWS|APEX_040000*                                         wwv_flow_flash_charts_5_fk|apex_040000
                                                                4       < WWV_FLOW_FLASH_CHART5_SERIES|APEX_040000                       wwv_flow_flash_5_series_fk|apex_040000
                                                                4       < WWV_FLOW_FLASH_CHARTS_5_DASH|APEX_040000                       wwv_flow_flash_charts5_dash_fk|apex_040000
                                                                3     < WWV_FLOW_FLASH_CHARTS|APEX_040000                                wwv_flow_flash_charts_fk2|apex_040000
                                                                4       > WWV_FLOWS|APEX_040000*                                         wwv_flow_flash_charts_fk|apex_040000
                                                                4       < WWV_FLOW_FLASH_CHART_SERIES|APEX_040000                        wwv_flow_flash_chart_series_fk|apex_040000
                                                                3     < WWV_FLOW_PAGE_DA_ACTIONS|APEX_040000                             wwv_flow_page_da_a_ar_fk|apex_040000
                                                                4       > WWV_FLOW_PAGE_DA_EVENTS|APEX_040000                            wwv_flow_page_da_a_evnt_fk|apex_040000
                                                                5         > WWV_FLOWS|APEX_040000*                                       wwv_flow_page_da_e_flow_fk|apex_040000
                                                                5         > WWV_FLOW_PAGE_PLUGS|APEX_040000*                             wwv_flow_page_da_e_tr_fk|apex_040000
                                                                5         > WWV_FLOW_STEPS|APEX_040000                                   wwv_flow_page_da_e_page_fk|apex_040000
                                                                6           > WWV_FLOWS|APEX_040000*                                     wwv_flow_steps_fk|apex_040000
                                                                6           < WWV_FLOW_PAGE_DA_ACTIONS|APEX_040000*                      wwv_flow_page_da_a_page_fk|apex_040000
                                                                6           < WWV_FLOW_PAGE_PLUGS|APEX_040000*                           wwv_flow_plug_to_page_fk|apex_040000
                                                                6           < WWV_FLOW_STEP_BRANCHES|APEX_040000                         wwv_flow_step_branches_fk2|apex_040000
                                                                7             > WWV_FLOWS|APEX_040000*                                   wwv_flow_step_branches_fk|apex_040000
                                                                7             < WWV_FLOW_STEP_BRANCH_ARGS|APEX_040000                    wwv_flow_step_branch_args_fk|apex_040000
                                                                6           < WWV_FLOW_STEP_BUTTONS|APEX_040000                          wwv_flow_step_buttons_fk2|apex_040000
                                                                7             > WWV_FLOWS|APEX_040000*                                   wwv_flow_step_buttons_fk1|apex_040000
                                                                7             > WWV_FLOW_PAGE_PLUGS|APEX_040000*                         wwv_flow_step_buttons_plug_fk|apex_040000
                                                                6           < WWV_FLOW_STEP_COMPUTATIONS|APEX_040000                     wwv_flow_step_comp_fk2|apex_040000
                                                                7             > WWV_FLOWS|APEX_040000*                                   wwv_flow_step_comp_fk|apex_040000
                                                                6           < WWV_FLOW_STEP_ITEMS|APEX_040000                            wwv_flow_step_items_fk2|apex_040000
                                                                7             > WWV_FLOWS|APEX_040000*                                   wwv_flow_step_items_fk|apex_040000
                                                                7             > WWV_FLOW_PAGE_PLUGS|APEX_040000*                         wwv_flow_step_items_plug_fk|apex_040000
                                                                7             < WWV_FLOW_STEP_ITEM_HELP|APEX_040000                      wwv_flow_item_helptext_fk|apex_040000
                                                                8               > WWV_FLOWS|APEX_040000*                                 wwv_flow_page_helptext_fk|apex_040000
                                                                6           < WWV_FLOW_STEP_PROCESSING|APEX_040000                       wwv_flow_step_proc_fk2|apex_040000
                                                                7             > WWV_FLOWS|APEX_040000*                                   wwv_flow_step_proc_fk|apex_040000
                                                                7             < WWV_FLOW_WS_PROCESS_PARMS_MAP|APEX_040000                wwv_flow_ws_map_fk2|apex_040000
                                                                8               > WWV_FLOW_WS_PARAMETERS|APEX_040000                     wwv_flows_ws_map_fk1|apex_040000
                                                                9                 > WWV_FLOW_WS_OPERATIONS|APEX_040000                   wwv_flow_ws_parms_fk|apex_040000
                                                               10                   > WWV_FLOW_SHARED_WEB_SERVICES|APEX_040000           wwv_flow_ws_opers_fk|apex_040000
                                                               11                     > WWV_FLOWS|APEX_040000*                           wwv_flow_ws_fk|apex_040000
                                                                6           < WWV_FLOW_STEP_VALIDATIONS|APEX_040000                      wwv_flow_step_val_fk2|apex_040000
                                                                7             > WWV_FLOWS|APEX_040000*                                   wwv_flow_step_val_fk|apex_040000
                                                                7             > WWV_FLOW_PAGE_PLUGS|APEX_040000*                         wwv_flow_step_val_to_reg_fk|apex_040000
                                                                3     < WWV_FLOW_PAGE_GENERIC_ATTR|APEX_040000                           wwv_flow_genattr_to_region_fk|apex_040000
                                                                3     = WWV_FLOW_PAGE_PLUGS|APEX_040000*                                 wwv_flow_plug_parent_fk|apex_040000
                                                                3     < WWV_FLOW_QUERY_DEFINITION|APEX_040000                            query_def_to_region_fk|apex_040000
                                                                4       < WWV_FLOW_QUERY_COLUMN|APEX_040000                              query_column_to_query_fk|apex_040000
                                                                5         > WWV_FLOW_QUERY_OBJECT|APEX_040000                            query_column_to_qry_object_fk|apex_040000
                                                                6           > WWV_FLOW_QUERY_DEFINITION|APEX_040000*                     query_object_to_query_fk|apex_040000
                                                                4       < WWV_FLOW_QUERY_CONDITION|APEX_040000                           query_condition_to_query_fk|apex_040000
                                                                3     < WWV_FLOW_REGION_CHART_SER_ATTR|APEX_040000                       wwv_flow_seattr_to_region_fk|apex_040000
                                                                3     < WWV_FLOW_REGION_REPORT_COLUMN|APEX_040000                        report_column_to_region_fk|apex_040000
                                                                3     < WWV_FLOW_REGION_REPORT_FILTER|APEX_040000                        sys_c004963|apex_040000
                                                                3     < WWV_FLOW_REGION_UPD_RPT_COLS|APEX_040000                         wwv_flow_urc_to_plug_fk|apex_040000
                                                                4       > WWV_FLOWS|APEX_040000*                                         wwv_flow_urc_to_flow_fk|apex_040000
                                                                3     < WWV_FLOW_TREE_REGIONS|APEX_040000                                wwv_flow_treeregion_fk2|apex_040000
                                                                4       > WWV_FLOWS|APEX_040000*                                         wwv_flow_treeregion_fk|apex_040000
                                                                3     < WWV_FLOW_WORKSHEETS|APEX_040000                                  wwv_flow_worksheets_reg_fk|apex_040000
                                                                4       > WWV_FLOWS|APEX_040000*                                         wwv_flow_worksheets_flow_fk|apex_040000
                                                                4       < WWV_FLOW_WORKSHEET_COLUMNS|APEX_040000                         wwv_flow_worksheet_columns_fk|apex_040000
                                                                5         > WWV_FLOWS|APEX_040000*                                       wwv_flow_worksheet_col_fk|apex_040000
                                                                5         > WWV_FLOW_WORKSHEET_COL_GROUPS|APEX_040000                    wwv_flow_worksheet_col_grps_fk|apex_040000
                                                                6           > WWV_FLOWS|APEX_040000*                                     wwv_flow_worksheet_col_grp_fk|apex_040000
                                                                6           > WWV_FLOW_WORKSHEETS|APEX_040000*                           wwv_flow_worksheet_col_grws_fk|apex_040000
                                                                6           > WWV_FLOW_WS_WEBSHEET_ATTR|APEX_040000                      wwv_flow_worksheet_col_grp_fk2|apex_040000
                                                                7             > WWV_FLOW_WORKSHEETS|APEX_040000*                         wwwv_flow_ws_websheet_attr_fk|apex_040000
                                                                7             < WWV_FLOW_WORKSHEET_COLUMNS|APEX_040000*                  wwv_flow_worksheet_col_fk2|apex_040000
                                                                7             < WWV_FLOW_WORKSHEET_COMPUTATION|APEX_040000               wwv_flow_ws_computation_fk|apex_040000
                                                                8               > WWV_FLOW_WORKSHEET_RPTS|APEX_040000                    wwv_flow_ws_comp_cols_fk|apex_040000
                                                                9                 > WWV_FLOW_WORKSHEET_CATEGORIES|APEX_040000            wwv_flow_worksheet_rpts_fk|apex_040000
                                                                9                 < WWV_FLOW_WORKSHEET_CONDITIONS|APEX_040000            wwv_flow_worksheet_cond_fk|apex_040000
                                                               10                   > WWV_FLOW_WS_WEBSHEET_ATTR|APEX_040000*             wwv_flow_ws_condition_fk|apex_040000
                                                                9                 < WWV_FLOW_WORKSHEET_GROUP_BY|APEX_040000              wwv_flow_ws_groupby_fk2|apex_040000
                                                               10                   > WWV_FLOW_WS_WEBSHEET_ATTR|APEX_040000*             wwv_flow_ws_groupby_fk|apex_040000
                                                                9                 < WWV_FLOW_WORKSHEET_NOTIFY|APEX_040000                wwv_flow_worksheet_notify_fk2|apex_040000
                                                               10                   > WWV_FLOW_WORKSHEETS|APEX_040000*                   wwv_flow_worksheet_notify_fk|apex_040000
                                                               10                   > WWV_FLOW_WS_WEBSHEET_ATTR|APEX_040000*             wwv_flow_worksheet_notify_fk4|apex_040000
                                                                9                 > WWV_FLOW_WS_WEBSHEET_ATTR|APEX_040000*               wwv_flow_ws_rpt_fk|apex_040000
                                                                7             < WWV_FLOW_WORKSHEET_LOVS|APEX_040000                      wwv_flow_worksheet_lovs_fk2|apex_040000
                                                                8               > WWV_FLOW_WORKSHEETS|APEX_040000*                       wwv_flow_worksheet_lovs_fk|apex_040000
                                                                8               < WWV_FLOW_WORKSHEET_LOV_ENTRIES|APEX_040000             wwv_flow_worksheet_lov_ent_fk2|apex_040000
                                                                9                 > WWV_FLOW_WORKSHEETS|APEX_040000*                     wwv_flow_worksheet_lov_ent_fk|apex_040000
                                                                9                 > WWV_FLOW_WS_WEBSHEET_ATTR|APEX_040000*               wwv_flow_worksheet_lov_ent_fk3|apex_040000
                                                                7             > WWV_FLOW_WS_APPLICATIONS|APEX_040000                     wwv_flow_ws_websheet_attr_fk2|apex_040000
                                                                8               < WWV_FLOW_WS_APP_SUG_OBJECTS|APEX_040000                wwv_flow_ws_app_so_fk1|apex_040000
                                                                8               < WWV_FLOW_WS_COL_VALIDATIONS|APEX_040000                wwv_flow_ws_col_val_fk3|apex_040000
                                                                9                 > WWV_FLOW_WORKSHEETS|APEX_040000*                     wwv_flow_ws_col_val_fk|apex_040000
                                                                9                 > WWV_FLOW_WS_WEBSHEET_ATTR|APEX_040000*               wwv_flow_ws_col_val_fk2|apex_040000
                                                                8               < WWV_FLOW_WS_CUSTOM_AUTH_SETUPS|APEX_040000             wwv_flow_ws_auth_setups_fk|apex_040000
                                                                8               < WWV_FLOW_WS_WEBPAGES|APEX_040000                       wwv_flow_ws_webpages_fk|apex_040000
                                                                9                 = WWV_FLOW_WS_WEBPAGES|APEX_040000*                    wwv_flow_ws_webpages_fk2|apex_040000
                                                                1 < WWV_FLOW_CAL_TEMPLATES|APEX_040000                                   wwv_flow_cal_templ_to_flow_fk|apex_040000
                                                                1 < WWV_FLOW_COMPOUND_CONDITIONS|APEX_040000                             wwv_flow_comp_cond_fk|apex_040000
                                                                1 < WWV_FLOW_COMPUTATIONS|APEX_040000                                    wwv_flow_computations_fk|apex_040000
                                                                1 < WWV_FLOW_CUSTOM_AUTH_SETUPS|APEX_040000                              wwv_flow_auth_setups_fk|apex_040000
                                                                1 < WWV_FLOW_ENTRY_POINTS|APEX_040000                                    wwv_flow_entry_points_fk|apex_040000
                                                                2   < WWV_FLOW_ENTRY_POINT_ARGS|APEX_040000                              wwv_flow_entry_point_args_fk|apex_040000
                                                                1 < WWV_FLOW_FIELD_TEMPLATES|APEX_040000                                 wwv_flow_field_temp_f_fk|apex_040000
                                                                1 < WWV_FLOW_ICON_BAR_ATTRIBUTES|APEX_040000                             wwv_flow_iconbarattr_fk|apex_040000
                                                                1 < WWV_FLOW_ICON_BAR|APEX_040000                                        wwv_flow_icon_bar_fk|apex_040000
                                                                1 < WWV_FLOW_INSTALL_BUILD_OPT|APEX_040000                               wwv_flow_install_build_opt_fk|apex_040000
                                                                2   > WWV_FLOW_INSTALL|APEX_040000                                       wwv_flow_install_build_opt_fk3|apex_040000
                                                                3     > WWV_FLOWS|APEX_040000*                                           wwv_flow_install_fk|apex_040000
                                                                3     < WWV_FLOW_INSTALL_CHECKS|APEX_040000                              wwv_flow_install_checks_fk3|apex_040000
                                                                4       > WWV_FLOWS|APEX_040000*                                         wwv_flow_install_checks_fk|apex_040000
                                                                3     < WWV_FLOW_INSTALL_SCRIPTS|APEX_040000                             wwv_flow_install_scripts_fk3|apex_040000
                                                                4       > WWV_FLOWS|APEX_040000*                                         wwv_flow_install_scripts_fk|apex_040000
                                                                2   > WWV_FLOW_PATCHES|APEX_040000                                       wwv_flow_install_build_opt_fk4|apex_040000
                                                                3     > WWV_FLOWS|APEX_040000*                                           wwv_flow_patches_fk|apex_040000
                                                                1 < WWV_FLOW_ITEMS|APEX_040000                                           wwv_flow_items_fk|apex_040000
                                                                1 < WWV_FLOW_LANGUAGE_MAP|APEX_040000                                    wwv_flow_lang_flow_id_fk|apex_040000
                                                                1 < WWV_FLOW_LISTS_OF_VALUES$|APEX_040000                                wwv_flow_lov_fk|apex_040000
                                                                2   < WWV_FLOW_LIST_OF_VALUES_DATA|APEX_040000                           wwv_flow_lov_data_fk|apex_040000
                                                                1 < WWV_FLOW_LISTS|APEX_040000                                           wwv_flow_lists_flow_fk|apex_040000
                                                                2   < WWV_FLOW_LIST_ITEMS|APEX_040000                                    wwv_flow_list_items_fk|apex_040000
                                                                3     = WWV_FLOW_LIST_ITEMS|APEX_040000*                                 parent_list_item_fk|apex_040000
                                                                1 < WWV_FLOW_LIST_TEMPLATES|APEX_040000                                  wwv_flow_list_template_fk|apex_040000
                                                                1 < WWV_FLOW_LOCK_PAGE|APEX_040000                                       sys_c004810|apex_040000
                                                                1 < WWV_FLOW_MENUS|APEX_040000                                           wwv_flow_menus_flow_fk|apex_040000
                                                                2   < WWV_FLOW_MENU_OPTIONS|APEX_040000                                  wwv_flow_opt_menus_fk|apex_040000
                                                                1 < WWV_FLOW_MENU_TEMPLATES|APEX_040000                                  wwv_flow_menus_t_flow_fk|apex_040000
                                                                1 < WWV_FLOW_MESSAGES$|APEX_040000                                       wwv_flow_messages_fk|apex_040000
                                                                1 < WWV_FLOW_PAGES_RESERVED|APEX_040000                                  wwv_flow_pages_reserved_fk|apex_040000
                                                                1 < WWV_FLOW_PAGE_CACHE|APEX_040000                                      wwv_flow_page_cache_fk|apex_040000
                                                                2   < WWV_FLOW_PAGE_CODE_CACHE|APEX_040000                               wwv_flow_page_code_cache_fk|apex_040000
                                                                1 < WWV_FLOW_PAGE_GROUPS|APEX_040000                                     sys_c004993|apex_040000
                                                                1 < WWV_FLOW_PAGE_PLUG_TEMPLATES|APEX_040000                             wwv_flow_plug_temp_fk|apex_040000
                                                                1 < WWV_FLOW_PAGE_SUBMISSIONS|APEX_040000                                wwv_flow_page_sub_fk|apex_040000
                                                                2   > WWV_FLOW_SESSIONS$|APEX_040000                                     wwv_flow_page_sub_sess_fk|apex_040000
                                                                3     < WWV_FLOW_COLLECTIONS$|APEX_040000                                wwv_flow_collection_fk|apex_040000
                                                                4       < WWV_FLOW_COLLECTION_MEMBERS$|APEX_040000                       wwv_flow_collection_membes_fk|apex_040000
                                                                3     < WWV_FLOW_DATA|APEX_040000                                        wwv_flow_data_fk|apex_040000
                                                                3     < WWV_FLOW_REQUEST_VERIFICATIONS|APEX_040000                       wwv_flow_request_verif_fk|apex_040000
                                                                3     < WWV_FLOW_SC_TRANS|APEX_040000                                    wwv_flow_sc_trans_fk2|apex_040000
                                                                3     < WWV_FLOW_TREE_STATE|APEX_040000                                  wwv_flow_tree_state$fk|apex_040000
                                                                1 < WWV_FLOW_PLUGINS|APEX_040000                                         wwv_flow_plugin_flow_fk|apex_040000
                                                                2   < WWV_FLOW_PLUGIN_ATTRIBUTES|APEX_040000                             wwv_flow_plugin_attr_parent_fk|apex_040000
                                                                3     > WWV_FLOWS|APEX_040000*                                           wwv_flow_plugin_attr_flow_fk|apex_040000
                                                                3     = WWV_FLOW_PLUGIN_ATTRIBUTES|APEX_040000*                          wwv_flow_plugin_attr_depend_fk|apex_040000
                                                                3     < WWV_FLOW_PLUGIN_ATTR_VALUES|APEX_040000                          wwv_flow_plugin_attrv_attr_fk|apex_040000
                                                                4       > WWV_FLOWS|APEX_040000*                                         wwv_flow_plugin_attrv_flow_fk|apex_040000
                                                                2   < WWV_FLOW_PLUGIN_EVENTS|APEX_040000                                 wwv_flow_plugin_evnt_parent_fk|apex_040000
                                                                3     > WWV_FLOWS|APEX_040000*                                           wwv_flow_plugin_evnt_flow_fk|apex_040000
                                                                2   < WWV_FLOW_PLUGIN_FILES|APEX_040000                                  wwv_flow_plugin_file_parent_fk|apex_040000
                                                                3     > WWV_FLOWS|APEX_040000*                                           wwv_flow_plugin_file_flow_fk|apex_040000
                                                                1 < WWV_FLOW_POPUP_LOV_TEMPLATE|APEX_040000                              wwv_flow_fk_poplov_temp|apex_040000
                                                                1 < WWV_FLOW_PROCESSING|APEX_040000                                      wwv_flow_processing_fk|apex_040000
                                                                1 < WWV_FLOW_REPORT_LAYOUTS|APEX_040000                                  wwv_flow_report_layoutse_fk|apex_040000
                                                                1 < WWV_FLOW_REQUIRED_ROLES|APEX_040000                                  wwv_flow_req_roles_fk|apex_040000
                                                                1 < WWV_FLOW_ROW_TEMPLATES|APEX_040000                                   wwv_flow_row_template_fk|apex_040000
                                                                1 < WWV_FLOW_SECURITY_SCHEMES|APEX_040000                                wwv_flow_sec_schemes_fk|apex_040000
                                                                1 < WWV_FLOW_SHARED_QRY_SQL_STMTS|APEX_040000                            wwv_flow_sqry_sql_flow_fk|apex_040000
                                                                2   > WWV_FLOW_SHARED_QUERIES|APEX_040000                                wwv_flow_sqry_sql_sqry_fk|apex_040000
                                                                3     > WWV_FLOWS|APEX_040000*                                           wwv_flow_shdqry_flow_fk|apex_040000
                                                                1 < WWV_FLOW_SHORTCUTS|APEX_040000                                       wwv_flow_shortcuts_to_flow_fk|apex_040000
                                                                1 < WWV_FLOW_TABS|APEX_040000                                            wwv_flow_tabs_fk|apex_040000
                                                                1 < WWV_FLOW_TEMPLATES|APEX_040000                                       wwv_flow_templates_fk|apex_040000
                                                                1 < WWV_FLOW_TEMPLATE_PREFERENCES|APEX_040000                            wwv_flow_templ_pref_fk|apex_040000
                                                                1 < WWV_FLOW_THEMES|APEX_040000                                          wwv_flow_themes_2f_fk|apex_040000
                                                                1 < WWV_FLOW_TOPLEVEL_TABS|APEX_040000                                   wwv_flow_toplev_tab_fk|apex_040000
                                                                1 < WWV_FLOW_TRANSLATABLE_TEXT$|APEX_040000                              wwv_flow_trans_text_fk|apex_040000
                                                                1 < WWV_FLOW_TREES|APEX_040000                                           wwv_flow_tree_fk|apex_040000
                                                                1 < WWV_FLOW_VALIDATIONS|APEX_040000                                     wwv_flow_val_fk|apex_040000
                                                                1 < WWV_MIG_GENERATED_APPLICATIONS|APEX_040000                           wwv_mig_gen_app_flow_id_fk|apex_040000
                                                                2   > WWV_MIG_PROJECTS|APEX_040000                                       wwv_mig_gen_app_proj_id_fk|apex_040000
                                                                3     < WWV_MIG_ACCESS|APEX_040000                                       wwv_mig_acc_fk|apex_040000
                                                                3     < WWV_MIG_FORMS|APEX_040000                                        wwv_mig_forms_project_id_fk|apex_040000
                                                                4       < WWV_MIG_FRM_MODULES|APEX_040000                                wwv_mig_frm_modules_file_id_fk|apex_040000
                                                                5         < WWV_MIG_FRM_FORMMODULES|APEX_040000                          wwv_mig_frm_frmmdl_mdl_id_fk|apex_040000
                                                                6           < WWV_MIG_FRM_ALERTS|APEX_040000                             wwv_mig_frm_alrt_frmmdl_id_fk|apex_040000
                                                                6           < WWV_MIG_FRM_ATTACHEDLIBRARY|APEX_040000                    wwv_mig_frm_atlib_frmmdl_id_fk|apex_040000
                                                                6           < WWV_MIG_FRM_BLOCKS|APEX_040000                             wwv_mig_frm_blk_frmmdl_id_fk|apex_040000
                                                                7             < WWV_MIG_FRM_BLK_DSA|APEX_040000                          wwv_mig_frm_blk_dsa_blk_id_fk|apex_040000
                                                                7             < WWV_MIG_FRM_BLK_DSC|APEX_040000                          wwv_mig_frm_blk_dsc_blk_id_fk|apex_040000
                                                                7             < WWV_MIG_FRM_BLK_ITEMS|APEX_040000                        wwv_mig_frm_bi_blk_id_fk|apex_040000
                                                                8               < WWV_MIG_FRM_BLK_ITEM_LIE|APEX_040000                   wwv_mig_frm_bi_lie_item_id_fk|apex_040000
                                                                8               < WWV_MIG_FRM_BLK_ITEM_RADIO|APEX_040000                 wwv_mig_frm_bir_item_id_fk|apex_040000
                                                                8               < WWV_MIG_FRM_BLK_ITEM_TRIGGERS|APEX_040000              wwv_mig_frm_bi_trg_item_id_fk|apex_040000
                                                                8               < WWV_MIG_FRM_REV_BLK_ITEMS|APEX_040000                  wwv_mig_frm_rev_bi_item_id_fk|apex_040000
                                                                7             < WWV_MIG_FRM_BLK_RELATIONS|APEX_040000                    wwv_mig_frm_blk_rel_blk_id_fk|apex_040000
                                                                7             < WWV_MIG_FRM_BLK_TRIGGERS|APEX_040000                     wwv_mig_frm_blk_trg_blk_id_fk|apex_040000
                                                                7             < WWV_MIG_FRM_REV_BLOCKS|APEX_040000                       wwv_mig_frm_rev_blocks_id_fk|apex_040000
                                                                6           < WWV_MIG_FRM_CANVAS|APEX_040000                             wwv_mig_frm_canvs_frmmdl_id_fk|apex_040000
                                                                7             < WWV_MIG_FRM_CNVS_GRAPHICS|APEX_040000                    wwv_mig_frm_cg_cnvs_id_fk|apex_040000
                                                                8               < WWV_MIG_FRM_CNVG_COMPOUNDTEXT|APEX_040000              wwv_mig_frm_cpdtxt_grphs_id_fk|apex_040000
                                                                9                 < WWV_MIG_FRM_CPDTXT_TEXTSEGMENT|APEX_040000           wwv_mig_frm_txtsgmt_cpd_id_fk|apex_040000
                                                                7             < WWV_MIG_FRM_CNVS_TABPAGE|APEX_040000                     wwv_mig_frm_ctp_cnvs_id_fk|apex_040000
                                                                6           < WWV_MIG_FRM_COORDINATES|APEX_040000                        wwv_mig_frm_crdnt_frmmdl_id_fk|apex_040000
                                                                6           < WWV_MIG_FRM_EDITOR|APEX_040000                             wwv_mig_frm_edtr_frmmdl_id_fk|apex_040000
                                                                6           < WWV_MIG_FRM_FMB_MENU|APEX_040000                           wwv_mig_frm_menu_frmmdl_id_fk|apex_040000
                                                                7             < WWV_MIG_FRM_FMB_MENU_MENUITEM|APEX_040000                wwv_mig_fmb_menuitem_menuid_fk|apex_040000
                                                                8               < WWV_MIG_FRM_FMB_MENUITEM_ROLE|APEX_040000              wwv_mig_fmb_mnuitemrl_mitm_fk|apex_040000
                                                                6           < WWV_MIG_FRM_LOV|APEX_040000                                wwv_mig_frm_lov_frmmdl_id_fk|apex_040000
                                                                7             < WWV_MIG_FRM_LOVCOLUMNMAPPING|APEX_040000                 wwv_mig_frm_lvcm_frmmdl_id_fk|apex_040000
                                                                8               < WWV_MIG_FRM_REV_LOVCOLMAPS|APEX_040000                 wwv_mig_frm_rev_lcm_id_fk|apex_040000
                                                                7             < WWV_MIG_FRM_REV_LOV|APEX_040000                          wwv_mig_frm_rev_lov_id_fk|apex_040000
                                                                6           < WWV_MIG_FRM_MODULEPARAMETER|APEX_040000                    wwv_mig_frm_mdlpr_frmmdl_id_fk|apex_040000
                                                                6           < WWV_MIG_FRM_OBJECTGROUP|APEX_040000                        wwv_mig_frm_objgp_frmmdl_id_fk|apex_040000
                                                                7             < WWV_MIG_FRM_OBJECTGROUPCHILD|APEX_040000                 wwv_mig_frm_objgpc_objgp_id_fk|apex_040000
                                                                6           < WWV_MIG_FRM_PROGRAMUNIT|APEX_040000                        wwv_mig_frm_pgut_frmmdl_id_fk|apex_040000
                                                                6           < WWV_MIG_FRM_PROPERTYCLASS|APEX_040000                      wwv_mig_frm_ppcl_frmmdl_id_fk|apex_040000
                                                                6           < WWV_MIG_FRM_RECORDGROUPS|APEX_040000                       wwv_mig_frm_recgp_frmmdl_id_fk|apex_040000
                                                                7             < WWV_MIG_FRM_RECORDGROUPCOLUMN|APEX_040000                wwv_mig_frm_rgc_recgp_id_fk|apex_040000
                                                                6           < WWV_MIG_FRM_REPORT|APEX_040000                             wwv_mig_frm_rpt_frmmdl_id_fk|apex_040000
                                                                6           < WWV_MIG_FRM_REV_FORMMODULES|APEX_040000                    wwv_mig_frm_rev_frmmdl_id_fk|apex_040000
                                                                6           < WWV_MIG_FRM_TRIGGERS|APEX_040000                           wwv_mig_frm_trg_frmmdl_id_fk|apex_040000
                                                                6           < WWV_MIG_FRM_VISUALATTRIBUTES|APEX_040000                   wwv_mig_frm_visat_frmmdl_id_fk|apex_040000
                                                                6           < WWV_MIG_FRM_WINDOWS|APEX_040000                            wwv_mig_frm_wndow_frmmdl_id_fk|apex_040000
                                                                3     < WWV_MIG_FRM_MENUS|APEX_040000                                    wwv_mig_menus_project_id_fk|apex_040000
                                                                4       < WWV_MIG_FRM_MENUS_MODULES|APEX_040000                          wwv_mig_mnu_modules_file_id_fk|apex_040000
                                                                5         < WWV_MIG_FRM_MENUS_MENUMODULES|APEX_040000                    wwv_mig_mnu_mnumdl_mdl_id_fk|apex_040000
                                                                6           < WWV_MIG_FRM_MENUSMODULEROLES|APEX_040000                   wwv_mig_mmodrole_id_fk|apex_040000
                                                                6           < WWV_MIG_FRM_MENUS_PROGRAMUNIT|APEX_040000                  wwv_mig_mnu_progunit_id_fk|apex_040000
                                                                6           < WWV_MIG_FRM_MENU|APEX_040000                               wwv_mig_mnu_id_fk|apex_040000
                                                                7             < WWV_MIG_FRM_MENU_MENUITEM|APEX_040000                    wwv_mig_mnuitem_id_fk|apex_040000
                                                                8               < WWV_MIG_FRM_MENUITEM_ROLE|APEX_040000                  wwv_mig_mnuitemrole_id_fk|apex_040000
                                                                3     < WWV_MIG_FRM_REV_APEX_APP|APEX_040000                             wwv_mig_frm_rev_apex_app_fk|apex_040000
                                                                3     < WWV_MIG_OLB|APEX_040000                                          wwv_mig_olb_project_id_fk|apex_040000
                                                                4       < WWV_MIG_OLB_MODULES|APEX_040000                                wwv_mig_olb_modules_file_id_fk|apex_040000
                                                                5         < WWV_MIG_OLB_OBJECTLIBRARY|APEX_040000                        wwv_mig_olb_objlib_mdl_id_fk|apex_040000
                                                                6           < WWV_MIG_OLB_BLOCK|APEX_040000                              wwv_mig_olb_block_objlib_id_fk|apex_040000
                                                                7             < WWV_MIG_OLB_BLK_DATASOURCECOL|APEX_040000                wwv_mig_olb_blk_dsc_blk_id_fk|apex_040000
                                                                7             < WWV_MIG_OLB_BLK_ITEM|APEX_040000                         wwv_mig_olb_blk_item_blk_id_fk|apex_040000
                                                                8               < WWV_MIG_OLB_BLK_ITEM_LIE|APEX_040000                   wwv_mig_olb_bil_item_id_fk|apex_040000
                                                                8               < WWV_MIG_OLB_BLK_ITEM_TRIGGER|APEX_040000               wwv_mig_olb_bit_item_id_fk|apex_040000
                                                                7             < WWV_MIG_OLB_BLK_TRIGGER|APEX_040000                      wwv_mig_olb_blk_trgr_blk_id_fk|apex_040000
                                                                6           < WWV_MIG_OLB_CANVAS|APEX_040000                             wwv_mig_olb_canvs_objlib_id_fk|apex_040000
                                                                7             < WWV_MIG_OLB_CNVS_GRAPHICS|APEX_040000                    wwv_mig_olb_cg_cnvs_id_fk|apex_040000
                                                                8               < WWV_MIG_OLB_CG_COMPOUNDTEXT|APEX_040000                wwv_mig_olb_cg_ct_grphs_id_fk|apex_040000
                                                                9                 < WWV_MIG_OLB_CG_CT_TEXTSEGMENT|APEX_040000            wwv_mig_olb_cg_ct_ts_ct_id_fk|apex_040000
                                                                6           < WWV_MIG_OLB_OBJECTLIBRARYTAB|APEX_040000                   wwv_mig_olb_olt_objlib_id_fk|apex_040000
                                                                7             < WWV_MIG_OLB_OLT_ALERT|APEX_040000                        wwv_mig_olb_olt_alrt_olt_id_fk|apex_040000
                                                                7             < WWV_MIG_OLB_OLT_BLOCK|APEX_040000                        wwv_mig_olb_t_block_olt_id_fk|apex_040000
                                                                8               < WWV_MIG_OLB_OLT_BLK_ITEM|APEX_040000                   wwv_mig_olb_olt_bi_blk_id_fk|apex_040000
                                                                9                 < WWV_MIG_OLB_OLT_BLK_ITEM_TRIGR|APEX_040000           wwv_mig_olb_olt_bit_item_id_fk|apex_040000
                                                                7             < WWV_MIG_OLB_OLT_CANVAS|APEX_040000                       wwv_mig_olb_t_canvas_olt_id_fk|apex_040000
                                                                8               < WWV_MIG_OLB_OLT_CNVS_GRAPHICS|APEX_040000              wwv_mig_olb_olt_cg_cnvs_id_fk|apex_040000
                                                                7             < WWV_MIG_OLB_OLT_GRAPHICS|APEX_040000                     wwv_mig_olb_t_grphcs_olt_id_fk|apex_040000
                                                                7             < WWV_MIG_OLB_OLT_ITEM|APEX_040000                         wwv_mig_olb_olt_item_olt_id_fk|apex_040000
                                                                7             < WWV_MIG_OLB_OLT_MENU|APEX_040000                         wwv_mig_olb_olt_menu_olt_id_fk|apex_040000
                                                                8               < WWV_MIG_OLB_OLT_MENU_MENUITEM|APEX_040000              wwv_mig_olb_olt_mmi_menu_id_fk|apex_040000
                                                                7             < WWV_MIG_OLB_OLT_OBJECTGROUP|APEX_040000                  wwv_mig_olb_t_objgrp_olt_id_fk|apex_040000
                                                                8               < WWV_MIG_OLB_OLT_OB_OBJGRPCHILD|APEX_040000             wwv_mig_olb_olt_ob_ogc_obid_fk|apex_040000
                                                                7             < WWV_MIG_OLB_OLT_REPORT|APEX_040000                       wwv_mig_olb_t_report_olt_id_fk|apex_040000
                                                                7             < WWV_MIG_OLB_OLT_TABPAGE|APEX_040000                      wwv_mig_olb_t_tabpage_oltid_fk|apex_040000
                                                                8               < WWV_MIG_OLB_OLT_TABPG_GRAPHICS|APEX_040000             wwv_mig_olb_olt_tpg_tp_id_fk|apex_040000
                                                                9                 < WWV_MIG_OLB_T_TP_G_GRAPHICS|APEX_040000              wwv_mig_olb_t_tp_gg_g_id_fk|apex_040000
                                                               10                   < WWV_MIG_OLB_T_TP_GG_CPDTXT|APEX_040000             wwv_mig_olb_t_tp_gg_ct_g_id_fk|apex_040000
                                                               11                     < WWV_MIG_OLB_T_TP_GG_CT_TXTSGT|APEX_040000        wwv_mig_olb_ttpggctts_ctid_fk|apex_040000
                                                               10                   < WWV_MIG_OLB_T_TP_GG_GRAPHICS|APEX_040000           wwv_mig_olb_t_tp_ggg_g_id_fk|apex_040000
                                                               11                     < WWV_MIG_OLB_T_TP_GGG_CPDTXT|APEX_040000          wwv_mig_olb_ttp_ggg_ct_gid_fk|apex_040000
                                                               12                       < WWV_MIG_OLB_T_TP_GGG_CT_TXTSGT|APEX_040000     wwv_mig_olb_ttpgggctts_ctid_fk|apex_040000
                                                               11                     < WWV_MIG_OLB_T_TP_GGG_GRAPHICS|APEX_040000        wwv_mig_olb_t_tp_gggg_g_id_fk|apex_040000
                                                               12                       < WWV_MIG_OLB_T_TP_GGGG_CPDTXT|APEX_040000       wwv_mig_olb_ttpggggct_g_id_fk|apex_040000
                                                               13                         < WWV_MIG_OLB_T_TP_GGGG_CT_TXSGT|APEX_040000   wwv_mig_olb_ttpggggcts_ctid_fk|apex_040000
                                                               12                       < WWV_MIG_OLB_T_TP_GGGG_GRAPHICS|APEX_040000     wwv_mig_olb_ttpggggg_g_id_fk|apex_040000
                                                               13                         < WWV_MIG_OLB_T_TP_GGGGG_CPDTXT|APEX_040000    wwv_mig_olb_ttpgggggct_g_id_fk|apex_040000
                                                               14                           < WWV_MIG_OLB_T_TP_GGGGG_CT_TXST|APEX_040000 wwv_mig_olb_ttp5gcts_ct_id_fk|apex_040000
                                                                7             < WWV_MIG_OLB_OLT_VISUALATTRBUTE|APEX_040000               wwv_mig_olb_olt_va_olt_id_fk|apex_040000
                                                                7             < WWV_MIG_OLB_OLT_WINDOW|APEX_040000                       wwv_mig_olb_olt_wndow_oltid_fk|apex_040000
                                                                6           < WWV_MIG_OLB_PROGRAMUNIT|APEX_040000                        wwv_mig_olb_pu_objlib_id_fk|apex_040000
                                                                6           < WWV_MIG_OLB_PROPERTYCLASS|APEX_040000                      wwv_mig_olb_pc_objlib_id_fk|apex_040000
                                                                6           < WWV_MIG_OLB_VISUALATTRIBUTE|APEX_040000                    wwv_mig_olb_va_objlib_id_fk|apex_040000
                                                                6           < WWV_MIG_OLB_WINDOW|APEX_040000                             wwv_mig_olb_wndow_objlib_id_fk|apex_040000
                                                                3     < WWV_MIG_PLSQL_LIBS|APEX_040000                                   wwv_mig_plls_project_id_fk|apex_040000
                                                                3     < WWV_MIG_PROJECT_COMPONENTS|APEX_040000                           wwv_mig_proj_comp_fk|apex_040000
                                                                3     < WWV_MIG_PROJECT_TRIGGERS|APEX_040000                             wwv_mig_proj_trig_fk|apex_040000
                                                                3     < WWV_MIG_RPTS|APEX_040000                                         wwv_mig_rpts_project_id_fk|apex_040000
                                                                4       < WWV_MIG_REPORT|APEX_040000                                     wwv_mig_rep_file_id_fk|apex_040000
                                                                5         < WWV_MIG_RPT_DATA|APEX_040000                                 wwv_mig_repdata_id_fk|apex_040000
                                                                6           < WWV_MIG_RPT_DATASRC|APEX_040000                            wwv_mig_repsrc_id_fk|apex_040000
                                                                7             < WWV_MIG_RPT_DATASRC_GRP|APEX_040000                      wwv_mig_grp_id_fk|apex_040000
                                                                8               < WWV_MIG_RPT_GRP_DATAITEM|APEX_040000                   wwv_mig_grp_dataitem_id_fk|apex_040000
                                                                9                 < WWV_MIG_RPT_GRP_DATAITEM_DESC|APEX_040000            wwv_mig_grp_itemdesc_id_fk|apex_040000
                                                                9                 < WWV_MIG_RPT_GRP_DATAITEM_PRIV|APEX_040000            wwv_mig_grp_itempriv_id_fk|apex_040000
                                                                8               < WWV_MIG_RPT_GRP_FIELD|APEX_040000                      wwv_mig_grp_fld_id_fk|apex_040000
                                                                8               < WWV_MIG_RPT_GRP_FILTER|APEX_040000                     wwv_mig_grp_fltr_id_fk|apex_040000
                                                                8               < WWV_MIG_RPT_GRP_FORMULA|APEX_040000                    wwv_mig_grp_form_id_fk|apex_040000
                                                                8               < WWV_MIG_RPT_GRP_ROWDELIM|APEX_040000                   wwv_mig_grp_row_id_fk|apex_040000
                                                                8               < WWV_MIG_RPT_GRP_SUMMARY|APEX_040000                    wwv_mig_grp_sum_id_fk|apex_040000
                                                                7             < WWV_MIG_RPT_DATASRC_SELECT|APEX_040000                   wwv_mig_select_id_fk|apex_040000
                                                                6           < WWV_MIG_RPT_DATA_SUMMARY|APEX_040000                       wwv_mig_repsum_id_fk|apex_040000
                                                                5         < WWV_MIG_RPT_REPORTPRIVATE|APEX_040000                        wwv_mig_rptpriv_id_fk|apex_040000
WWV_FLOW_ADVISOR_CATEGORIES|APEX_040000          2       3      0 WWV_FLOW_ADVISOR_CATEGORIES|APEX_040000                                ROOT
                                                                1 < WWV_FLOW_ADVISOR_CHECKS|APEX_040000                                  wwv_flow_adv_chk_cat_fk|apex_040000
                                                                2   < WWV_FLOW_ADVISOR_CHECK_MSGS|APEX_040000                            wwv_flow_adv_chk_msg_check_fk|apex_040000
WWV_FLOW_BUGS|APEX_040000                       13      11      0 WWV_FLOW_BUGS|APEX_040000                                              ROOT
                                                                1 < WWV_FLOW_TEAMDEV_TAG_CLOUD|APEX_040000                               wwv_flow_teamdev_tc_b|apex_040000
                                                                2   > WWV_FLOW_FEATURES|APEX_040000                                      wwv_flow_teamdev_tc_f|apex_040000
                                                                3     = WWV_FLOW_FEATURES|APEX_040000*                                   wwv_flow_features_par_feat_fk|apex_040000
                                                                3     < WWV_FLOW_FEATURE_HISTORY|APEX_040000                             wwv_flow_feature_hist_fk|apex_040000
                                                                3     < WWV_FLOW_FEATURE_PROGRESS|APEX_040000                            wwv_flow_feature_prog_fk|apex_040000
                                                                3     < WWV_FLOW_TEAM_FILES|APEX_040000                                  wwv_flow_team_files_fk1|apex_040000
                                                                4       > WWV_FLOW_EVENTS|APEX_040000                                    wwv_flow_team_files_fk3|apex_040000
                                                                4       > WWV_FLOW_FEEDBACK|APEX_040000                                  wwv_flow_team_files_fk4|apex_040000
                                                                5         < WWV_FLOW_FEEDBACK_FOLLOWUP|APEX_040000                       wwv_flow_feedback_fup_fk|apex_040000
                                                                5         < WWV_FLOW_TEAM_FILES|APEX_040000*                             wwv_flow_team_files_fk5|apex_040000
                                                                4       > WWV_FLOW_TASKS|APEX_040000                                     wwv_flow_team_files_fk2|apex_040000
                                                                5         < WWV_FLOW_TASK_PROGRESS|APEX_040000                           wwv_flow_task_prog_fk|apex_040000
                                                                5         < WWV_FLOW_TEAMDEV_TAG_CLOUD|APEX_040000*                      wwv_flow_teamdev_tc_t|apex_040000
WWV_FLOW_DATA_LOAD_BAD_LOG|APEX_040000           1       2      0 WWV_FLOW_DATA_LOAD_BAD_LOG|APEX_040000                                 ROOT
                                                                1 > WWV_FLOW_DATA_LOAD_UNLOAD|APEX_040000                                wwv_flow_data_load_bad_log_fk1|apex_040000
WWV_FLOW_DICTIONARY_VIEWS|APEX_040000            1       1      0 WWV_FLOW_DICTIONARY_VIEWS|APEX_040000                                  ROOT
                                                                1 = WWV_FLOW_DICTIONARY_VIEWS|APEX_040000*                               wwv_flow_dict_view_parent_fk|apex_040000
WWV_FLOW_FILE_OBJECTS$|FLOWS_FILES               5       6      0 WWV_FLOW_FILE_OBJECTS$|FLOWS_FILES                                     ROOT
                                                                1 < WWV_FLOW_IMPORT_EXPORT|APEX_040000                                   wwv_flow_import_export_fk|apex_040000
                                                                1 < WWV_FLOW_SW_BINDS|APEX_040000                                        wwv_flow_sw_bind_fk|apex_040000
                                                                1 < WWV_FLOW_SW_RESULTS|APEX_040000                                      wwv_flow_sw_result_fk|apex_040000
                                                                2   < WWV_FLOW_SW_DETAIL_RESULTS|APEX_040000                             wwv_flow_sw_d_result_fk|apex_040000
                                                                1 < WWV_FLOW_SW_STMTS|APEX_040000                                        wwv_flow_sw_stmts_fk|apex_040000
WWV_FLOW_FLASH_MAP_FILES|APEX_040000             2       3      0 WWV_FLOW_FLASH_MAP_FILES|APEX_040000                                   ROOT
                                                                1 > WWV_FLOW_FLASH_MAP_FOLDERS|APEX_040000                               wwv_flow_flash_map_files_fk|apex_040000
                                                                1 < WWV_FLOW_FLASH_MAP_REGIONS|APEX_040000                               wwv_flow_flash_map_reg_fk|apex_040000
WWV_FLOW_FND_GROUP_USERS|APEX_040000             1       2      0 WWV_FLOW_FND_GROUP_USERS|APEX_040000                                   ROOT
                                                                1 > WWV_FLOW_FND_USER_GROUPS|APEX_040000                                 wwv_flow_fnd_gu_int_g_fk|apex_040000
WWV_FLOW_HNT_ARGUMENT_INFO|APEX_040000           1       2      0 WWV_FLOW_HNT_ARGUMENT_INFO|APEX_040000                                 ROOT
                                                                1 > WWV_FLOW_HNT_PROCEDURE_INFO|APEX_040000                              wwv_flow_hnt_arg_info_proc_fk|apex_040000
WWV_FLOW_HNT_COLUMN_DICT|APEX_040000             1       2      0 WWV_FLOW_HNT_COLUMN_DICT|APEX_040000                                   ROOT
                                                                1 < WWV_FLOW_HNT_COL_DICT_SYN|APEX_040000                                wwv_flow_hnt_col_dict_syn_fk|apex_040000
WWV_FLOW_HNT_COLUMN_INFO|APEX_040000             4       4      0 WWV_FLOW_HNT_COLUMN_INFO|APEX_040000                                   ROOT
                                                                1 > WWV_FLOW_HNT_GROUPS|APEX_040000                                      wwv_flow_hnt_col_info_grp_fk|apex_040000
                                                                2   > WWV_FLOW_HNT_TABLE_INFO|APEX_040000                                wwv_flow_hnt_groups_tab_fk|apex_040000
                                                                3     < WWV_FLOW_HNT_COLUMN_INFO|APEX_040000*                            wwv_flow_hnt_col_info_tab_fk|apex_040000
                                                                1 < WWV_FLOW_HNT_LOV_DATA|APEX_040000                                    wwv_flow_hnt_lov_data_col_fk|apex_040000
WWV_FLOW_MAIL_ATTACHMENTS|APEX_040000            1       2      0 WWV_FLOW_MAIL_ATTACHMENTS|APEX_040000                                  ROOT
                                                                1 > WWV_FLOW_MAIL_QUEUE|APEX_040000                                      wwv_flow_mail_attachments_fk1|apex_040000
WWV_FLOW_MODELS|APEX_040000                      4       4      0 WWV_FLOW_MODELS|APEX_040000                                            ROOT
                                                                1 < WWV_FLOW_MODEL_PAGES|APEX_040000                                     wwv_flow_model_pages_fk|apex_040000
                                                                2   = WWV_FLOW_MODEL_PAGES|APEX_040000*                                  wwv_flow_model_pages_fk2|apex_040000
                                                                2   < WWV_FLOW_MODEL_PAGE_REGIONS|APEX_040000                            wwv_flow_mpr_fk|apex_040000
                                                                3     < WWV_FLOW_MODEL_PAGE_COLS|APEX_040000                             wwv_flow_model_page_cols_fk|apex_040000
WWV_FLOW_QB_SAVED_COND|APEX_040000               3       4      0 WWV_FLOW_QB_SAVED_COND|APEX_040000                                     ROOT
                                                                1 > WWV_FLOW_QB_SAVED_QUERY|APEX_040000                                  sys_c005031|apex_040000
                                                                2   < WWV_FLOW_QB_SAVED_JOIN|APEX_040000                                 sys_c005038|apex_040000
                                                                2   < WWV_FLOW_QB_SAVED_TABS|APEX_040000                                 sys_c005045|apex_040000
WWV_FLOW_RESTRICTED_SCHEMAS|APEX_040000          1       2      0 WWV_FLOW_RESTRICTED_SCHEMAS|APEX_040000                                ROOT
                                                                1 < WWV_FLOW_RSCHEMA_EXCEPTIONS|APEX_040000                              wwv_flow_rschema_exceptions_fk|apex_040000
WWV_MIG_FRM_OLB_XMLTAGTABLEMAP|APEX_040000       1       1      0 WWV_MIG_FRM_OLB_XMLTAGTABLEMAP|APEX_040000                             ROOT
                                                                1 = WWV_MIG_FRM_OLB_XMLTAGTABLEMAP|APEX_040000*                          wwv_mig_olb_xmltagtablemap_fk|apex_040000
WWV_MIG_FRM_XMLTAGTABLEMAP|APEX_040000           1       1      0 WWV_MIG_FRM_XMLTAGTABLEMAP|APEX_040000                                 ROOT
                                                                1 = WWV_MIG_FRM_XMLTAGTABLEMAP|APEX_040000*                              wwv_mig_frm_xmltagtablemap_fk|apex_040000
WWV_MIG_MENU_XMLTAGTABLEMAP|APEX_040000          1       1      0 WWV_MIG_MENU_XMLTAGTABLEMAP|APEX_040000                                ROOT
                                                                1 = WWV_MIG_MENU_XMLTAGTABLEMAP|APEX_040000*                             wwv_mig_mnu_xmltagtablemap_fk|apex_040000
WWV_MIG_RPT_XMLTAGTABLEMAP|APEX_040000           1       1      0 WWV_MIG_RPT_XMLTAGTABLEMAP|APEX_040000                                 ROOT
                                                                1 = WWV_MIG_RPT_XMLTAGTABLEMAP|APEX_040000*                              wwv_mig_rpt_xmltagtablemap_fk|apex_040000
WWV_PURGE_DATAFILES|APEX_040000                  4       5      0 WWV_PURGE_DATAFILES|APEX_040000                                        ROOT
                                                                1 > WWV_PURGE_WORKSPACES|APEX_040000                                     wwv_purge_datafiles_fk1|apex_040000
                                                                2   < WWV_PURGE_EMAILS|APEX_040000                                       wwv_purge_emails_fk1|apex_040000
                                                                3     < WWV_PURGE_WORKSPACE_RESPONSES|APEX_040000                        wwv_purge_workspace_resp_fk1|apex_040000
                                                                2   < WWV_PURGE_SCHEMAS|APEX_040000                                      wwv_purge_schemas_fk1|apex_040000

359 rows selected.

Elapsed: 00:00:01.81
Network summary 1 - by network

Network                                     #Links  #Nodes    Max Lev
------------------------------------------ ------- ------- ----------
WWV_FLOW_HNT_COLUMN_DICT|APEX_040000             2       2          1
WWV_MIG_RPT_XMLTAGTABLEMAP|APEX_040000           2       1          1
WWV_MIG_MENU_XMLTAGTABLEMAP|APEX_040000          2       1          1
WWV_MIG_FRM_XMLTAGTABLEMAP|APEX_040000           2       1          1
WWV_MIG_FRM_OLB_XMLTAGTABLEMAP|APEX_040000       2       1          1
WWV_FLOW_RESTRICTED_SCHEMAS|APEX_040000          2       2          1
WWV_FLOW_MAIL_ATTACHMENTS|APEX_040000            2       2          1
WWV_FLOW_HNT_ARGUMENT_INFO|APEX_040000           2       2          1
WWV_FLOW_FND_GROUP_USERS|APEX_040000             2       2          1
WWV_FLOW_DICTIONARY_VIEWS|APEX_040000            2       1          1
WWV_FLOW_DATA_LOAD_BAD_LOG|APEX_040000           2       2          1
WWV_FLOW_FLASH_MAP_FILES|APEX_040000             3       3          1
WWV_FLOW_ADVISOR_CATEGORIES|APEX_040000          3       3          2
WWV_FLOW_QB_SAVED_COND|APEX_040000               4       4          2
WWV_PURGE_DATAFILES|APEX_040000                  5       5          3
APEX$_WS_FILES|APEX_040000                       5       5          2
WWV_FLOW_MODELS|APEX_040000                      5       4          3
WWV_FLOW_HNT_COLUMN_INFO|APEX_040000             5       4          3
WWV_FLOW_FILE_OBJECTS$|FLOWS_FILES               6       6          2
WWV_FLOW_BUGS|APEX_040000                       14      11          5
WWV_FLOWS|APEX_040000                          287     236         14

21 rows selected.

Elapsed: 00:00:00.01
Network summary 2 - grouped by numbers of nodes

 #Nodes  #Networks
------- ----------
      1          5
      2          6
      3          2
      4          3
      5          2
      6          1
     11          1
    236          1

8 rows selected.

Elapsed: 00:00:00.01

Schema Metadata Analysis Report

This is too long to embed so is included as an attachment.
L_APEX_040000