Skip to main content

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!

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
  8. Hi Steven,

    Many thanks for sharing, I am trying to explore SonarQube to analyze PL/SQL code but it is available only for eclipse.
    Please help me out if you know any way to integrate with PL/SQL Developer or SQL Developer.

    Thanks,
    Mukesh

    ReplyDelete
  9. Hi Steve, sadly this must happen all the time, but someone called Priyaraj Thirukonda has ripped off your answer (and two replies), and posted it as their own work, at https://www.quora.com/Are-there-any-tools-available-to-check-coding-standards-in-PL-SQL

    ReplyDelete

Post a Comment

Popular posts from this blog

Quick Guide to User-Defined Types in Oracle PL/SQL

A Twitter follower recently asked for more information on user-defined types in the PL/SQL language, and I figured the best way to answer is to offer up this blog post. PL/SQL is a strongly-typed language . Before you can work with a variable or constant, it must be declared with a type (yes, PL/SQL also supports lots of implicit conversions from one type to another, but still, everything must be declared with a type). PL/SQL offers a wide array of pre-defined data types , both in the language natively (such as VARCHAR2, PLS_INTEGER, BOOLEAN, etc.) and in a variety of supplied packages (e.g., the NUMBER_TABLE collection type in the DBMS_SQL package). Data types in PL/SQL can be scalars, such as strings and numbers, or composite (consisting of one or more scalars), such as record types, collection types and object types. You can't really declare your own "user-defined" scalars, though you can define subtypes  from those scalars, which can be very helpful from the p

The differences between deterministic and result cache features

 EVERY once in a while, a developer gets in touch with a question like this: I am confused about the exact difference between deterministic and result_cache. Do they have different application use cases? I have used deterministic feature in many functions which retrieve data from some lookup tables. Is it essential to replace these 'deterministic' key words with 'result_cache'?  So I thought I'd write a post about the differences between these two features. But first, let's make sure we all understand what it means for a function to be  deterministic. From Wikipedia : In computer science, a deterministic algorithm is an algorithm which, given a particular input, will always produce the same output, with the underlying machine always passing through the same sequence of states.  Another way of putting this is that a deterministic subprogram (procedure or function) has no side-effects. If you pass a certain set of arguments for the parameters, you will always get

My two favorite APEX 5 features: Regional Display Selector and Cards

We (the over-sized development team for the PL/SQL Challenge - myself and my son, Eli) have been busy creating a new website on top of the PLCH platform (tables and packages): The Oracle Dev Gym! In a few short months (and just a part time involvement by yours truly), we have leveraged Oracle Application Express 5 to create what I think is an elegant, easy-to-use site that our users will absolutely love.  We plan to initially make the Dev Gym available only for current users of PL/SQL Challenge, so we can get feedback from our loyal user base. We will make the necessary adjustments and then offer it for general availability later this year. Anyway, more on that as the date approaches (the date being June 27, the APEX Open Mic Night at Kscope16 , where I will present it to a packed room of APEX experts). What I want to talk about today are two features of APEX that are making me so happy these days: Regional Display Selector and Cards. Regional Display Sel