PL/SQL pre-compile and Code Quality checks in an automated build environment?

Question

We build software using Hudson and Maven. We have C#, java and last, but not least PL/SQL sources (sprocs, packages, DDL, crud)

For C# and Java we do unit tests and code analysis, but we don't really know the health of our PL/SQL sources before we actually publish them to the target database.

Requirements

There are a couple of things we wan't to test in the following priority:

1. Are the sources valid, hence "compilable"?
2. For packages, with respect to a certain database, would they compile?
3. Code Quality: Do we have code flaws like duplicates, too complex methods or other violations to a defined set of rules?

Also,

• the tool must run head-less (commandline, ant, ...)
• we want to do analysis on a partial code base (changed sources only)

Tools

We did a little research and found the following tools that could potencially help:

• Cast Application Intelligence Platform (AIP): Seems to be a server that grasps information about "anything". Couldn't find a console version that would export in readable format.
• Toad for Oracle: The Professional version is said to include something called Xpert validates a set of rules against a code base.
• Sonar + PL/SQL-Plugin: Uses Toad for Oracle to display code-health the sonar-way. This is for browsing the current state of the code base.
• Semantic Designs DMSToolkit: Quite general analysis of source code base. Commandline available?
• Semantic Designs Clones Detector: Detects clones. But also via command line?
• Fortify Source Code Analyzer: Seems to be focussed on security issues. But maybe it is extensible? more...

So far, Toad for Oracle together with Sonar seems to be an elegant solution. But may be we are missing something here?

Any ideas? Other products? Experiences?

Related Questions on SO:

• http://stackoverflow.com/questions/531430/any-static-code-analysis-tools-for-stored-procedures
• http://stackoverflow.com/questions/839707/any-code-quality-tool-for-pl-sql
• http://stackoverflow.com/questions/956104/is-there-a-static-analysis-tool-for-python-ruby-sql-cobol-perl-and-pl-sql

Answer 1

Our approach is to keep each database object (tables, views, functions, packages, sprocs etc) in its own file under source control and have an integration server (TeamCity, Hudson etc) do a nightly build of the database - from source - where it drops and recreates the schema before checking for compilation errors in the user_errors system table. This lets you know when someone has introduced compilation errors into the build.

The next step is to use something like PLUTO to add unit tests to your PL/SQL code and add those into the nightly build task. For us, this has involved having sample test datasets (also under source control) that allow us to get the database to a "known state" for the purposes of testing.

I've not found anything that helps us much with any of the above so it's mainly a collection of Ant tasks, custom shell scripts and wizardry, which basically apply the required DDL to an empty database and use DBMS_UTILITY.COMPILE_SCHEMA() to, uh, compile the schema. You can add more fancy stuff later, like back-tracing objects which fail to compile or fail tests to a specific submit in source control, and issue "blame mail".

I'd be really interested to see if anyone else has a better approach or if there's an off-the-shelf product that does this for me!

Answer 2

Hi Lars,

I think that this blog describes the needed process:

http://www.theserverlabs.com/blog/2009/05/18/continuous-integration-with-oracle-plsql-utplsql-and-hudson/

Please check and let me know what you think about it.

Kind Regards, Hanno

Answer 3

Our DMS Software Reengineering Toolkit is the foundation for arbitrary customizable tools. It has a PL/SQL front end that can be used to build arbitrary source code quality checks. Yes, it has a command-line version.

There are a variety of PL/SQL COTS tools based on DMS that could be used to check quality:

• Formatter - Cleans up layout. Side effect: static check for legal PL/SQL syntax
• Source Code Search Engine - enables fast search of indexed source code base. Computes Halstead and Cyclomatic metrics as a side effect of setting up the index.
• CloneDR - finds and reports duplicated PL/SQL code
• Test Coverage - determines part of PL/SQL code not executed by tests (ad hoc, unit, or functional tests)

All these have command line versions.