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

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

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

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

Data Types

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

  • NUMBER
  • VARCHAR2
  • DATE
  • TIMESTAMP

Custom Dependencies

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

Code

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

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

 
Output for Cursor against HR.employees

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






 

A Note on Dependencies and Database Unit Testing

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

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

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

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

Development Phase
UT Phases-dev

Regression Phase
UT Phases-reg

I would welcome any comments or opinions.






 

A Note on Oracle Join Orders and Hints

I read an interesting article this week on a company internal blog (thanks, Deepak), which pointed out that many people seem to think that the hint USE_HASH needs two parameters to specify the hint, when in fact only the right table alias is required. Where more than one alias is given the hint is effectively treated as separate one-alias hints. Here is what the manual says, Oracle SQL Manual 12.1 - Hints - USE_HASH:

use_hash_hint

The USE_HASH hint instructs the optimizer to join each specified table with another row source using a hash join. For example:

SELECT /*+ USE_HASH(l h) */ *
FROM orders h, order_items l
WHERE l.order_id = h.order_id
AND l.order_id > 2400;

Observe that in the example given, with two tables, there can be only one join in the chosen plan but both aliases are given in the hint. Only one of the hints can take effect here, but which one? It depends on the join order chosen by the the Cost Based Optimizer (CBO). In the sections on USE_MERGE and USE_NL, the manual says:

Use of the USE_NL and USE_MERGE hints is recommended with the LEADING and ORDERED hints. The optimizer uses those hints when the referenced table is forced to be the inner table of a join. The hints are ignored if the referenced table is the outer table.

My colleague linked to an article by Jonathan Lewis that considers join order in relation to hash joins in some detail, Quiz Night.

You have NOT defined a hash join completely until you have specified which rowsource should be used as the build table and which as the probe table – so every time you supply the use_hash() hint for a table, you should also supply the swap_join_inputs() hint or the no_swap_join_inputs() hint.

In the article, JL asks how many execution plans are possible for a four-table query where he says he specifies both join order (leading hint) and join method for each of the three joins (use_hash). The answer is 8, which might be a surprise if you accept the specification at face value, as that would imply that a single plan has been fully specified. The explanation of the apparent paradox is of course given in the quote above. JL goes on to enumerate each possible plan, obtained by different combinations of the swap_join_inputs and no_swap_join_inputs hints. He then asserts:

Note the extreme change in shape and apparent order of tables in the plan. Despite this the join order really is t1 -> t2 -> t3 -> t4 in every case. I’ll give a quick description of the first and last plans to explain this.

The explanation seems plausible, but I can see a possible problem with it. It is possible to generate exactly the same plans using the hint leading (t2 t1 t3 t4) and different combinations of the swap hints. I could then equally plausibly assert that the join order really is t2 -> t1 -> t3 -> t4 in every case. Both assertions can't be right can they? The explanation is that we need to consider two levels of ordering, and not just for hash joins. First, I will demonstrate how to get identical plans with different leading hints. I will use Oracle's HR schema, and show the hints necessary to get all hash join plans (with current statistics - I am not fully specifying the plans in general):

SELECT *
  FROM employees e
  JOIN departments d
 USING (department_id)
  JOIN locations l
 USING (location_id)

You can see that hash joins are only possible between e and d, and between d and l (or rowsets that include the relevant keys).

We can use a notation x.y to mean hash-join y as the right table, using x to form the hash-table, and (x.y) to denote the resulting rowset. Then I believe there are exactly 8 possible hash-join permutations, each of which I was able to hint using leading, use_hash, swap_join_inputs and no_swap_join_input hints as follows:

1. leading (l) use_hash (d)
===========================
Combo              SJI
-------            ----
(l.d).e  
e.(l.d)            e
(d.l).e            d
e.(d.l)            d, e

2. leading (e)
==============
Combo              SJI    NSJI
-------            ----   ----
l.(e.d)
(e.d).l                   d
l.(d.e)            d
(d.e).l            d      l

