Skip to main content

Unit testing in database - Gamification


Welcome to the last part of the unit testing serial, the gamification part. If you are still reading this then congratulations, you have the most boring parts behind you.

How to keep writing tests fun?

Let's think about it. As a leader/manager you (should) want your code covered by meaningful tests which won't costs you a lot of money. You don't want useless tests. As a developer you don't want to create too many tests but you want to cover at least the most risky/fragile parts of your code.

Gamification is the answer.

I created some packages for a tower defence game. Then I want to make sure I wont break my code in the future or at least I would be notified when I do before it reaches users. My procedures are usually short but there are some exceptions. Also they usually do more than one thing so they require more than one test. I wrote some tests and realised it is like being half blind. That led me to the code coverage feature (DBMS_PLSQL_CODE_COVERAGE) introduced in Oracle 12.2.

Theory is simple. You start code coverage, run your tests, stop coverage and check not covered rows and write tests for those. As usual it is more complicated in real life. First, I found the results of code coverage inconsistent, unreliable. Maybe I am doing something wrong but not all rows in code coverage report which were accessed by test are marked as covered. So I turned into DBMS_PROFILER with pretty much same results. I somehow don't trust it either. I studied the reports for a while. I was thinking about trying also DBMS_HPROF but I don't want to deal with files and I didn't grasp the file less method yet. Maybe DBMS_TRACE would be useful? So I merged Code Coverage and Profiler reports. It is not perfect but it is close enough. If you come up with a better method please let me know.

    coverage_id     PLS_INTEGER;
    DBMS_OUTPUT.PUT_LINE('COVERAGE_ID: ' || coverage_id);

It is a good practice to set session parameters and force recompile schema before start. Also avoid NULL statements in your code.

ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL  = 1;  -- to not to shift lines

Code coverage vs Profiler issues

You may wonder which one is better? Which one should I use?

You can check the code coverage output:

    s.type,, s.line, b.run_id, b.block,
    --LISTAGG(b.col, ', ') WITHIN GROUP (ORDER BY b.col) OVER (PARTITION BY b.run_id, b.object_id, b.line) AS cols,
    b.col, b.covered, s.text
FROM user_source s
LEFT JOIN dbmspcc_units u
    ON           =
    AND u.type          = s.type
LEFT JOIN dbmspcc_blocks b
    ON b.run_id         = u.run_id
    AND b.object_id     = u.object_id
    AND b.line          = s.line
WHERE            = 'PLAYER'          -- your package
    AND s.type          = 'PACKAGE BODY'
    AND u.run_id        = (SELECT MAX(r.run_id) FROM dbmspcc_runs r)
ORDER BY s.line, b.col;

You can check profiler output:

    u.runid, u.unit_type, u.unit_name,
    d.line#, d.total_occur, d.total_time, d.min_time, d.max_time,
    s.text AS source_line
FROM plsql_profiler_units u
JOIN plsql_profiler_data d
    ON u.runid          = d.runid
    AND u.unit_number   = d.unit_number
JOIN all_source s
    ON s.owner          = u.unit_owner
    AND          = u.unit_name
    AND s.type          = u.unit_type
    AND s.line          = d.line#
WHERE u.runid           = (SELECT MAX(u.runid) FROM plsql_profiler_runs u)
    AND u.unit_owner    = USER
    AND          = 'PLAYER'          -- your package
    AND s.type          = 'PACKAGE BODY'
ORDER BY u.unit_number, d.line#;

Now lets check the code coverage vs profiler coverage:

    SELECT u.unit_name AS name, d.line# AS line
    FROM plsql_profiler_units u
    JOIN plsql_profiler_data d
        ON d.runid          = u.runid
        AND d.unit_number   = u.unit_number
        --AND d.total_occur   > 0
        --AND d.total_time    > 0
    WHERE u.runid           = (SELECT MAX(u.runid) FROM plsql_profiler_runs u)
        AND u.unit_type     = 'PACKAGE BODY'
        AND u.unit_owner    = USER
        AND u.unit_name     NOT LIKE '%\_UT' ESCAPE '\'
    GROUP BY u.unit_name, d.line#
    --ORDER BY u.unit_name, d.line#
