Code Analysis for Oracle checks PL/SQL code for “smells”, suggests best practices, identifies deprecated syntax, and can be used to enforce naming conventions. Rules can be customized according to your organization’s own chosen standards, conventions, and policies. Code analysis can be run as part of an automated process, like Continuous Integration (CI), to identify issues early before they are released to Production.

Download

Code Analysis for Oracle is only available as part of Redgate Deploy.  Download the latest version from our website or use Docker.  If you are using Redgate Change Automation to automate your database deployments, then code analysis is automatically run as part of the build command.

Supported rules

See all the rules in the product documentation.

Usage

Configure the Code Analysis for Oracle rules with the following options to meet your needs.  For your convenience, a sample cao.settings.xml is included in the command line installation that you can edit. This file can also be found here.

Severities

Each rule can be classified with one of the following severities: Ignore, Warning, and Error. Ignored issues will not be reported. Warnings will be reported, and will cause the command line to exit with the code "4" (see below). If at least one Error is found, the exit code will be "3", which can be used to fail a build when running as part of a CI process.

Specify a subset of rules to run

By default, all rules will be run and are set to the "Error" severity. To specify a subset, either use the /include, /exclude and /warning switches, or use /config with a configuration settings file and simply remove or comment out the rules that you don't want to run.  This can be customized on a project by project basis. 

Naming convention rules

For the objects in the cao.settings.xml, specify the allowed or disallowed naming conventions for the specified objects.  These names should be provided in the form of a regular expression supported by the Regexp class. So if you want to exclude a Function where the name starts with ‘z’, you should write ^z.*, for example. For further hints on the Regular Expression language, read this Quick Reference.

Exit Codes

• 0 - Code Analysis for Oracle successfully completed its analysis and reported no issues in the analyzed SQL files
• 1 - Code Analysis for Oracle failed to complete its analysis due to a fatal error
• 2 - Code Analysis for Oracle managed to finish but only partially completed its analysis - often this is because one or more SQL files could not be parsed (such files are currently excluded from the generated reports)
• 3 - Code Analysis for Oracle ran successfully but reported one or more errors in the analyzed SQL files
• 4 - Code Analysis for Oracle ran successfully but reported one or more warnings in the analyzed SQL files

Examples

On Windows, the Code Analysis for Oracle command line is added to the PATH environment variable on installation. For Linux or Mac users, use Docker.  In either case, the command line is executed by running cao.cmd without any parameters for help.

To analyze a specific script or a folder of .sql scripts, use the /source switch. The /outfile switch saves the code analysis results.

The following targets a folder of scripts and outputs an html results report.

cao.cmd /source:MyScriptsFolder /outfile:CodeAnalysisResults.html

The following targets a single script and outputs an xml results report.

cao.cmd /source:deployment_script.sql /outfile:CodeAnalysisResults.xml

To specify a subset of rules it is easiest to specify a settings file using /config:

cao.cmd /config:myRules.cao.settings.xml /source:MyScriptsFolder /outfile:CodeAnalysisResults.xml

Requirements

• Supported on Windows or Linux using .NETCoreApp v2.2 (let us know if you have any problems getting this running on your preferred OS)

Known issues and limitations

Please contact us if any limitations are causing inconvenience, or are outright blockers.  We are actively looking for feedback and happy to add additional checks if they can help more teams.

• It is only possible to analyze SQL scripts, not a live database.