Now, look at the following output where I have got exactly the same plan (the first combo above) using two different leading hints (putting in the first two aliases to be sure). The first has:

/*+ leading (l d)  use_hash (d) */

The second has:

/*+ leading (d l) use_hash (l e) swap_join_inputs (l) */

The plans that result, showing identical plan hash value of 2684174912 are:

SQL_ID  ds98jqmdbn9ym, child number 0
-------------------------------------
SELECT  /*+ leading (l d)  use_hash (d) GATHER_PLAN_STATISTICS
Lead_l_Hash_d */      *   FROM employees e   JOIN departments d  USING
(department_id)   JOIN locations l  USING (location_id)

Plan hash value: 2684174912

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |             |      1 |        |    106 |00:00:00.01 |      25 |       |       |          |
|*  1 |  HASH JOIN          |             |      1 |    106 |    106 |00:00:00.01 |      25 |   796K|   796K| 1241K (0)|
|*  2 |   HASH JOIN         |             |      1 |     27 |     27 |00:00:00.01 |      12 |   835K|   835K| 1122K (0)|
|   3 |    TABLE ACCESS FULL| LOCATIONS   |      1 |     23 |     23 |00:00:00.01 |       6 |       |       |          |
|   4 |    TABLE ACCESS FULL| DEPARTMENTS |      1 |     27 |     27 |00:00:00.01 |       6 |       |       |          |
|   5 |   TABLE ACCESS FULL | EMPLOYEES   |      1 |    107 |    107 |00:00:00.01 |      13 |       |       |          |
------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
   2 - access("D"."LOCATION_ID"="L"."LOCATION_ID")

SQL_ID  fcbh8httjtpj3, child number 0
-------------------------------------
SELECT  /*+ leading (d l) use_hash (l e) swap_join_inputs (l)
GATHER_PLAN_STATISTICS Lead_d_Hash_l_e_SJI_l */      *   FROM employees
e   JOIN departments d  USING (department_id)   JOIN locations l  USING
(location_id)

Plan hash value: 2684174912

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |             |      1 |        |    106 |00:00:00.01 |      25 |       |       |          |
|*  1 |  HASH JOIN          |             |      1 |    106 |    106 |00:00:00.01 |      25 |   796K|   796K| 1240K (0)|
|*  2 |   HASH JOIN         |             |      1 |     27 |     27 |00:00:00.01 |      12 |   835K|   835K| 1109K (0)|
|   3 |    TABLE ACCESS FULL| LOCATIONS   |      1 |     23 |     23 |00:00:00.01 |       6 |       |       |          |
|   4 |    TABLE ACCESS FULL| DEPARTMENTS |      1 |     27 |     27 |00:00:00.01 |       6 |       |       |          |
|   5 |   TABLE ACCESS FULL | EMPLOYEES   |      1 |    107 |    107 |00:00:00.01 |      13 |       |       |          |
------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
   2 - access("D"."LOCATION_ID"="L"."LOCATION_ID")

So, here we seem to have shown that join order l -> d -> e = join order d -> l -> e, since they have the same plan. I think that to understand this we need to separate out two levels of join order. In the two-table example I took from the manual I stated there could be only one join order in a given plan as there is only one join, meaning by join order which table appears on the left and which on the right.

Outer-level join order

This can be specified by a number assigned to each table matching the sequence number of the join in which it features. The first two tables therefore both have a sequence number of 1. Another way of looking at it can be found by considering my bracketing notation above. In general, the outer-level join sequence number for an n-table query, OS(i) = n - number of brackets enclosing table i - 1

For (l.d).e, l and d have OS = 1 and e has OS = 2. The same is true for (d.l).e.

It is in this sense of join order that JL's queries all have the same join order, and that a single plan can arise from different outer-level join orders, once inner-join order is factored in.

Inner-level join order

This is simply the side on which a table is joined to a rowset in a given join, say 1 for the left side, and 2 for the right side in a hash join.

