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

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:

• Any Static Code Analysis Tools for Stored Procedures?
• https://stackoverflow.com/questions/839707/any-code-quality-tool-for-pl-sql
• Is there a static analysis tool for Python, Ruby, Sql, Cobol, Perl, and PL/SQL?

Answer 1

I think that this blog describes the needed process:

http://www.theserverlabs.com/blog/?p=435

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

Answer 2

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!