Toad World Blog

Why PL/SQL Code Quality Matters

Jun 1, 2018 5:03:00 AM by John Pocknell

I’ve worked in software development for many years starting back in the late 1980s with Oracle Database version 5.0 and Oracle Forms version 3.0. That was when my experience of working with PL/SQL first started and over 30 years later, I’m still involved with PL/SQL through Toad for Oracle although not actually writing it anymore!

Back then, I had little understanding of the need for coding standards apart from basic object templates and the need to document everything as you go along. So long as the code compiled and ran, that was considered good enough – right?

These days, PL/SQL language has evolved significantly with more sophisticated constructs and advanced data types enabling developers to build more advanced programs. With that complexity, however, comes the increased likelihood of failure.

 

Cost of defects

Research by tricentis.com found that in 2016, software failures cost the economy USD$1.1 trillion in assets. In total, software failures at 363 companies affected 4.4 billion customers and caused more than 315 years of lost time.

Some years ago, I worked with a Toad customer in China in the financial services sector who really took code quality seriously. They used Toad’s Code Analysis features as fully automated code QA tool and if excessive defects were found in their PL/SQL code, the developers responsible would be penalized by way of a cut to their bonus. That’s an extreme example, but the point is, the reason this company clearly took code defects seriously was because there can be a financial impact to business if defects ultimately lead to business interruption or loss of service.

 

“Best Practices” alone don’t work

Many companies I visit, when asked, claim they have a coding “best practices” manual which their developers are supposed to use. I’ve seen many of them and some are really good taking up 2 or 3 lever-arch files. However, when asked “how many of your developers actually use the recommendations, the embarrassing answer is usually “very few”.

What that basically means is that static code reviews, which are supposed to assess the quality of the code and it’s fitness-for-purpose in production, is piecemeal at best and are essentially nothing more than “guidelines” – which very few people follow – that’s just human nature.

If we actually set out what the goals should be in order to take a standardized approach to ensuring optimal code quality, we’d probably consider some or all of the following:

  • Reliability
    • Do we have the ideal set of best practice rules identified?
    • Is everyone following all the prescribed rules all the time?
  • Consistency
    • Is everyone interpreting all the rules the exact same way?
    • Will different people apply the same rules to different ends?
  • Measurability
    • How do we measure and attribute success of this methodology?
    • Can we quantify the cost versus savings of this methodology?
  • Effectiveness
    • Will we simply and easily get results of better engineered code?

How many of you can honestly say yes to more than 3 of these?

Classic “peer” reviews, for which I’m sure many of you reading this will have been involved at some stage in your career, involve having someone else read your code to see if there are any obvious issues.

 

 

There are some key flaws to this approach.

  • They are subjective and time consuming.
  • They are generally incomplete, focussing on basic structure and readability.
  • In the interests of time, they don’t cover all the code in the application.
  • No documentation, analysis or reporting. What does “good enough” look like?

So, peer reviews aren’t exactly taking a scientific approach to the problem and when I ask companies how seriously they take static code analysis, the answer is it’s not a key priority for them compared with (say) testing and performance.

However, with the emergence of integrating database change management into DevOps driving the need to automate many development processes, static code quality analysis is being demanded by organizations that need to accelerate their build and release processes.

What if there was a better way?

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 knowledgable 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 the Quest 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 a Code 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

 

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 it’s behaviour can be observed.

Figure 12 – Modifying a Code Analysis rule

So, why does code quality matter?

With all the disruption and costs associated with unplanned development cycles which come from rejected or poorly performing code, repeatable, objective code reviews are a must for you individually as well as your organization.

Implementing a rules-based approach to your PL/SQL code reviews is particularly important if you are considering integrating Oracle database development tasks into DevOps with ambitions of 2 – 3 week sprints. Peer reviews are simply not practical.

Our Toad DevOps Toolkit solution provides the ability to include PL/SQL code reviews as part of your DevOps build automation process. This is powered by Code Analysis.

Code Analysis is the only Oracle PL/SQL code review tool of its kind which is embedded into the development IDE for maximum effect and supports both individual developers by performing individual code reviews in the Editor as well as team leads and managers by assessing the overall quality of the code across the entire project.

To see a video showing Code Analysis in action, please click here.

Tags: Toad for Oracle

John Pocknell

Written by John Pocknell

John Pocknell is a senior product marketing manager at Quest Software. Based at the European headquarters in the U.K., John is responsible for the go-to-market strategy for the Toad portfolio of products worldwide. He has been with Quest Software since 2000, working in the database design, development and deployment product areas. John has spent over 17 years (including 12 years in Product Management) successfully evangelising Toad to customers at various conferences and user groups around the world and he writes blogs and produced many videos on the Toad user community, Toad World as well as technical papers about Toad on the Quest Software website.

John has worked in IT for more than 30 years, most of that time in Oracle application design and development. He is a qualified aeronautical engineer with more than 10 years of experience in provisioning IT consultancy services and implementing quality assurance systems to ISO 9001.