c AS (
    SELECT, b.line
    FROM dbmspcc_units u
    JOIN dbmspcc_blocks b
        ON  b.run_id    = u.run_id
        AND b.object_id = u.object_id
    WHERE u.run_id      = (SELECT MAX(r.run_id) FROM dbmspcc_runs r)
        AND u.type      = 'PACKAGE BODY'
        AND b.covered   = 1
    GROUP BY, b.line
    --ORDER BY, b.line
r AS (
    SELECT, s.line, p.line AS profiler_, c.line AS coverage_, s.text
    FROM user_source s
        ON   =
        AND p.line  = s.line
        ON   =
        AND c.line  = s.line
    WHERE s.type    = 'PACKAGE BODY'
        AND NOT REGEXP_LIKE(s.text, '^\s*([-][-].*)?$')     -- ignore empty line
        AND NOT REGEXP_LIKE(s.text, '^\s*(END[\s;])')       -- ignore END;
        AND NOT REGEXP_LIKE(s.text, '^\s*(BEGIN)')          -- ignore BEGIN
    COUNT(           AS total_lines,
    COUNT(r.profiler_)      AS prof_lines,  ROUND(COUNT(r.profiler_) / COUNT( * 100, 2) AS prof_perc,
    COUNT(r.coverage_)      AS cov_lines,   ROUND(COUNT(r.coverage_) / COUNT( * 100, 2) AS cov_perc
WHERE IN ('PLAYER')  -- your package(s)

The secret

Following query will give you a simple ascii chart with the percentage of code covered by tests. One line per package. This should get you motivated to get the percentage up. This should give you the juice to continue in your journey (assuming your packages have "_UT" postfix). And if not, you have to come up with a game which works for you.

    u.type                                                              AS object_type,                                                              AS object_name,
    MAX(t.tests)                                                        AS unit_tests,
    ROUND((SUM(b.covered) / COUNT(*) * 100), 2)                         AS covered_perc,
    RPAD(LPAD('[]', (SUM(b.covered) / COUNT(*) * 100), '.'), 100, '.')  AS covered_graph
FROM dbmspcc_runs r
JOIN dbmspcc_units u
    ON r.run_id         = u.run_id
JOIN dbmspcc_blocks b
    ON r.run_id         = b.run_id
    AND u.object_id     = b.object_id
        REPLACE(c.object_name, '_UT', '')   AS object_name,
        COUNT(a.subobject_name)             AS tests
    FROM ut3.ut_annotation_cache_info c
    JOIN ut3.ut_annotation_cache a
        ON c.object_owner       = USER
        AND a.cache_id          = c.cache_id
    WHERE a.annotation_name     = 'test'
    GROUP BY REPLACE(c.object_name, '_UT', '')
) t
    ON t.object_name    =
WHERE r.run_id          = (SELECT MAX(run_id) FROM dbmspcc_runs)
    AND b.not_feasible  = 0
    AND IN (
        SELECT DISTINCT REPLACE(object_name, '_UT', '') AS target_package
        FROM all_procedures
        WHERE object_name LIKE '%\_UT' ESCAPE '\'
GROUP BY u.type,
ORDER BY 1, 2;

Create this SELECT statement as ut_code_coverage_report view. Then you can check the charts everytime you run tests.

    in_name             CONSTANT VARCHAR2(30) := 'UNIT_TESTS';
    coverage_id         PLS_INTEGER;
    profiler_id         PLS_INTEGER;
    coverage_id := DBMS_PLSQL_CODE_COVERAGE.START_COVERAGE(in_name);
    DBMS_PROFILER.START_PROFILER(in_name, run_number => profiler_id);

    -- run all available unit tests;

    -- stop

    -- show coverage
    DBMS_OUTPUT.PUT_LINE('CODE COVERAGE: ' || coverage_id);
    FOR c IN (
        SELECT, c.covered_graph
        FROM ut_code_coverage_report c
        ORDER BY 1
    ) LOOP
        DBMS_OUTPUT.PUT_LINE(LPAD(, 10) || ' ' || c.covered_graph);

Final advice

Keep in mind that there is no point in doing tests just to increase the numbers. You should always put the quality of your tests first.

Final level, the boss

Now you are at the last level and facing the boss. Literally. It is horrifying.

I have had and I still have ongoing troubles convincing pretty much all of my bosses to invest some time into unit tests. Unsuccessfully. Your boss might be more open minded, but you should prepare good arguments why your work will now take (optimistically) twice as long.

Good luck.

Update (Dec 2021):

I have been neglecting Office Hours lately, so I missed this video from Jacek Gebal and Samuel Nitsche about Testing with PL/SQL