Wednesday, April 1, 2015

Lint Checkers for PL/SQL

Received this question today: "Is there a commercial tool available for scanning PL/SQL code and providing feedback on adherence to coding standards, like say in much the same way ‘lint’ works on C code? Do you sell such a product or know of anyone who does?"

First about that reference to "lint": it's defined outside of software as "minute shreds or ravelings of yarn; bits of thread." And as wikihow points out, "Having lint stick to your clothing can ruin an otherwise perfectly dashing outfit."

In the world of software, Wikipedia tells us that "lint was the name originally given to a particular program that flagged some suspicious and non-portable constructs (likely to be bugs) in C language source code. The term is now applied generically to tools that flag suspicious usage in software written in any computer language. The term lint-like behavior is sometimes applied to the process of flagging suspicious language usage. Lint-like tools generally perform static analysis of source code."

So, generally, you use your compiler to tell you whether or not you can run your code (if it doesn't compile, you can't run it). You use a lint checker to tell you if there are ways you could improve the quality or performance of your code.

In the world of PL/SQL, lint checkers are built into the most popular IDEs and are also available in a couple of forms built into PL/SQL itself.

Built-in Features

1. Compile-time warnings: turn on warnings before you compile your code, and the PL/SQL compiler will not only report any errors, but also provide you extensive feedback on ways we think you could improve that code. I offer some examples below, but I encourage you to check the documentation on this excellent feature.

Enable warnings for an entire session, or for a single program unit:

ALTER SESSION SET plsql_warnings = 'ENABLE:ALL'
/

ALTER PROCEDURE my_procedure COMPILE plsql_warnings = 'ENABLE:ALL'
/

You can also enable specific warnings, categories of warnings, and even specify that a particular warning should be treated as a compilation error:

ALTER SESSION
  SET PLSQL_WARNINGS='ENABLE:SEVERE', 
                     'DISABLE:PERFORMANCE', 
                     'ERROR:06002'
/

Here's an example of compiler warnings in SQL Developer:


To get a sense of the breadth and depth of lint checking that the PL/SQL compiler will do for you, check out the Error Messages manual, PLW section.

2. PL/Scope: it's not really a lint checker, but a tool that you can use to build your own lint checker. This post is not the place top explore this powerful feature (added in 11.1). You might start with the documentation or my article in Oracle Magazine.

In IDEs

SQL Developer integrates compile-time warnings into its IDE. Turn on warnings and you will see them appear in the same Compiler window as any possible errors. In other words, SQL Developer lint checking is based directly on PL/SQL compile-time warnings.

And you don't have to issue that ALTER SESSION statement in SQL developer to leverage compile-time warnings. Turn it on by default via Preferences:



Toad for Oracle from Dell offers its own Code Analysis feature built on its propriety parsing technology and XPath-based rules. You can, of course, also turn on PL/SQL's compile-time warnings and see them in the compile error window. Here's a screenshot of the Code Analysis feature:


PL/SQL Developer from Allround Automations also offers its own warning system (with its own rules), automatically enabled when you compile a program. You can, of course, also turn on PL/SQL's compile-time warnings and see them in the compile error window. Sorry, don't have PL/SQL Developer installed, so cannot offer a screenshot, but you can more information about the product here.

Do you know of and use other lint checker products or features? Tell us all about it!

7 comments:

  1. I particularly like TVDCC, Trivadis Code Cop for SQL Developer: http://www.salvis.com/blog/downloads/tvdcc-trivadis-plsql-sql-codechecker-for-sql-developer/

    It checks PL/SQL code against the Trivadis coding guidelines, which are very sensible, so they should suit most developers. Even though you cannot customize the rules themselves, you can disable certain rules.

    Moreover, it shows metrics such as cyclomatic complexity, and it does not require code to be compiled, so you can use it to check your code before you send it to the DB for compilation.

    ReplyDelete
  2. We use SonarQube (http://www.sonarqube.org/) as code quality checker with the commercial plugin language for PL/SQL (http://www.sonarsource.com/products/plugins/languages/plsql/).

    There are some embedded rule and some are configurable. You can also add your own rule.

    Code need to be analysed from source.

    Check the demo here (http://nemo.sonarqube.org/dashboard/index/nl.oracledeveloper:utplsql) on the popular Unit Testing Framework ;-)

    ReplyDelete
  3. See PMD for PL/SQL
    https://pmd.github.io/pmd-5.4.1/pmd-plsql/rules/index.html

    Kind Regards

    ReplyDelete
  4. Hi Steve,
    many thanks for this discussion, it's very important.

    I have been working with PL/SQL Developer from Allround Automation since 10 years and I am very happy with it!

    The TVDCC from Trivadis is a very good concept, I like the PL/SQL Code Guidlines from Trivadis and I would like to try it in the next time.

    SonarQube has good PL/SQL rule definitions..

    Another good PL/SQL scanner is: http://www.conquestsoftwaresolutions.com/page/clearsql_pr_description

    Best Regards

    ReplyDelete
  5. Hi Steve,

    My Organization is looking for a tool which can review the PL/SQL code based on our custom rules. Our requirement is that the tool can look into the delta piece of code which is added or modified and based on our rules it should give the report so that the developer can look for any issue and fix the same.

    Presently we are using CLear SQL tool but that does not suffice our requirement as it does not look into the delta piece o the code.

    Do you have any tools which can match our requirement or suggest any?.

    Reg,
    Ambuj

    ReplyDelete
  6. Ambuj, great idea, but no I do not know of a tool that can do that...though I am pretty sure that with Toad Code Analysis you can write your own rules, maybe you can do more now, I am not sure. Maybe others know more than me.

    ReplyDelete
  7. Nice post... I am using open source code review tools and this tool is beneficial to all developers, managers and architects. Thanks for sharing

    ReplyDelete