Note that the leading hint treats both levels of join order, but behaves differently between hash joins and other types of join.

Leading hint in hash join

  • Determines the outer-level join order
  • Defaults the inner-level join order
  • Swap_join_inputs operation overrides the inner-level default join order

Leading hint in other types of join

  • Determines the outer-level join order
  • Determines the inner-level join order on the first two tables
  • Inner-level order not applicable after first two tables






 

Brendan's Database Unit Testing Framework

I recently wrote a number of articles on design patterns for database unit testing, with code included for testing some example procedures against Oracle's HR demo schema.

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 have now removed it to form my own lightweight independent framework that is designed to help avoid many unit testing anti-patterns, and provide better output formatting than utPLSQL.

Update, 9 July 2016: I have added a listing of all inputs at scenario level (see box below). This means that the unit test outputs now become precise and accurate documents of what the program does. All the output is printed by a central library procedure so that work by individual test programs is minimised.

The code is available here: BrenPatF/db_unit_test

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

Design Patterns for Database Unit Testing 1: Web Service Saving - Design
Design Patterns for Database Unit Testing 2: Web Service Saving - Code
Design Patterns for Database Unit Testing 3: Views - Design
Design Patterns for Database Unit Testing 4: Views - Code

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

PL/SQL procedure successfully completed.

SQL> @L_Log_Default

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

UNIT TEST for UT_Emp_WS.ut_AIP_Save_Emps
========================================

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

    INPUTS
    ======

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

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

        }
        =

    OUTPUTS
    =======

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

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

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

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

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

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

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

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

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

    INPUTS
    ======

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

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

        }
        =

    OUTPUTS
    =======

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

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

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

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

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

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

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

    INPUTS
    ======

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

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

        }
        =

    OUTPUTS
    =======

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

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

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

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

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

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

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

    INPUTS
    ======

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

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

        }
        =

    OUTPUTS
    =======

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

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

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

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

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

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

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

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

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

SUMMARY for UT_Emp_WS.ut_AIP_Save_Emps
======================================

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

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

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

UNIT TEST for UT_View_Drivers.ut_HR_Test_View_V
===============================================

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

#  Employee id  Department id     Manager  Job id          Salary
-  -----------  -------------  ----------  ----------  ----------
1         1518             10              IT_PROG           1000
2         1519             10        1518  IT_PROG           2000
3         1520                       1518  IT_PROG           3000
4         1521             10        1518  AD_ASST           4000

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

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

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

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

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

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

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

    INPUTS
    ======

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

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

        }
        =

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

            Where
            -----


        }
        =

    OUTPUTS
    =======

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

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

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

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

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

    INPUTS
    ======

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

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

        }
        =

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

            Where
            -----


        }
        =

    OUTPUTS
    =======

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

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

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

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

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

    INPUTS
    ======

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

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

        }
        =

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

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

        }
        =

    OUTPUTS
    =======

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

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

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

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

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

    INPUTS
    ======

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

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

        }
        =

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

            Where
            -----


        }
        =

    OUTPUTS
    =======

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

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

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

SUMMARY for UT_View_Drivers.ut_HR_Test_View_V
=============================================

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

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

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

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

Package.Procedure                  Tests  Fails         ELA         CPU
---------------------------------  -----  -----  ----------  ----------
UT_Emp_WS.ut_AIP_Save_Emps            17      3        1.71        0.07
UT_View_Drivers.ut_HR_Test_View_V      9      1        0.32        0.09
---------------------------------  -----  -----  ----------  ----------
Total                                 26      4        2.03        0.16
---------------------------------  -----  -----  ----------  ----------
Others error in (): ORA-20001: Suite BRENDAN returned error status: ORA-06512: at "DP_7.UT_UTILS", line 140
ORA-06512: at "DP_7.UT_UTILS", line 807
ORA-06512: at line 5


476 rows selected.

SQL> SPOOL OFF






 

Design Patterns for Database Unit Testing 4: Views - Code

