Database versioning tools such as Source Control for Oracle and Redgate Change Control help development teams author and version control their PL/SQL scripts. These include programmable objects (packages, procedures, functions, triggers etc), migration scripts (creating and modifying table structure and transactional data), and static/configuration data changes. The Code Analysis for Oracle command line allows organizations to apply a set of standards to these scripts as part of a continuous integration process. However, it is often preferable to shift-left even more and identify these issues before they disrupt the development team by causing the build to break.

To do this, you can configure a pre-commit hook. This is a process that executes as part of a VCS commit operation and can be used to abort a commit if criteria are not met, such as code analysis rules failing.

To configure a pre-commit hook in Git, create a text file called pre-commit and place it in in your .git/hooks/ folder.

#!/bin/sh
#
echo "Running code analysis in pre commit hook"
"C:\CAO>cao.cmd.exe" /config:cao.settings.xml /source:<my_database_files> /outfile:CodeAnalysisIssues.html /log:CodeAnalysisLog.log
x=$?
 
if [ $x == 1 ]
then   
    echo "Opening CodeAnalysisIssues.html in your browser to review issues"
    start CodeAnalysisIssues.html
fi
echo exit code = $x
exit $x

Once the Git hook file is in place, any attempt to commit will apply the chosen rules to your folder of database scripts and will output a report (CodeAnalysisIssues.html in the example above).In this example, the Code Analysis for Oracle command line references a code analysis rules configuration file (cao.settings.xml). This is an xml file where the desired rules are listed with user-configurable severities (ignore, warning and error). This file should be saved and version-controlled as part of your project. This means that it can be not only referenced by each developer's pre-commit hook, but also as the input configuration for your CI check.

Known issues

• The code analysis command line outputs a log file each time it is run, which will need to be cleaned up manually for now (eg, place logic into the Git hook script to remove the files). To mitigate this use /log:<logfilename.log> and gitignore this file.
• Detected code analysis issues aren't displayed in the console output, so the report (html or xml) needs to be consulted.
• If you have more than one folder with .sql scripts, you will need to invoke the sqlcodeguard.exe command twice, as /source can only take one value.