Code analysis: Why PL/SQL code quality matters

If you’ve been “Livin’ On A Prayer” that your code won’t slow your database, give a “Shout” and a “Careless Whisper” that it won’t be “Another One Bites The Dust.” We know you’re “Under Pressure” and it’s just “The Reflex” not to do a code review. But … Toad is always singing, “Don’t You Want Me.”

Code_Analysis is more complicated now than in the 80s.

An 80s developer myself, I have lived a thousand lessons as to why code review matters. I hope you got a chance to read part 1 of this blog, “What is a code review and why PL/SQL code quality is important.” In that blog I discussed important features every code review tool should have and why. In this blog, I’ll show you how Quest® Toad® for Oracle Pro is a tool that checkmarks the box on every requirement you would look for in a code analysis tool.

Try Toad free for 30 days

Want to try before your buy? The code review feature, Code Analysis, is available in Toad for Oracle Pro edition and above. Try it for free, in 30-day trial of Toad for Oracle. Free 30-day trial of Toad for Oracle

Questions? Volume discounts? Want a demo? Talk with an expert.

Welcome to Toad’s secret weapon – Code Analysis!

A part of Toad for Oracle since version 8.0 in 2004 (originally called CodeXpert), Code Analysis is the original and best automated, rules-based PL/SQL code review and analysis tool in the market. Code Analysis is available from the Professional Edition of Toad and higher, and is accessible through the Editor as well as other locations as you will see.

If you are using a recent version of Toad, you may already be using Code Analysis and not even aware of it! See those blue squiggly lines under some of your code? That’s Code Analysis detecting coding violations! (See Figure 2 below)

Code Analysis analyzes your PL/SQL against a set of rules (over 200) defined by leading experts in order that you can ensure coding best practices are followed. These rules are stored in a rule set of which there are several that come with Toad, or they can be user-defined.

You can access Code Analysis by clicking the Code Analysis button found on the Editor Toolbar.

At any point, a Toad developer can ask for feedback on a piece of PL/SQL or SQL code. Code Analysis will analyze the code and offer detailed corrective recommendations on how to optimize it—not only for code correctness, but also for better readability and maintainability. This extends Toad’s benefits to the entire development and quality assurance teams. So when the developer moves on to something else, the person who inherits that same program is not left to wonder what is going on.

For example, if the database is not complaining with ORA or PLS errors when you create your stored procedures, it is very tempting to just assume that everything must be OK. With Toad, you can address that nagging fear in the back of your head that maybe all is not well with your PL/SQL program.

Code formatting, a very low level of review, is also available using the Formatter which can be invoked through an Editor right-click. This will “clean-up” the appearance of the code and ensure it adheres to company code layout standards.

The Code Analysis window comprises a dashboard into which a code review’s results for one or more programs are displayed and analyzed. The dashboard consists of the Code Analysis toolbars, the Results tab, the Reports tab as well as Rules and Statements Totals.

Figure 1 – Code Analysis dashboard

By the way, we don’t just simply tell you that you have a coding problem, Rule Hints explain why a particular coding violation has taken place in order to provide advice and enable you to become more knowledgeable and write better code next time around.

The Code Analysis dashboard provides a huge amount of flexibility regardless of whether you are a developer or a team-lead or manager.

As a developer, you can quickly review your code against pre-defined objectives and see how your code stacks up. You can then send the code, together with any violations to the Editor in order to make any necessary changes.

Alternatively, you can simply open some code into the Editor and analyze it by pushing the “Analyze code” button on the toolbar (see below).

Figure 2 – Coding violations and explanations presented in the Editor

As a team lead or manager, you can use the dashboard to provide you with an instant summary of just how well the code for your project is adhering to the coding standards and best practices laid down and what needs to be done to correct any violations.

There are several elements to the Code Analysis dashboard which I’ll explain here:

Code Analysis Toolbar

Code Analysis has an optional repository where code analysis metrics can be stored and used for future trending reports using Toad Reports Manager. The first time you click this button, Toad will ask you if you wish to install the repository and in which schema where you’d like to install the objects (The TOAD schema is generally recommended).