In Design Patterns for Database Unit Testing 3: Views - Design I presented a design pattern for unit testing views, using an example based on Oracle's HR demo schema, and here I list the code for the main unit test procedure and a couple of the utility procedures, with notes (all the code is included in a linked article).

First a structure diagram shows how the PL/SQL packages relate to each other, and two call structure tables are given for both the main unit test procedure for the view and for the previous web service saving procedure. The utility code has been substantially re-factored to improve output format and for other reasons. Sections of the code are listed with notes.

All the code and installation scripts are supplied in a linked article. With these scripts, anyone can run and experiment with the unit testing example, and can use it as a template for developing real unit testing code following the same design patterns.

Update, 26 June 2016: The code has been re-factored to improve formatting and remove the dependence on utPLSQL, Brendan's Database Unit Testing Framework.
Update, 10 July 2016: I have added in scenario-level printing of all inputs.

Package Structure Diagram

Unit Testing-CSD-All

Call Structure Table - UT_Emp_WS.ut_AIP_Save_Emps

Level 1 Level 2 Level 3 Package
Init UT_Utils
Setup UT_Emp_WS
Increment_Time Timer_Set
Call_WS UT_Emp_WS
Do_Save UT_Emp_WS
Init_Time Timer_Set
AIP_Save_Emps UT_Emp_WS
Increment_Time Timer_Set
Get_Tab_Lis UT_Emp_WS
List_Delim Utils
Get_Arr_Lis UT_Emp_WS
List_Delim Utils
List_or_Empty UT_Utils
Check_UT_Results UT_Utils
Write_Other_Error Utils

Call Structure Table - UT_View_Drivers.ut_HR_Test_View_V

Level 1 Level 2 Package
Init UT_Utils
Setup UT_View_Drivers
Heading Utils
Col_Headers Utils
Ins_Emp DML_API_UT_HR
Increment_Time Timer_Set
Get_View UT_Utils
Check_UT_Results UT_Utils
Write_Other_Error Utils

UT_View_Drivers.ut_HR_Test_View_V - View Unit Test Procedure

Declare section

PROCEDURE ut_HR_Test_View_V IS

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

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

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

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

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

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

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

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

Notes on declare section

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

Setup section

  PROCEDURE Setup (p_call_ind PLS_INTEGER, x_inp_lis OUT L1_chr_arr) IS -- scenario index

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

  BEGIN

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

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

    END LOOP;

  END Setup;

Notes on setup section

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

Main section

BEGIN

  l_timer_set := UT_Utils.Init (c_proc_name);
  l_act_2lis.EXTEND (c_exp_2lis.COUNT);
  l_inp_3lis.EXTEND (c_exp_2lis.COUNT);

  FOR i IN 1..c_exp_2lis.COUNT LOOP

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

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

    l_inp_3lis (i)(2) := L1_chr_arr (c_where_lis(i));
    Timer_Set.Increment_Time (l_timer_set, UT_Utils.c_setup_timer);
    l_act_2lis(i) := UT_Utils.Get_View (
                            p_view_name         => c_view_name,
                            p_sel_field_lis     => c_sel_lis,
                            p_where             => c_where_lis(i),
                            p_timer_set         => l_timer_set);

  END LOOP;

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

EXCEPTION

  WHEN OTHERS THEN
    Utils.Write_Other_Error;
    RAISE;

END ut_HR_Test_View_V;

Notes on main section

  • It's quite short isn't it 🙂
  • Main section loops over the scenarios
  • Setup is called to create the data set for the scenario
  • Get_View returns the results of the query on the view as 2-level array
  • Get_View rolls back after getting the results, so the inserted test records are removed from the database
  • Check_UT_Results is called to do all the assertions within nested loops, then print the results

UT_Utils - Unit Test Utility Procedures

Check_UT_Results - to check results from testing

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

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

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

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

END Check_UT_Results;

Notes on Check_UT_Results

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

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

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

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

