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!