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 for 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 your 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 to read Steven Feuerstein’s article Powerful Impact Analysis or have 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 line 27-29. All three calls to procedure p1 have been detected. That’s good and correct.

But when we add a NULL statement before the first call of p1, the calls after the NULL statement are not reported anymore.

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

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.

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.

2.2 References to Uncompiled Synonyms

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

But the usage hierarchy becomes broken when we call procedure p1via an uncompiled synonym as in the example 2.2b. The highlighted result row is referencing a non-existing usage_id. In a recursive query this result row will get lost.

This is a bug. See bug 26363026 on MOS.

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.

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.

We know that the non-CDB architecture has been deprecated with 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 line 19 to 23 you see the container identifiers and their names. On line line 37 to 40 you see that the PL/Scope identifiers for the procedure put_lineis available in every container, except PDB$SEED. So far everything still looks good.

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.

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.

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.

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:

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 a 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 dept table 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.

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 are 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 is becoming 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 to check from time to time the status of the following bugs on MOS and open 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 is storing the results after the semantic analysis, therefore each identifier comes with a context such as the 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 to 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.