PL/SQL Plugin

Demonstration	The capability of the plugin is demonstrated on Nemo
Tracker	You can browse tickets for the plugin in our Jira
Price	2,000 € per instance of Sonar and per year.
Option	A PL/SQL extractor for Oracle Forms can be purchased as an option of the plugin at the cost of 400 € per instance of Sonar and per year.
Including	Upgrade, maintenance and support
Prerequisite	Toad for Oracle Professional Edition (Quest Software, version 9.5 or higher), not included

Download

Description

The professional Edition of Toad for Oracle includes an embedded feature called Code Xpert. It allows to perform objective and automated PL/SQL code quality and standards reviews against pre-defined rule sets and coding best practices. It also generates standard code metrics like number of lines or density of comments.

Adding the PL/SQL plugin to Sonar gives the capability to transparently launch the Toad Code Xpert functionality through the command line interface and to collect the generated metrics and rules violations.

You then get full Sonar capability in terms of reporting, including :

• Core features like trends, time machine and alerts.
• Drill down from project to code
• Central place for quality profiles management, including PL/SQL coding rules
• Consolidation of PL/SQL projects with other projects
• Deactivation of false positive violations using –NOSONAR

Projects	Dashboard	Violations
Time machine	Quality profiles	Alerts
Coding rules	Highlighted code syntax

Prerequisites

• Quest Software Toad 9.6 or higher with the CodeXpert module (included into Toad for Oracle Xpert, Toad for Oracle Professional and Toad Development Suite for Oracle)
• Java 5 or greater
• Apache Maven 2.0.9 or greater
• A connection to your Oracle database. This is a limitation of Toad that needs to be connected to the database even if it is not used by the CodeXpert module. So you can set a “fake” connection to an empty database.
• Option Forms extractor only : Oracle JDAPI library included into Oracle Developer Suite 10g or equivalent.

CodeXpert has two modes to analyse PL/SQL sources :

• It scans source from files on disk. This mode is generally used in enterprise environments with configuration management strategies (use tools like Subversion or Clearcase).
• It connects to the database and analyzes all the objects stored in that database. This mode is NOT supported by Sonar.

Configure Sonar

Server

Read the installation guide to deploy and configure Sonar. Extra steps specific to the PL/SQL plugin are :

• Copy the file sonar-plugin-plsql.jar to /extensions/plugins
• Before PL/SQL plugin 1.6 : copy staxmate-2.0.0.jar to /extensions/plugins/
• Restart the server
• Check that PL/SQL coding rules are activated. Just browse to the “Coding Rules” page.

Maven2

The Sonar Maven2 plugin automatically configures and launches Toad CodeXpert to analyse the PL/SQL code. For each PL/SQL project, create a file named pom.xml at the root directory and edit it according to this template:

Replace the following parameters :

• <groupId> is the id of your organization, for example com.mycompany or org.apache (use dots)
• <artifactId> is the id of the project, for example my-project (do no use dots)
• <version> is the project version, for example 1.0. Set to 1.0-SNAPSHOT if you don’t know.
• <name> is the name of the project that is displayed into the Sonar dashboard
• <description> is the description of the project, also displayed into the Sonar dashboard
• <sourceDirectory> is the relative path to the root directory of PL/SQL sources, for example ${basedir}/src/plsql.
• <sonar.toad.login> is the connection to Oracle, aka the login@the database
• <sonar.toad.password> is the password for <sonar.toad.login>
• <sonar.toad.exe> is the path to the Toad executable. Default value is C:\Program Files\Quest Software\Toad for Oracle\toad.exe if the parameter is not set.

It’s possible to set the three last parameters into the global Maven configuration. It avoids to repeat them into each PL/SQL project. Just edit the file settings.xml located into the Maven application :

The following advanced parameters can be used also :

• sonarsource.plsql.skipToad : enables to run an analysis without running Toad, therefore not having coding rules violations
• sonarsource.plsql.toadTimeoutSeconds : set a maximum time for Toad to run and kills it after. Default is 10 minutes.

Analyze projects

Like Java projects, just execute the following command from the root directory of each PL/SQL project :
mvn sonar:sonar

PL/SQL extractor for Oracle Forms

The PL/SQL extractor uses Oracle JDAPI library, included in Oracle Developer Suite. To avoid DLL conflicts with Toad CodeXpert, it is recommended to install Oracle Developer Suite before Toad. To check that the environment is correct :

• The PATH variable contains Oracle Developer Suite paths, for example :
  
  PATH=C:\OracleDevSuite\jdk\jre\bin\classic;C:\OracleDevSuite\jdk\jre\bin;C:\OracleDevSuite\jdk\jre\bin\client;C:\OracleDevSuite\jlib;C:\OracleDevSuite\BIN;C:\oraclexe\app\oracle\product\10.2.0\server\bin
  
  
• The ORACLE_HOME variable must contains the Oracle root path, for example :
  
  ORACLE_HOME=C:\oraclexe\app\oracle\product\10.2.0\server
  
  
• Start Toad and connect to any database
• Execute Database -> Diagnose -> CodeXpert -> Load my objects -> select a source -> Perform a code quality review.
• Export the HTML result to file (see link at the bottom of the window). It crashes if there are conflicts.

The Oracle JDAPI JAR library can’t be distributed with the Sonar plugin for licensing reasons. That’s why the file forms/java/frmjdapi.jar from Oracle Developer Suite must also be copied into %SONAR_HOME%/extensions/plugins.

Troubleshooting

Toad Code Xpert 9.x is unable to start from the command-line when the month of the current date contains an accent (”Décembre” in french for example). The work around for this is to change the regional settings on the machine.