My first car was a Renault R5 TX. The motor cooling of this car was really bad. On a hot summer day, it was simply not possible to drive slowly in high traffic without overheating the engine. To cool the engine you could either stop the car, open the front lid and hope for the best or turn on the heating. I decided on the latter. It was impressive how well the heating system worked on hot days. Not very pleasant to drive uphill behind a slow Dutch caravan in the Alps, but a funny experience in retrospect. My R5 was a reliable companion for years and I loved it.
When you are aware of the limitations of PL/Scope and know how to deal with them, you will find PL/Scope a very useful tool. This post is supposed to enable you to use PL/Scope more effectively. I’m fond of PL/Scope, because it may provide very reliable insights of your static PL/SQL code. I hope you are going to use PL/Scope, even if it is required sometimes “to turn on the heating”.
PL/Scope gathers at compile-time metadata about your PL/SQL code and stores it in dedicated tables. These metadata are accessible for analysis via the views dba/all/user_identfiers (since 11.1) and in dba/all/user_statements (since 12.2). If you are not familiar with PL/Scope I recommend reading Steven Feuerstein’s article Powerful Impact Analysis or having a look a the introduction chapters of the documentation. If you are fluent in German, I can recommend also Sabine Heimsath’s article Schöner Coden – PL/SQL analysieren mit PL/Scope.
The limitations covered in this post are based on Oracle Database version 12.2.0.1.170814. Most of the limitations are bugs. You may track the progress on MOS with the provided bug numbers.
1. Missing Results After NULL Statement
In the following example, we analyse the procedure p2. Look at the result of the user_identifiers query on lines 27-29. All three calls of the procedure p1 have been detected. That’s good and correct.
ALTER SESSION SET plscope_settings='identifiers:all, statements:all';
CREATE OR REPLACE PROCEDURE p1 (in_p IN INTEGER) IS
BEGIN
sys.dbms_output.put_line(sqrt(in_p));
END;
/
CREATE OR REPLACE PROCEDURE p2 IS
BEGIN
p1(4);
p1(9);
p1(16);
END;
/
SELECT usage, type, name, line, col
FROM user_identifiers
WHERE object_type = 'PROCEDURE'
AND object_name = 'P2'
ORDER BY line, col;
USAGE TYPE NAME LINE COL
----------- ------------------ ---------- ---------- ----------
DEFINITION PROCEDURE P2 1 11
DECLARATION PROCEDURE P2 1 11
CALL PROCEDURE P1 3 4
CALL PROCEDURE P1 4 4
CALL PROCEDURE P1 5 4 But when we add a NULL statement before the first call of p1, the calls after the NULL the statement are not reported anymore.
CREATE OR REPLACE PROCEDURE p2 IS
BEGIN
NULL;
p1(4);
p1(9);
p1(16);
END;
/
SELECT usage, type, name, line, col
FROM user_identifiers
WHERE object_type = 'PROCEDURE'
AND object_name = 'P2'
ORDER BY line, col;
USAGE TYPE NAME LINE COL
----------- ------------------ ---------- ---------- ----------
DECLARATION PROCEDURE P2 1 11
DEFINITION PROCEDURE P2 1 11This is a bug. See bug 24916492 on MOS.
I am not aware of a simple workaround. This means you have to change the code. In this case, it is easy, just remove the NULL statement.
The good news is, that it affects just the basic block of the NULL statement. Other basic blocks are not affected. Here’s an example of a complete result, even if a NULL statement is used. The term “basic block” has been introduced with PL/SQL Basic Block Coverage in version 12.2. However, the definition is valid for all versions of PL/SQL. I like Chris Saxon’s definition: “It’s a piece of code that either runs completely or not at all”.
CREATE OR REPLACE PROCEDURE p2 IS
BEGIN
IF FALSE THEN
NULL;
END IF;
p1(4);
p1(9);
p1(16);
END;
/
SELECT usage, type, name, line, col
FROM user_identifiers
WHERE object_type = 'PROCEDURE'
AND object_name = 'P2'
ORDER BY line, col, usage_id;
USAGE TYPE NAME LINE COL
----------- ------------------ ---------- ---------- ----------
DEFINITION PROCEDURE P2 1 11
DECLARATION PROCEDURE P2 1 11
CALL PROCEDURE P1 6 4
CALL PROCEDURE P1 7 4
CALL PROCEDURE P1 8 4In cases where PL/SQL requires at least one statement and the NULL statement is the only one, you should not have a problem. Just unnecessary usages of NULL statements might cause problems. So, make sure that you do not use unnecessary NULL statements. They are noise and may lead to incomplete code analysis results.
2. Broken Usage Hierarchy
Let’s look at the usage hierarchy of the procedure p2 in example 1c. The hierarchy level is represented in the result column usage. Three leading spaces for each sub-level. The usage_id identifies a row for an object, p2 in this case. The usage_id starts with 1 and ends with 5. There are no gaps. The column usage_context_id is part of the foreign key to the parent usage_id. Oracle decided to start the hierarchy with the non-existing usage_id 0 (zero). That’s what we use in the start_with clause. The recursive query produces the same number of result rows as the non-recursive query in example 1c. That’s important, and that’s how it should be. Always.
WITH ids AS (
SELECT usage, type, name, line, col, usage_id, usage_context_id
FROM user_identifiers ids
WHERE object_type = 'PROCEDURE'
AND object_name = 'P2'
)
SELECT lpad(' ', 3 * (level - 1)) || ids.usage AS usage,
ids.type,
ids.name,
ids.line,
ids.col,
ids.usage_id,
ids.usage_context_id
FROM ids
START WITH ids.usage_context_id = 0
CONNECT BY PRIOR ids.usage_id = ids.usage_context_id
ORDER BY ids.line, ids.col, usage_id;
USAGE TYPE NAME LINE COL USAGE_ID USAGE_CONTEXT_ID
-------------- ---------- ----- ---- ---- -------- ----------------
DECLARATION PROCEDURE P2 1 11 1 0
DEFINITION PROCEDURE P2 1 11 2 1
CALL PROCEDURE P1 6 4 3 2
CALL PROCEDURE P1 7 4 4 2
CALL PROCEDURE P1 8 4 5 2There are two reasons for broken usage hierarchies.
2.1. Static SQL Statements
Since version 12.2 PL/Scope covers static SQL statements in the user_statements view. Static SQL statements are missing in the user_identifiers view, probably for compatibility reasons. To get the full usage hierarchy you have to combine the views using UNION ALL like in line 49 of the next example 2.1a. Please note that the usages on line 25-29 are referring to the parent on line 41. A recursive query on user_identifiers only, would return just the lines 69-70 – a quite incomplete result set. Therefore you should think twice before applying your “old” PL/Scope version 11.1 scripts against an Oracle 12.2 database.
ALTER SESSION SET plscope_settings='identifiers:all, statements:all';
CREATE OR REPLACE FUNCTION f1 (in_val NUMBER) RETURN NUMBER IS
BEGIN
RETURN SQRT(in_val);
END;
/
CREATE OR REPLACE PROCEDURE p3 IS
BEGIN
UPDATE emp SET sal = sal + f1(comm);
END;
/
SELECT usage, type, name, line, col, usage_id, usage_context_id
FROM user_identifiers
WHERE object_type = 'PROCEDURE'
AND object_name = 'P3'
ORDER BY line, col, usage_id;
USAGE TYPE NAME LINE COL USAGE_ID USAGE_CONTEXT_ID
-------------- ---------- ----- ---- ---- -------- ----------------
DECLARATION PROCEDURE P3 1 11 1 0
DEFINITION PROCEDURE P3 1 11 2 1
REFERENCE TABLE EMP 3 11 4 3
REFERENCE COLUMN SAL 3 19 6 3
REFERENCE COLUMN SAL 3 25 5 3
CALL FUNCTION F1 3 31 7 3
REFERENCE COLUMN COMM 3 34 8 7
7 rows selected.
SELECT text, type, sql_id, line, col, usage_id, usage_context_id
FROM user_statements
WHERE object_type = 'PROCEDURE'
AND object_name = 'P3'
ORDER BY line, col;
TEXT TYPE SQL_ID LINE COL USAGE_ID USAGE_CONTEXT_ID
----------------------------------- ------ ------------- ---- ---- -------- ----------------
UPDATE EMP SET SAL = SAL + F1(COMM) UPDATE 8kyysdc8m75ag 3 4 3 2
WITH
ids AS (
SELECT usage, type, name, line, col, usage_id, usage_context_id
FROM user_identifiers
WHERE object_type = 'PROCEDURE'
AND object_name = 'P3'
UNION ALL
SELECT 'EXECUTE' AS usage, type, sql_id AS name, line, col, usage_id, usage_context_id
FROM user_statements
WHERE object_type = 'PROCEDURE'
AND object_name = 'P3'
)
SELECT lpad(' ', 3 * (level - 1)) || usage AS usage,
type,
name,
line,
col,
usage_id,
usage_context_id
FROM ids
START WITH usage_context_id = 0
CONNECT BY PRIOR usage_id = usage_context_id
ORDER BY line, col, usage_id;
USAGE TYPE NAME LINE COL USAGE_ID USAGE_CONTEXT_ID
--------------------- ---------- -------------- ---- ---- -------- ----------------
DECLARATION PROCEDURE P3 1 11 1 0
DEFINITION PROCEDURE P3 1 11 2 1
EXECUTE UPDATE 8kyysdc8m75ag 3 4 3 2
REFERENCE TABLE EMP 3 11 4 3
REFERENCE COLUMN SAL 3 19 6 3
REFERENCE COLUMN SAL 3 25 5 3
CALL FUNCTION F1 3 31 7 3
REFERENCE COLUMN COMM 3 34 8 72.2 References to Uncompiled Synonyms
In example 2.2a the procedure p4 calls procedure p1, but p1 is not compiled with PL/Scope. The usage hierarchy is intact. Please note that the result set contains also all usages of the parameter in_p.
ALTER SESSION SET plscope_settings='identifiers:none, statements:none';
CREATE OR REPLACE PROCEDURE p1 (in_p IN INTEGER) IS
BEGIN
sys.dbms_output.put_line(sqrt(in_p));
END;
/
ALTER SESSION SET plscope_settings='identifiers:all, statements:all';
CREATE OR REPLACE PROCEDURE p4 (in_p IN INTEGER) IS
BEGIN
p1(in_p => in_p);
END;
/
SELECT usage, type, name, line, col, usage_id, usage_context_id
FROM user_identifiers
WHERE object_type = 'PROCEDURE'
AND object_name = 'P4'
ORDER BY line, col, usage_id;
USAGE TYPE NAME LINE COL USAGE_ID USAGE_CONTEXT_ID
--------------------- ---------- -------------- ---- ---- -------- ----------------
DECLARATION PROCEDURE P4 1 11 1 0
DEFINITION PROCEDURE P4 1 11 2 1
DECLARATION FORMAL IN IN_P 1 15 3 2
REFERENCE SUBTYPE INTEGER 1 23 4 3
REFERENCE FORMAL IN IN_P 3 15 5 2But the usage hierarchy becomes broken when we call procedure p1via an uncompiled synonym as in example 2.2b. The highlighted result row references a non-existing usage_id. In a recursive query, this result row will get lost.
This is a bug. See bug 26363026 on MOS.
ALTER SESSION SET plscope_settings='identifiers:none, statements:none';
CREATE OR REPLACE SYNONYM s1 FOR p1;
ALTER SESSION SET plscope_settings='identifiers:all, statements:all';
CREATE OR REPLACE PROCEDURE p4 (in_p IN INTEGER) IS
BEGIN
s1(in_p => in_p);
END;
/
SELECT usage, type, name, line, col, usage_id, usage_context_id
FROM user_identifiers
WHERE object_type = 'PROCEDURE'
AND object_name = 'P4'
ORDER BY line, col, usage_id;
USAGE TYPE NAME LINE COL USAGE_ID USAGE_CONTEXT_ID
--------------------- ---------- -------------- ---- ---- -------- ----------------
DECLARATION PROCEDURE P4 1 11 1 0
DEFINITION PROCEDURE P4 1 11 2 1
DECLARATION FORMAL IN IN_P 1 15 3 2
REFERENCE SUBTYPE INTEGER 1 23 4 3
REFERENCE FORMAL IN IN_P 3 15 6 5You can fix such a broken usage hierarchy on the fly. Here’s simplified version of the query based on the plscope_identifiers view of the plscope-utils project. The analytic function in line 27-31 fixes invalid foreign keys. The highest preceding usage_id might not always be the best choice, but it is usually not that bad either. In this case the non-existing ‘5’ was replaced with a ‘4’ as you can see on line 53.
WITH
filtered_ids AS (
SELECT usage, type, name, line, col, usage_id, usage_context_id
FROM user_identifiers ids
WHERE object_type = 'PROCEDURE'
AND object_name = 'P4'
),
sanitized_ids AS (
SELECT fids.usage, fids.type, fids.name, fids.line, fids.col,
fids.usage_id, fids.usage_context_id,
CASE
WHEN fk.usage_id IS NOT NULL OR fids.usage_context_id = 0 THEN
'YES'
ELSE
'NO'
END AS sane_fk
FROM filtered_ids fids
LEFT JOIN filtered_ids fk
ON fk.usage_id = fids.usage_context_id
),
ids AS (
SELECT usage, type, name, line, col, usage_id,
CASE
WHEN sane_fk = 'YES' THEN
usage_context_id
ELSE
last_value(CASE WHEN sane_fk = 'YES' THEN usage_id END)
IGNORE NULLS OVER (
ORDER BY line, col
ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
)
END AS usage_context_id -- fix broken hierarchies
FROM sanitized_ids
)
SELECT lpad(' ', 3 * (level - 1)) || usage AS usage,
type,
name,
line,
col,
usage_id,
usage_context_id
FROM ids
START WITH usage_context_id = 0
CONNECT BY PRIOR usage_id = usage_context_id
ORDER BY line, col, usage_id;
USAGE TYPE NAME LINE COL USAGE_ID USAGE_CONTEXT_ID
--------------------- ---------- -------------- ---- ---- -------- ----------------
DECLARATION PROCEDURE P4 1 11 1 0
DEFINITION PROCEDURE P4 1 11 2 1
DECLARATION FORMAL IN IN_P 1 15 3 2
REFERENCE SUBTYPE INTEGER 1 23 4 3
REFERENCE FORMAL IN IN_P 3 15 6 43 Missing Usages of Objects in CDB$ROOT
PL/Scope stores by default the metadata for the following SYS packages:
- DBMS_STANDARD
- STANDARD
If you want to analyse the usage of other supplied PL/SQL packages, you need to compile these package with PL/Scope settings first. The next example shows how to do that. On line 29 and 30 the reference to the package dbms_output and its procedure put_line are properly reported. So far so good.
CONNECT sys/oracle@odb AS SYSDBA
ALTER SESSION SET plscope_settings='identifiers:all, statements:all';
ALTER PACKAGE dbms_output COMPILE;
CONNECT scott/tiger@odb
ALTER SESSION SET plscope_settings='identifiers:all, statements:all';
CREATE OR REPLACE PROCEDURE p5 (in_p IN VARCHAR2) IS
BEGIN
sys.dbms_output.put_line(in_p);
END;
/
SELECT usage, type, name, line, col, usage_id, usage_context_id
FROM user_identifiers
WHERE object_type = 'PROCEDURE'
AND object_name = 'P5'
ORDER BY line, col, usage_id;
USAGE TYPE NAME LINE COL USAGE_ID USAGE_CONTEXT_ID
----------- ------------------ ----------- ---- ---- -------- ----------------
DECLARATION PROCEDURE P5 1 11 1 0
DEFINITION PROCEDURE P5 1 11 2 1
DECLARATION FORMAL IN IN_P 1 15 3 2
REFERENCE CHARACTER DATATYPE VARCHAR2 1 23 4 3
REFERENCE PACKAGE DBMS_OUTPUT 3 8 5 2
CALL PROCEDURE PUT_LINE 3 20 6 5
REFERENCE FORMAL IN IN_P 3 29 7 6We know that the non-CDB architecture has been deprecated with Oracle 12.2. So let’s try the same with the recommended CDB architecture. In the next example we compile the dbms_output package within the CDB$ROOT container, which owns this package. Compiling it in a PDB is not possible (it does not throw an error, but it simply has no effect). On lines 19 to 23 you see the container identifiers and their names. On lines 37 to 40 you see that the PL/Scope identifiers for the procedure put_line are available in every container, except PDB$SEED. So far everything still looks good.
CONNECT sys/oracle@ocdb AS SYSDBA
SHOW con_name
CON_NAME
------------------------------
CDB$ROOT
ALTER SESSION SET plscope_settings='identifiers:all, statements:all';
ALTER PACKAGE dbms_output COMPILE;
SELECT con_id, name
FROM v$containers
ORDER BY con_id;
CON_ID NAME
------ -----------
1 CDB$ROOT
2 PDB$SEED
3 OPDB1
4 OPDB2
5 OPDB3
SELECT usage, type, name, line, col, origin_con_id, con_id
FROM cdb_identifiers
WHERE OWNER = 'SYS'
AND object_type = 'PACKAGE'
AND object_name = 'DBMS_OUTPUT'
AND TYPE = 'PROCEDURE'
AND USAGE = 'DECLARATION'
AND NAME = 'PUT_LINE'
ORDER BY con_id;
USAGE TYPE NAME LINE COL ORIGIN_CON_ID CON_ID
----------- ------------------ ----------- ---- ---- ------------- ------
DECLARATION PROCEDURE PUT_LINE 83 13 1 1
DECLARATION PROCEDURE PUT_LINE 83 13 1 3
DECLARATION PROCEDURE PUT_LINE 83 13 1 4
DECLARATION PROCEDURE PUT_LINE 83 13 1 5Now we are ready to create our procedure p5 which is using dbms_output in user SCOTT. However, the PL/Scope result is incomplete. Only 5 instead of 7 rows are reported. The two usages of dbms_output are missing.
This is a bug. See bug 26169004 on MOS.
CONNECT scott/tiger@opdb1
SHOW con_name
CON_NAME
------------------------------
OPDB1
ALTER SESSION SET plscope_settings='identifiers:all, statements:all';
CREATE OR REPLACE PROCEDURE p5 (in_p IN VARCHAR2) IS
BEGIN
sys.dbms_output.put_line(in_p);
END;
/
SELECT usage, type, name, line, col, usage_id, usage_context_id
FROM user_identifiers
WHERE object_type = 'PROCEDURE'
AND object_name = 'P5'
ORDER BY line, col, usage_id;
USAGE TYPE NAME LINE COL USAGE_ID USAGE_CONTEXT_ID
----------- ------------------ ----------- ---- ---- -------- ----------------
DECLARATION PROCEDURE P5 1 11 1 0
DEFINITION PROCEDURE P5 1 11 2 1
DECLARATION FORMAL IN IN_P 1 15 3 2
REFERENCE CHARACTER DATATYPE VARCHAR2 1 23 4 3
REFERENCE FORMAL IN IN_P 3 29 5 2I see the following workarounds:
- Use a non-CDB database for PL/Scope analysis
- Do the analysis in the
CDB$ROOTcontainer
4. Missing Identifiers
If you are analysing the usages of identifiers, e.g. to check if a declared identifier is used, then you will report false positives if PL/Scope does not report all identifier usages. See line 6 in the next example. The identifier l_stmt is referenced in the execute immediate statement, but the usage is not reported.
This is a bug. See bug 26351814 on MOS.
ALTER SESSION SET plscope_settings='identifiers:all, statements:all';
CREATE OR REPLACE PROCEDURE p6 IS
l_stmt VARCHAR2(100) := 'BEGIN NULL; END;';
BEGIN
EXECUTE IMMEDIATE l_stmt;
END;
/
SELECT usage, type, name, line, col, usage_id, usage_context_id
FROM user_identifiers
WHERE object_type = 'PROCEDURE'
AND object_name = 'P6'
ORDER BY line, col, usage_id;
USAGE TYPE NAME LINE COL USAGE_ID USAGE_CONTEXT_ID
----------- ------------------ ----------- ---- ---- -------- ----------------
DECLARATION PROCEDURE P6 1 11 1 0
DEFINITION PROCEDURE P6 1 11 2 1
DECLARATION VARIABLE L_STMT 2 4 3 2
ASSIGNMENT VARIABLE L_STMT 2 4 5 3
REFERENCE CHARACTER DATATYPE VARCHAR2 2 11 4 3There is no feasible workaround. Ok, you could use a third-party parser to verify the result, but that’s an extreme measure and a lot of work. I really hope Oracle is going to fix this bug soon.
5. Wrong Usages
In the next example the usage of the parameter in_pin the if statement is reported as DEFINITION instead of REFERENCE on line 23.
ALTER SESSION SET plscope_settings='identifiers:all, statements:all';
CREATE OR REPLACE PROCEDURE p7 (in_p IN BOOLEAN) IS
BEGIN
IF in_p THEN
NULL;
END IF;
END;
/
SELECT usage, type, name, line, col, usage_id, usage_context_id
FROM user_identifiers
WHERE object_type = 'PROCEDURE'
AND object_name = 'P7'
ORDER BY line, col, usage_id;
USAGE TYPE NAME LINE COL USAGE_ID USAGE_CONTEXT_ID
----------- ------------------ ----------- ---- ---- -------- ----------------
DECLARATION PROCEDURE P7 1 11 1 0
DEFINITION PROCEDURE P7 1 11 2 1
DECLARATION FORMAL IN IN_P 1 15 3 2
REFERENCE BOOLEAN DATATYPE BOOLEAN 1 23 4 3
DEFINITION FORMAL IN IN_P 3 7 5 2This is a bug. See bug 20056796 on MOS.
Since a DEFINITION for a FORMAL IN type does not make sense, you can just replace all occurrences as follows:
SELECT CASE
WHEN type = 'FORMAL IN' AND usage = 'DEFINITION' THEN
'REFERENCE'
ELSE
usage
END AS usage, type, name, line, col, usage_id, usage_context_id
FROM user_identifiers
WHERE object_type = 'PROCEDURE'
AND object_name = 'P7'
ORDER BY line, col, usage_id;
USAGE TYPE NAME LINE COL USAGE_ID USAGE_CONTEXT_ID
----------- ------------------ ----------- ---- ---- -------- ----------------
DECLARATION PROCEDURE P7 1 11 1 0
DEFINITION PROCEDURE P7 1 11 2 1
DECLARATION FORMAL IN IN_P 1 15 3 2
REFERENCE BOOLEAN DATATYPE BOOLEAN 1 23 4 3
REFERENCE FORMAL IN IN_P 3 7 5 2However, when you look at the bug description, you will also find examples for the following additional wrong usage reports:
- an
ASSIGNMENTinstead of aREFERENCE - a
REFERENCEinstead of anASSIGNMENT
For these wrong usages it might not be so simple to work around it. You have to find a solution on an on-case basis until Oracle provides a bug fix.
6. Missing Usages and Structures in Static SQL Statements
In example 6a the procedure p8 creates a new row in the table dept in a rather awkward manner. In line 31 the analysis query adds the table name to the column name. Something like that is necessary if column names are not unique across tables.
However, the result has some flaws. Let’s look at the line 59-70. They are all direct descendants of the insert statement.
ALTER SESSION SET plscope_settings='identifiers:all, statements:all';
CREATE OR REPLACE FUNCTION f2 (in_name IN VARCHAR2) RETURN VARCHAR2 IS
BEGIN
RETURN in_name || ' CITY';
END;
/
CREATE OR REPLACE PROCEDURE p8 IS
BEGIN
INSERT INTO dept (deptno, dname, loc)
WITH
silly_data AS (
SELECT job || substr(ename, 1, 0) AS loc,
ename AS dname,
deptno + 20 AS deptno
FROM emp
WHERE comm IS NOT NULL
AND ROWNUM = 1
)
SELECT deptno, dname, f2(loc)
FROM silly_data;
END;
/
WITH
ids AS (
SELECT i.usage, i.type,
CASE
WHEN i.type = 'COLUMN' THEN
p.object_name || '.' || i.name
ELSE
i.name
END AS name, i.line, i.col, i.usage_id, i.usage_context_id
FROM user_identifiers i
JOIN user_identifiers p
ON p.signature = i.signature
AND p.usage = 'DECLARATION'
WHERE i.object_type = 'PROCEDURE'
AND i.object_name = 'P8'
UNION ALL
SELECT 'EXECUTE' AS usage, type, sql_id AS name, line, col, usage_id, usage_context_id
FROM user_statements
WHERE object_type = 'PROCEDURE'
AND object_name = 'P8'
)
SELECT lpad(' ', 3 * (level - 1)) || usage AS usage,
type, name, line, col, usage_id, usage_context_id
FROM ids
START WITH usage_context_id = 0
CONNECT BY PRIOR usage_id = usage_context_id
ORDER BY usage_id;
USAGE TYPE NAME LINE COL USAGE_ID USAGE_CONTEXT_ID
--------------------- ------------------ ------------- ---- ---- -------- ----------------
DECLARATION PROCEDURE P8 1 11 1 0
DEFINITION PROCEDURE P8 1 11 2 1
EXECUTE INSERT 7dkawtszvu0a8 3 4 3 2
REFERENCE TABLE EMP 9 17 4 3
REFERENCE COLUMN EMP.COMM 10 17 5 3
REFERENCE COLUMN EMP.DEPTNO 8 17 6 3
REFERENCE COLUMN EMP.ENAME 7 17 7 3
REFERENCE COLUMN EMP.ENAME 6 31 8 3
REFERENCE COLUMN EMP.JOB 6 17 9 3
REFERENCE COLUMN EMP.ENAME 13 19 10 3
REFERENCE TABLE DEPT 3 16 11 3
REFERENCE COLUMN DEPT.LOC 3 37 12 3
REFERENCE COLUMN DEPT.DNAME 3 30 13 3
REFERENCE COLUMN DEPT.DEPTNO 3 22 14 3
CALL FUNCTION F2 13 26 16 3How do we identify the target table of the insert statement? Is it emp or is it dept? Ok, when we order the result by line and column instead of usage_id, we could know based on the SQL grammar that dept must be a target table. But what about emp? Could that be the target of a mutitable insert statement? Probably not since there is no other table to query data from, right? Probably right, but if we’d query data from a table function which is not compiled with PL/Scope emp could still be a target of a multitable insert statement. A different usage might help, but unfortunately, all table and column usages within static SQL statements are reported as REFERENCE. This is not a bug. If we want to change that we have to file an enhancement request.
Identifying target columns, source columns or columns used to filter data is impossible with PL/Scope alone. You need a SQL parser or tools using such a parser for deeper static code analysis.
Even if you are interested in the column usages only, you have to be aware that column-less access to tables is possible, e.g. when omitting the column list in the insert_into_clause. In such cases, all visible columns of the target table are used. If synonyms and views are used, the analysis becomes a bit harder.
Nonetheless, the metadata provided through user_statementscompletes the missing pure PL/SQL analysis reporting capabilities for PL/SQL identifiers. Now all usages of PL/SQL identifiers are reported with a static SQL statement context – if they have one. E.g the use of the function f2 within the insert statement on line 70. And that alone is very useful.
7. Summary
Most of the limitations mentioned in this post are based on bugs. Hence I recommend checking from time to time the status of the following bugs on MOS and opening a SR when you are unable to produce a correct analysis result due to PL/Scope bugs.
- Bug 24916492: PLSCOPE_SETTINGS DOESN’T PARSE IDENTIFIER INFORMATION AFTER NULL STATEMENT
- Bug 26363026: WRONG RESULT IN PL/SCOPE HIERARCHICAL QUERY USING SYNONYM
- Bug 26351814: EXECUTE IMMEDIATE STATEMENT IDENTIFIER REFERENCE NOT COLLECTED BY PL/SCOPE
- Bug 26169004: PL/SCOPE DOES NOT DETECT USAGES OF CDB OBJECTS SUCH AS SYS.DBMS_SQL
- Bug 20056796 : PLSCOPE SHOWS WRONG USAGE OF IDENTIFIERS
Some limitations of PL/Scope are by design. In the end, PL/Scope provides just information about identifiers, a subset of data produced during parse time and not a complete parse tree, which would be desirable for complex static code analysis. However, if you just want to analyse the use of identifiers in your PL/SQL code, you should consider using PL/Scope. PL/Scope stores the results after the semantic analysis, therefore each identifier comes with a context such as a schema, nested program unit, etc. Even if you need a third-party tool for static code analysis, PL/Scope might be helpful to verify or complete the result.
Before you start developing your own PL/Scope queries from scratch, have a look at plscope-utils. There are predefined views which address some of the mentioned limitations out of the box. There’s also a SQL Developer plugin which works against any database version with PL/Scope.
4 Comments
There is one more issue I found dealing with PL/Scope raw data. If some routine has parameter declared via %TYPE reference – there is no way to determine the resulting parameter type out of PL/Scope hierarchy. The last dead-end leaf node is an “ASSIGNMENT” record of the declared (with %TYPE) routine parameter, where “usage_id” could not be tracked further as “usage_context_id” of some other record regarding this param. Has anyone a proper workaround of this issue? Thanks!
You find the resolved parameter type in the column
data_typeofuser_arguments.Yes, it is possible to calculate subprogram_id and overload number over the identifiers data using window functions, then look into user/all/dba_arguments view for a resolved routine signature. But, this approach is only applicable to PACKAGE SPEC. What would you do if you need to determine full routine signature (set of param name-type pairs) in case this routine defined exclusively in PACKAGE BODY and has at least one %TYPE param?
Can you please provide a simple example? There are some options with
%TYPE. Thanks.