This post was originally published on this site


PL/Scope is an SDK for source code analysis. It is available since Oracle Database 11g Release 1 and has been significantly improved in 12c Release 2.

plscope-utils for SQL Developer is a SQL Developer extension that simplifies the compilation with PL/Scope, visualizes PL/Scope information under a PL/Scope node in the Connections window and provides various source code analysis reports.

Follow these instructions for a fresh install or an update to the latest version of plscope-utils for SQL Developer.

Updated Tree Structure

The level for grouping primary and secondary object types was removed. This makes the the tree simpler. All object types can now be filtered. This is especially helpful for schemas with many objects. And there is an additional node for Sequences.

PL/Scope node in SQL Developer's Connection window (plscope-utils)

No more ORA-01436: CONNECT BY loop in user data

Many queries are based on recursive structures. In some data constellations, an ORA-1436 error caused no result to be returned. The reasons for this were manifold. Due to wrongly fixed hierarchies or due to dependency loops in the Oracle data dictionary. However, incomplete hierarchies are now fixed without causing a loop and all hierarchical queries use the cycle_clause which detects loops, avoids such runtime errors and ensures a reasonable result even with loops in the underlying data.

Concise Representation of Identifiers

Romain Vassallo contributed a pull request for the plscope_identifiers view. He added a column name_usage to combine name, type, usage and sql_id in a single column with a left indent of two characters per level.

The Identifiers tab includes now a column Name (Type, Usage) with this nifty logic.

PL/Scope identifiers tab in SQL Developer (plscope-utils)

That’s all?

Yes, regarding plscope-utils for SQL Developer. However, there are optional PL/SQL packages and views for static code analysis based on PL/Scope. Optional means, the SQL Developer extension does not need them. But if you have to analyze code in your Oracle database they can be a good starting point for your work.

Related Information

If you are interested in PL/Scope, the following links may be of interest:

The post plscope-utils for SQL Developer 1.0 – What’s New? appeared first on Philipp Salvisberg's Blog.