Oracle DBMS_PLSQL_CODE_COVERAGE
Version 21c

General Information
Library Note Morgan's Library Page Header
Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
Purpose Collects code coverage data for PL/SQL applications at the basic block level. A basic block is defined as a single entry single exit block of PL/SQL code in a user's program.

The user must supply a collection of filter_list_elem which is a schema, unit name pair for the list of units that the user is interested in gathering coverage information about.
AUTHID CURRENT_USER
Constants
Name Data Type Value
 Package spec
package_spec_namespace NUMBER 1
package_body_namespace NUMBER 2
trigger_namespace NUMBER 3
 Others
function_namespace NUMBER 1
procedure_namespace NUMBER 1
type_spec_namespace NUMBER 1
type_body_namespace NUMBER 2
Data Types type map_rec is record(
/* Name of the procedure containing the basic block */
procedure_name varchar2(32767),
/* Identifies the basic block */
block_num number,
/* Starting line of the basic block */
line number,
/* Starting column of the basic block */
col number,
/* Not_feasible marking of the basic block */
not_feasible number);

TYPE t_map_rec is table of map_rec;
Dependencies
DBMS_ASSERT DBMS_STANDARD PLSQL_CODE_COVERAGE_RUNNUMBER
DBMS_PLSQL_CODE_COVERAGE_LIB PLITBLM USER_OBJECTS
Documented Yes
Exceptions
Error Code Reason
ORA-08402 Coverage error
First Available 12.2
Security Model Owned by SYS with EXECUTE granted to PUBLIC
Source {ORACLE_HOME}/rdbms/admin/dbmscov.sql
Subprograms
 
CREATE_COVERAGE_TABLES
This procedure creates the coverage tables dbms_plsql_code_coverage.create_coverage_tables(force_it IN BOOLEAN := FALSE);
exec dbms_plsql_code_coverage.create_coverage_tables(TRUE);

SELECT object_name, created
FROM dba_objects
WHERE created > sysdate-1/360;

OBJECT_NAME                    CREATED
------------------------------ --------------------
DBMSPCC_RUNS                   21-DEC-2020 01:19:53
DBMSPCC_RUNS_PK                21-DEC-2020 01:19:53
DBMSPCC_UNITS                  21-DEC-2020 01:19:53
DBMSPCC_UNITS_PK               21-DEC-2020 01:19:53
DBMSPCC_BLOCKS                 21-DEC-2020 01:19:53
DBMSPCC_BLOCKS_PK              21-DEC-2020 01:19:53


6 rows selected.
 
GET_BLOCK_MAP
Returns the mapping of basic blocks to PL/SQL source dbms_plsql_code_coverage.get_block_map(
unit_owner IN VARCHAR2 DEFAULT NULL,
unit_name  IN VARCHAR2,
namespace  IN POSITIVE)
RETURN t_map_rec;
TBD
 
START_COVERAGE
Starts coverage data collection at the basic block level in the user's session dbms_plsql_code_coverage.start_coverage(run_comment IN VARCHAR2) RETURN NUMBER;
SELECT dbms_plsql_code_coverage.start_coverage('Code Coverage Run 1') AS START_CVG
FROM dual;

START_CVG
---------
       16
 
STOP_COVERAGE
Stopping coverage flushes the coverage data to a file dbms_plsql_code_coverage.stop_coverage;
exec dbms_plsql_code_coverage.stop_coverage;
 
Demo
PL/SQL developers want to know how well their test infrastructure exercised their code. A typical code coverage run in a session will look like this conn uwclass/uwclass@pdbdev

-- the following function based on Oracle's verify_function from catpvf.sql
CREATE OR REPLACE FUNCTION cmplxty_chk(pwd IN VARCHAR2) RETURN BOOLEAN AUTHID DEFINER IS
 ch         CHAR(1);
 cnt_digit  INTEGER := 0;
 cnt_letter INTEGER := 0;
 cnt_lower  INTEGER := 0;
 cnt_upper  INTEGER := 0;
 cnt_specl  INTEGER := 0;
 len        INTEGER;
BEGIN
  len := LENGTH(pwd);
  FOR i IN 1..len LOOP
    ch := SUBSTR(pwd, i, 1);
    IF ch = '"'  THEN
      EXIT;
    ELSIF REGEXP_INSTR(ch, '[[:alnum:]]') > 0 THEN
      IF  REGEXP_INSTR(ch, '[[:digit:]]') > 0 THEN
        cnt_digit := cnt_digit + 1;
      END IF;
      IF REGEXP_INSTR(ch, '[[:alpha:]]') > 0 THEN
        cnt_letter := cnt_letter + 1;
        IF regexp_instr(ch, '[[:lower:]]') > 0 THEN
          cnt_lower := cnt_lower + 1;
        END IF;
        IF regexp_instr(ch, '[[:upper:]]') > 0 THEN
          cnt_upper := cnt_upper + 1;
        END IF;
      END IF;
    ELSE
      cnt_specl := cnt_specl + 1;
    END IF;
  END LOOP;

  IF cnt_digit < 1 THEN
    dbms_output.put_line('No Digits');
    RETURN FALSE;
  END IF;
  IF cnt_letter < 1 THEN
    dbms_output.put_line('No Letters');
    RETURN FALSE;
  END IF;
  IF cnt_lower < 1 THEN
    dbms_output.put_line('No Lower Case');
    RETURN FALSE;
  END IF;
  IF cnt_upper < 1 THEN
    dbms_output.put_line('No Upper Case');
    RETURN FALSE;
  END IF;
  IF cnt_specl < 1 THEN
    dbms_output.put_line('No Special Characters');
    RETURN FALSE;
  END IF;

  RETURN TRUE;
