Limitations of PL/Scope and How to Deal with Them

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.

Example 1a – Complete Result
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.

Example 1b – Missing Calls in Result
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         11

This 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”.

Example 1c – Complete Result With NULL Statement
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          4

In 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.

Example 2a – Intact Usage Hiearchy
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                2

There are two reasons for broken usage hierarchies.

  1. Static SQL statements (expected behaviour)
  2. References to uncompiled synonyms (bug)

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.

Example 2.1a – Combine Identifiers and Statements
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                7

2.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.

Example 2.2a – Intact Usage Hierarchy
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                2

But 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.

Example 2.2b – Broken Usage Hierarchy by Uncompiled Synonym
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                5

You 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.

Example 2.2c – Fix Broken Usage Hierarchies
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                4

3 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.

Example 3a – Non-CDB Architecture – Complete Result
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                6

We 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.

Example 3b – CDB-Architecture – Compile DBMS_OUTPUT
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      5

Now 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.

Example 3c – CDB Architecture – Incomplete Result in PDB
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                2

I see the following workarounds:

  • Use a non-CDB database for PL/Scope analysis
  • Do the analysis in the CDB$ROOT container

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.

Example 4a – Missing Usage of L_STMT
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                3

There 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.

Example 5a – Wrong Usage of IN_P
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                2

This 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:

Example 5b – Fix Wrong Usage of IN_P
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                2

However,  when you look at the bug description, you will also find examples for the following additional wrong usage reports:

  • an ASSIGNMENT instead of a REFERENCE
  • a REFERENCE instead of an ASSIGNMENT

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.

Example 6a – Limited Analysis Capabilities of Static SQL
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                3

How 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

  1. blank Andrii Oseledko says:

    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!

  2. blank Andrii Oseledko says:

    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?

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.