Figure 3 – Setting up the Code Analysis repository

Figure 4 – Reports Manager and Code Analysis report

You can also publish the Code Analysis results to a Toad Intelligence Central server to share them with team members. For more information on this capability, please read the Toad Help.

Figure 5 – Code Analysis results (over time) viewed from the Toad Intelligence Central web console

Code Analysis provides integration with theQuest SQL Optimizer for Oracle (part of the Toad Xpert Edition and higher) which allows developers to scan their PL/SQL code to identify potentially problematic SQL statements and tune (or optimize) them before they become a performance bottleneck in production.

Dashboard

The Code Analysis dashboard can be populated with PL/SQL code you want to review and can be loaded from the file system or from the database.

There is aCode Metrics section which summarizes where the issues are. Each of the columns is sortable.

The SQL Scan Summary shows where there could be potential performance issues where PL/SQL code contains SQL statements and classifies them according to various criteria (see Toad Options for more information).

Figure 6 – Code Analysis code dashboard for multiple code reviews.

Result tab

This section shows a summary of the code review in a collapsible tree format. Expanding the tree and clicking on an item will highlight the exact line of code in the lower right code panel.

The SQL Scan section of the report displays any potential issues by classifying SQL statements found according to whether they are Invalid, Problematic (worst), Complex or Simple:

Figure 7 – Code Analysis Results tab.

SQL identified can then be submitted for optimization either in the Toad Editor using Optimize Current SQL or into the SQL Optimizer itself for more advanced analysis and optimization.

Figure 8 – Optimize Current SQL optimizes inefficient SQL while still in Toad and provides developers with sementically equivalent re-writes from which they can choose the most optimum

Rule violations

Rules violations are sorted according to how the sorting option is configured on the Rule Set dropdown field.

Clicking on a code violation will highlight the offending line in the code panel.

Properties

The Properties tree contains a profile analysis of the PL/SQL. Categories include:

Code metrics

This is a summary of the SEI (Software Engineering Institute) metrics and also includes the Toad Code Rating (TCR).

The TCR is a composite of the Code Analysis rating criteria (TCR always rounds up), and then the numbers are assigned a color as follows:

1	Green	3	Orange
2	Yellow	4	Red

Reports tab

This tab contains various reports which can be saved to HTML format. HTML report styles can be modified in the Toad Options.

You can also select whether to generate a report for one code unit or multiple (depending which option is selected in the Toad Options).

Figure 9 – Code Analysis reports

Configuring code analysis rule sets

In order to provide development shops with the ability to define which rules should be applied to a particular project, Code Analysis enables the user to select and group rules into Rule Sets. The Code Analysis Rule Set determines what rules will or will not be included in a particular Analysis. Rule Sets are configured through the Code Analysis Rule Set Window:

Figure 10 – Selecting rules into a new custom Rule Set

You can access the Rule Set window by clicking the Edit Rule Sets button found on the Code Analysis General Toolbar.

Once you have selected the rule you need and the Rule Set has been saved, it will then be available for selection and use in the Rule Set drop-down found on the Code Analysis General Toolbar.

Figure 11 – Selecting a pre-defined Rule Set for use in code review

Buy Toad for Oracle Xpert Plus Subscription Edition for same price as Oracle Pro DB Admin Subscription Edition, save $400 USD

Customizing code analysis rules

Code Analysis provides the ability to customize rules to modify their behavior when used. You can customize rules by clicking the Edit Rules button found on the Code Analysis General Toolbar.

Code Analysis rules are created and maintained using XPath.

Upon selecting a rule you wish to modify, the lower left quadrant becomes active enabling the any of the elements of the rule to be modified including the XPath expression.

Modified rules can be tested, using the Test Code quadrant (upper right) so its behaviour can be observed.

Figure 12 – Modifying a Code Analysis rule

I hope you enjoyed my blog, read more about this topic in the links below.

Have questions or comments?

Head over to the Toad for Oracle forum on Toad World®! Chat with Toad developers and lots of experienced users.

Help your colleagues

If you think your colleagues would benefit from this blog, share it now on social media with the buttons located at the top of this blog post. Thanks!