BEGIN

  FOR i IN 1..p_sel_field_lis.COUNT LOOP

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

  END LOOP;

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

  OPEN l_cur FOR l_sql_txt;

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

  CLOSE l_cur;

  Timer_Set.Increment_Time (p_timer_set, UT_Utils.c_call_timer);
  ROLLBACK;
  RETURN ut_Utils.List_or_Empty (l_result_lis);

END Get_View;

Notes on Get_View

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

Package block section

BEGIN

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

END UT_Utils;

Notes on package block section

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

Installation Instructions

See Brendan's Database Unit Testing Framework for a link to the installation code for both the framework and the demo code on gitHub, and links to related articles.

Conclusions

  • A second design pattern has been presented, for testing views, with scenarios and output results given in Design Patterns for Database Unit Testing 3: Views - Design
  • The implementation, presented here, was against an Oracle database publicly available demonstration schema, and used Brendan's database unit testing framework






 

Design Patterns for Database Unit Testing 3: Views - Design

In the first of a 2-part article, Design Patterns for Database Unit Testing 1: Web Service Saving - Design I presented a design pattern for unit testing web service 'save' procedures, beginning with an abstract discussion, and progressing to an example based on Oracle's HR demo schema. In the second part, Design Patterns for Database Unit Testing 2: Web Service Saving - Code, I listed various extremely common antipatterns in database unit testing, and how they may be avoided, and provided the code with notes.

In this post, I present a design pattern for unit testing views, as the first of another 2-part article. I start by discussing when and how to unit test views. Unlike in the previous article, test data has to be created during unit testing of views, and a very general approach to creating and selecting the test data is proposed. The use case for the design pattern is described, and scenarios and sub-scenarios are defined conceptually. Finally, the output from the unit testing is presented, with notes.

The second post provides the code, with notes: Design Patterns for Database Unit Testing 4: Views - Code. As before, a self-contained installation file is attached (now in a separate link) for others who may be interested, including the earlier web service unit test code, which has been substantially re-factored.

Update, 26 June 2016:The code has been re-factored to improve formatting and remove the dependence on utPLSQL, Brendan's Database Unit Testing Framework.
Update, 09 July 2016:I have added in scenario-level printing of all inputs.

When to Unit Test Views

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

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

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

How to Unit Test Views Using a PL/SQL Testing Framework

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

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

Unit Test Data

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

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

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

Partitioning Views with System Contexts
In Oracle ebusiness's multi-org implementations (at least up to release 11), transactions are partitioned by a numeric identifier for the organization owning the transaction. This org_id value is stored in a column in the base table on transaction creation. Within the application code the base table is not queried directly, but through a view that restricts records returned to those for the organization corresponding to the current role of the application user, which is stored in the userenv system context.
See SYS_CONTEXT) for information on the system context database feature, and Multiple Organizations in Oracle Applications for its use in Oracle ebusiness.

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

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

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

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

Schema Structure

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

Design Pattern Use Case for Testing Views

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

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

The view functionality can be described in words as:

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

The view SQL is:

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

Scenarios and Sub-scenarios

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

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

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

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

View Unit Test Output

Data setup section

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

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

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

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

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

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

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

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

Notes on data setup section

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

Results section

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

PL/SQL procedure successfully completed.

SQL> @L_Log_Default

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

UNIT TEST for UT_Emp_WS.ut_AIP_Save_Emps
========================================

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

    INPUTS
    ======

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

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

        }
        =

    OUTPUTS
    =======

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

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

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

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

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

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

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

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

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

    INPUTS
    ======

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

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

        }
        =

    OUTPUTS
    =======

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

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

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

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

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

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

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

    INPUTS
    ======

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

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

        }
        =

    OUTPUTS
    =======

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

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

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

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

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

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

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

    INPUTS
    ======

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

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

        }
        =

    OUTPUTS
    =======

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

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

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

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

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

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

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

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

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

SUMMARY for UT_Emp_WS.ut_AIP_Save_Emps
======================================

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

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

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

UNIT TEST for UT_View_Drivers.ut_HR_Test_View_V
===============================================

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

