Simplify the Use of PL/Scope in SQL Developer

In this post, I showed how to do some code analysis with PL/Scope and how the views and packages of the plscope-utils might simplify this task. However, these views and packages are based on dba_* views and it is sometimes not that easy to get such privileges for an additional user in a non-personal database instance. To simplify the use of PL/Scope even more, I thought it would be helpful to come up with a solution, which does not require the installation of database components and will work with pre-version 12.2 connections as well. Hence I’ve built my first bundled XML extension for SQL Developer as part of the plscope-utils GitHub project.

plscope-utils for SQL Developer extends the database navigator tree by a “PL/Scope” node, context menus, detail views for PL/SQL units and some reports.

Compile

Before you analyse code with PL/Scope you have to compile the packages, procedures, functions, triggers, types and synonyms after enabling PL/Scope in the current session. Using dbms_utility.compile_schema sounds like the right choice, but unfortunately, it is not enough. The documentation reveals why: “This procedure compiles all procedures, functions, packages, views and triggers in the specified schema.”… Ah, types and synonyms are not compiled.

To compile the code with plscope-utils for SQL Developer, right-click on the “PL/SQL” or the connection name node and select the “Compile with PL/Scope…” option.

Amend the settings

and press the “Apply” button. Maybe it is also a good idea to peek at the SQL statement first, to better understand what exactly will be executed. Maybe you could integrate that part into your installation scripts as well.

Column Usages

Select a package within the “PL/Scope” node of the Connections window to show the column usage and other details. Please note, that the associated package procedure name is provided as well.

Click on the “Link” column to open the PL/SQL editor at the right line and column.

Reports

The “plscope-utils Reports” folder provides the following reports:

The “UDF Calls in SQL Statements” report shows static SELECT, INSERT, UPDATE, DELETE and MERGE statements within packages, procedures, functions, triggers and types using user-defined function calls. Such calls might be costly, especially when they are accessing data as well. See also Steven Feuerstein’s post More 12.2 PL/Scope Magic: Find SQL statements that call user-defined functions. In fact I snitched the idea from Steven.

Download / Installation

You may download plscope-utils for SQL Developer from here, here or here.

Use the “Install From Local File” option within the “Check for Updates… ” dialog to install the previously downloaded zip file or configure the http://update.salvis.com/ update site as described at the bottom of the plscope-utils for SQL Developer page.