Oracle Database 26ai will be available on generic Linux platforms in January and soon on AIX and Windows
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);
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
RUN_ID OBJECT_ID OWNER NAME TYPE LAST_DDL_TIME
------- ---------- --------- -------------- ---------- --------------------
2 74674 UWCLASS CMPLXTY_CHK FUNCTION 09-AUG-2024 15:07:50
2 74672 UWCLASS COVERAGE_TEST PROCEDURE 09-AUG-2024 15:07: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.
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;