#  Employee id  Department id     Manager  Job id          Salary
-  -----------  -------------  ----------  ----------  ----------
1         1518             10              IT_PROG           1000
2         1519             10        1518  IT_PROG           2000
3         1520                       1518  IT_PROG           3000
4         1521             10        1518  AD_ASST           4000

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

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

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

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

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

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

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

    INPUTS
    ======

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

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

        }
        =

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

            Where
            -----


        }
        =

    OUTPUTS
    =======

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

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

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

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

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

    INPUTS
    ======

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

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

        }
        =

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

            Where
            -----


        }
        =

    OUTPUTS
    =======

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

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

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

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

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

    INPUTS
    ======

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

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

        }
        =

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

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

        }
        =

    OUTPUTS
    =======

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

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

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

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

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

    INPUTS
    ======

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

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

        }
        =

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

            Where
            -----


        }
        =

    OUTPUTS
    =======

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

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

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

SUMMARY for UT_View_Drivers.ut_HR_Test_View_V
=============================================

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

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

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

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

Package.Procedure                  Tests  Fails         ELA         CPU
---------------------------------  -----  -----  ----------  ----------
UT_Emp_WS.ut_AIP_Save_Emps            17      3        1.71        0.07
UT_View_Drivers.ut_HR_Test_View_V      9      1        0.32        0.09
---------------------------------  -----  -----  ----------  ----------
Total                                 26      4        2.03        0.16
---------------------------------  -----  -----  ----------  ----------
Others error in (): ORA-20001: Suite BRENDAN returned error status: ORA-06512: at "DP_7.UT_UTILS", line 140
ORA-06512: at "DP_7.UT_UTILS", line 807
ORA-06512: at line 5


476 rows selected.

SQL> SPOOL OFF

Notes on results section

  • Several improvements have been made in output formatting since the previous article
  • In the update of this article on 26 June 2016, the output has been completely re-formatted, including displaying individual fields rather than records as delimited strings, as well as the dependence on utPLSQL being removed
  • There is now only one main results section, with failed lines having the expected value printed directly under the actual
  • In a view test there is only one group, namely the selected data set
  • The output includes testing of the web service procedure discussed in the earlier articles

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






 

A Template Script for JDBC Integration Testing of Oracle Procedures

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

The code below runs against any Oracle instance on which my example package and supporting objects have been installed (see link above for instructions). It 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_g.jar, which is available in Oracle client or database installations, and can be run from an IDE such as Eclipse, or from the command line thus (based on a local Oracle 12.1 installation):

java.exe -Dfile.encoding=UTF-8 -classpath C:\app\Brend_000\product\12.1.0\dbhome_1\jdbc\lib\ojdbc6_g.jar hrdemo.Driver
package hrdemo;
/***************************************************************************************************
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.

             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 Integration Testing of Oracle Procedures'
             
A Template Script for JDBC Integration Testing of Oracle Procedures
Modification History Who When Which What -------------------- ----------- ----- ------------------------------------------------------------- B. Furey 14-May-2016 1.0 Created ***************************************************************************************************/ 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/pdborcl"; 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 += 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






 

Design Patterns for Database Unit Testing 1: Web Service Saving - Design

Last October I gave a presentation on database unit testing with utPLSQL, Oracle Unit Testing with utPLSQL. I mentioned design patterns as a way of reducing the effort of building unit tests and outlined some strategies for coding them effectively. In the current set of articles, I develop the ideas further and present a design pattern for web service 'save' procedures by means of a conceptual discussion, together with a working example of base code and unit test code for a procedure to save new employees in Oracle's well-known HR demonstration schema. The working example can be used as a template for real use cases, and I believe can simplify the development process. It can also be used as a basis for comparing other unit testing frameworks, by implementing the same testing in those frameworks.

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

Design Patterns for Database Unit Testing 3: Views - Design presents another design pattern following the same ideas.

