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

Get rid of mutating table trigger errors with the compound trigger

When something mutates, it is changing. Something that is changing is hard to analyze and to quantify. A mutating table error (ORA-04091) occurs when a row-level trigger tries to examine or change a table that is already undergoing change (via an INSERT, UPDATE, or DELETE statement). In particular, this error occurs when a row-level trigger attempts to read or write the table from which the trigger was fired. Fortunately, the same restriction does not apply in statement-level triggers. In this post, I demonstrate the kind of scenario that will result in an ORA-04091 errors. I then show the "traditional" solution, using a collection defined in a package. Then I demonstrate how to use the compound trigger, added in Oracle Database 11g Release1,  to solve the problem much more simply. All the code shown in this example may be found in this LiveSQL script . How to Get a Mutating Table Error I need to implement this rule on my employees table: Your new salary cannot be mo

How to Pick the Limit for BULK COLLECT

This question rolled into my In Box today: In the case of using the LIMIT clause of BULK COLLECT, how do we decide what value to use for the limit? First I give the quick answer, then I provide support for that answer Quick Answer Start with 100. That's the default (and only) setting for cursor FOR loop optimizations. It offers a sweet spot of improved performance over row-by-row and not-too-much PGA memory consumption. Test to see if that's fast enough (likely will be for many cases). If not, try higher values until you reach the performance level you need - and you are not consuming too much PGA memory.  Don't hard-code the limit value: make it a parameter to your subprogram or a constant in a package specification. Don't put anything in the collection you don't need. [from Giulio Dottorini] Remember: each session that runs this code will use that amount of memory. Background When you use BULK COLLECT, you retrieve more than row with each fetch,

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