END cmplxty_chk;
/

CREATE OR REPLACE PROCEDURE coverage_test(password IN VARCHAR2) AUTHID DEFINER IS
BEGIN
  IF cmplxty_chk(password) THEN
    dbms_output.put_line('T');
  ELSE
    dbms_output.put_line('F');
  END IF;
END coverage_test;
/

DECLARE
 retVal NUMBER;
BEGIN
  dbms_plsql_code_coverage.create_coverage_tables(TRUE);
  retVal := dbms_plsql_code_coverage.start_coverage('Start Demo');
  dbms_output.put_line(retVal);

  coverage_test('M0rGaN');

  dbms_plsql_code_coverage.stop_coverage;
END;
/
2
No Speical Characters
F


col run_comment format a20
col run_owner format a20
col owner format a15
col name format a20

SELECT * FROM dbmspcc_runs;

 RUN_ID RUN_COMMENT          RUN_OWNER            RUN_TIMESTAMP
------- -------------------- -------------------- --------------------
      2 Start Demo           UWCLASS              21-DEC-2020 17:29:04



SELECT * FROM dbmspcc_units;

 RUN_ID  OBJECT_ID OWNER     NAME           TYPE       LAST_DDL_TIME
------- ---------- --------- -------------- ---------- --------------------
      2      74674 UWCLASS   CMPLXTY_CHK    FUNCTION   21-DEC-2020 17:28:50
      2      74672 UWCLASS   COVERAGE_TEST  PROCEDURE  21-DEC-2020 17:28:52


-- note in the following query the columns covered and not_feasible, these are critical.
-- covered=1 means the code was executed and tested.
-- not_feasible=1 would indicate that there was no feasible way to test the code.
-- compare the highlighted lines in the following query and the query below it that
-- focuses on the specific lines not covered but feasible to cover through testing.


SELECT * FROM dbmspcc_blocks;

 RUN_ID  OBJECT_ID  BLOCK  LINE  COL  COVERED NOT_FEASIBLE
------- ---------- ------ ----- ---- -------- ------------
      2      74674      1     1    1        1            0
      2      74674      2    11    3        1            0
      2      74674      3    12    5        1            0
      2      74674      4    15   43        1            0
      2      74674      5    16   43        1            0
      2      74674      6    17    9        1            0
      2      74674      7    19   42        1            0
      2      74674      8    20    9        1            0
      2      74674      9    22   11        1            0
      2      74674     10    24   44        1            0
      2      74674     11    25   11        1            0
      2      74674     12    29    7        0            0
      2      74674     13    33   16        1            0
      2      74674     14    34    5        0            0
      2      74674     15    37   17        1            0
      2      74674     16    38    5        0            0
      2      74674     17    41   16        1            0
      2      74674     18    42    5        0            0
      2      74674     19    45   16        1            0
      2      74674     20    46    5        0            0
      2      74674     21    49   16        1            0
      2      74674     22    50    5        1            0
      2      74674     23    54    3        0            0
      2      74672      1     1    1        1            0
      2      74672      2     4    5        0            0
      2      74672      3     6    5        1            0


col object_name format a15
col text format a45

WITH q AS (SELECT db.object_id, db.line, db.not_feasible
           FROM dbmspcc_blocks db
           WHERE db.covered = 0
           AND db.run_id = 2)
SELECT uo.object_name, q.line, us.text, q.not_feasible
FROM user_source us, user_objects uo, q
WHERE uo.object_id = q.object_id
AND uo.object_name = us.name
AND us.line = q.line
ORDER BY 1,2;

OBJECT_NAME     LINE TEXT                                          NOT_FEASIBLE
-------------- ----- --------------------------------------------- ------------
CMPLXTY_CHK       29       cnt_specl := cnt_specl + 1;                        0
CMPLXTY_CHK       34     dbms_output.put_line('No Digits');                   0
CMPLXTY_CHK       38     dbms_output.put_line('No Letters');                  0
CMPLXTY_CHK       42     dbms_output.put_line('No Lower Case');               0
CMPLXTY_CHK       46     dbms_output.put_line('No Upper Case');               0
CMPLXTY_CHK       54   RETURN TRUE;                                           0
COVERAGE_TEST      4     dbms_output.put_line('T');                           0

Related Topics
Built-in Functions
Built-in Packages
Directories
What's New In 19c
What's New In 20c-21c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2019 Daniel A. Morgan All Rights Reserved
  DBSecWorx