Update, 26 June 2016:The code has been re-factored to improve formatting and remove the dependence on utPLSQL, Brendan's Database Unit Testing Framework.
Update, 09 July 2016:I have added in scenario-level printing of all inputs.

General Discussion of Database Unit Testing

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

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

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

Pragmatic testing

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

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

That being so, we can note the following guidelines:

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

Design Pattern Use Case for Web Service Save Procedure

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

ERD of Input and Output Data Structures in Relation to Scenarios

Unit Testing - ERD

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

Unit Testing - ASD

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

General Unit Test Design Process

The design process involves two high level steps

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

Design Pattern Scenarios

Unit Testing - HR

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

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

Design Pattern Output Groups

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

Unit Test Results Output

The output below is for a failing run, where the time limit is breached, and I also have deliberately entered incorrected expected values for two records, to show the difference in formatting between success and failure output group records. I like to include the output tables on completion of development in my technical design document. The output below is generated by my own library packages, not directly by utPLSQL, whose output format I dislike.

UNIT TEST for UT_Emp_WS.ut_AIP_Save_Emps
========================================

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

    INPUTS
    ======

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

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

        }
        =

    OUTPUTS
    =======

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

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

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

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

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

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

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

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

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

    INPUTS
    ======

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

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

        }
        =

    OUTPUTS
    =======

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

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

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

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

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

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

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

    INPUTS
    ======

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

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

        }
        =

    OUTPUTS
    =======

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

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

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

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

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

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

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

    INPUTS
    ======

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

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

        }
        =

    OUTPUTS
    =======

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

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

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

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

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

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

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

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

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

SUMMARY for UT_Emp_WS.ut_AIP_Save_Emps
======================================

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

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

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

Notes on output

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

Conclusions

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






 

Design Patterns for Database Unit Testing 2: Web Service Saving - Code

In Design Patterns for Database Unit Testing 1: Web Service Saving - Design I presented a design pattern for unit testing web service 'save' procedures, beginning with an abstract discussion, and progressing to an example based on Oracle's HR demo schema.

That article presented the unit test scenarios, logical data structures, and the example testing output. Here, I start by listing a number of extremely prevalent antipattern approaches to database unit testing, and how to avoid them.

Following this, I list the code for the base procedure and the unit 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. All the code and installation scripts is supplied in an attachment. With these scripts, anyone can run and experiment with the unit testing example, and can use it as a template for developing real unit testing code following the same design pattern.

After the code listings, I provide some design material on the code packages used, then explain how to install and run the demo (now a link to another article).

Design Patterns for Database Unit Testing 3: Views - Design presents another design pattern following the same ideas.

Update, 26 June 2016: The code has been re-factored to improve formatting and remove the dependence on utPLSQL, Brendan's Database Unit Testing Framework.
Update, 10 July 2016: I have added in scenario-level printing of all inputs.

Unit Testing Antipatterns

Automated unit testing 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 the utPLSQL home site as examples to follow.

utPLSQL Example Procedure Test

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.

The utPLSQL examples page has this antipattern design.

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.

The utPLSQL examples page is at least suggestive of this antipattern.

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 unit 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 utPLSQL, where package level ut_Setup and ut_Teardown are mandatory. My own framework deliberately does not support these, preferring the unit 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 definitely not at the package level à la utPLSQL!), 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

ut_Emp_WSut_AIP_Save_Emps - Unit Testing Package Procedure

Declaration section

PROCEDURE ut_AIP_Save_Emps IS

  c_proc_name CONSTANT  VARCHAR2(61) := 'UT_Emp_WS.ut_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_ws_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_fields_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_ws_act_3lis           L3_chr_arr := L3_chr_arr ();

Notes on declaration section

  • Observe that the declarations of l_inp_3lis and g_ws_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 Unit Testing 1: Web Service Saving
  • 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 now printed in the output, making the testing self-documenting

