ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling
complimentary technical Workshops on Database Security for the first 30
Oracle Database customers located anywhere in North America, EMEA, LATAM, or
APAC that send an email to
asra_us@oracle.com. Request a Workshop for
your organization today.
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
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.
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;