Setup

  PROCEDURE Setup IS
    l_last_seq_val         PLS_INTEGER;
  BEGIN

    SELECT employees_seq.NEXTVAL
      INTO l_last_seq_val
      FROM DUAL;

    g_ws_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'))),
                                    UT_Utils.c_empty_list
                        ),
                        L2_chr_arr (UT_Utils.c_empty_list,
                                    L1_chr_arr (Utils.List_Delim (0, 'ORA-02291: integrity constraint (.) violated - parent key not found')),
                                    L1_chr_arr (Utils.List_Delim (0, 'ORA-02291: integrity constraint (.) violated - parent key not found'))
                        ),
                        L2_chr_arr (UT_Utils.c_empty_list,
                                    UT_Utils.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'))),
                                    UT_Utils.c_empty_list
                        )
                     );

  END Setup;

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)

Main testing procedure

  PROCEDURE Call_WS (p_ws_inp_2lis        L2_chr_arr,       -- input list of lists (record, field)
                     x_ws_out_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_ws_inp_2lis.COUNT LOOP
        l_emp_in_lis.EXTEND;
        l_emp_in_lis (l_emp_in_lis.COUNT) := emp_in_rec (p_ws_inp_2lis(i)(1), p_ws_inp_2lis(i)(2), p_ws_inp_2lis(i)(3), p_ws_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 => UT_Utils.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 utid = Utils.c_session_id_if_UT
       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_ws_out_2lis := L2_chr_arr (UT_Utils.List_or_Empty (l_tab_lis), UT_Utils.List_or_Empty (l_arr_lis), UT_Utils.List_or_Empty (l_err_lis));

  END Call_WS;

BEGIN

  l_timer_set := UT_Utils.Init (c_proc_name);
  Setup;
  Timer_Set.Increment_Time (l_timer_set, 'Setup');
  l_ws_act_3lis.EXTEND (c_params_3lis.COUNT);
  l_inp_3lis.EXTEND (c_params_3lis.COUNT);

  FOR i IN 1..c_params_3lis.COUNT LOOP

    Call_WS (c_params_3lis(i), l_ws_act_3lis(i));
    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;
    ROLLBACK;

  END LOOP;

  UT_Utils.Check_UT_Results (c_proc_name, c_scenario_lis, l_inp_3lis, l_ws_act_3lis, g_ws_exp_3lis, l_timer_set, c_ws_ms_limit,
                             c_inp_group_lis, c_inp_field_2lis, c_out_group_lis, c_fields_2lis);

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

Notes on main testing procedure

  • The main section is very simple: a local procedure Call_WS is called for each input scenario followed by a ROLLBACK
  • 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
  • Check_UT_Results 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
  • Call_WS 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

UT_Utils - Unit 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.
Check_UT_Results - to check results from testing

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

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

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

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

END Check_UT_Results;

Notes on Check_UT_Results

  • This is the base version of Check_UT_Results with 3-level arrays of expected and actuals
  • 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 unit test procedure will be via a single call to one of the versions of this procedure, making a big reduction in code compared with traditional unit testing approaches
  • After final assertion a call is made to write out all the results, by scenario, with all inputs printed first, followed by actuals (and expected, where they differ); this means that the unit test outputs now become precise and accurate documents of what the program does

Package Usages Diagram

Unit Testing - CSD2
Notes on packages

Call Structure Table - UT_Emp_WS.ut_AIP_Save_Emps

Level 1 Level 2 Level 3 Package
Init UT_Utils
Setup UT_Emp_WS
Increment_Time Timer_Set
Call_WS UT_Emp_WS
Do_Save UT_Emp_WS
Init_Time Timer_Set
AIP_Save_Emps UT_Emp_WS
Increment_Time Timer_Set
Get_Tab_Lis UT_Emp_WS
List_Delim Utils
Get_Arr_Lis UT_Emp_WS
List_Delim Utils
List_or_Empty UT_Utils
Check_UT_Results UT_Utils
Write_Other_Error Utils

Installation Instructions

See Brendan's Database Unit Testing Framework for a link to the installation code for both the framework and the demo code on gitHub, and links to related articles.

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.