PL/SQL Cop for Trivadis Coding Guidelines Version 3.2

The PL/SQL Cop tool suite supports the new Trivadis PL/SQL & SQL Coding Guidelines 3.2. Download the new versions from the Download section. So, what’s new?

Numbering and Categorisation Scheme

The guidelines have been renumbered, extended, categorised by severity (blocker, critical, major, minor and info) and assigned to one or more SQALE characteristics (changeability, efficiency, maintainability, portability, reliability, reusability, security and testability).

This new categorisation allows to sort issues by severity. The most important issues will be listed first, even if you do not disable less important guidelines.

Severity and characteristics may now be used beside guideline numbers in check and skip lists (white- and blacklists).

Validator Plugins

Would you like to create your own guidelines? Or extend the existing guidelines? Then validators are your friends. A validator is basically a Java class implementing the PLSQLCopValidator interface. Validators may be used in the command-line utility, in SonarQube and in the SQL Developer extension.

A complete example is provided with source code as Maven project. This example extends the default validator by 15 additional guidelines to check naming conventions according chapter 2.2 of the Trivadis PL/SQL & SQL Coding Guidelines 3.2. The following screenshot shows the violation of a custom guideline (G-9013: Exceptions should start with ‘e’) within SonarQube.

PL/SQL Editor for Eclipse

The editor is mainly provided to understand the PL/SQL model better. The full model is available as PLSQL.ecore file, which may be explored best within the Eclipse IDE. Understanding the PL/SQL model is important if you plan to develop your own validators.

Use the plsqleditor.zip file in the eclipse folder to install the PL/SQL editor in Eclipse. The editor supports beside an outline view, syntax colouring, bracket matching, code formatting and error integration into the Eclipse workbench.

Changelogs

8 Comments

  1. Sebastian says:

    Thanks for the update!

    But I think I found a bug:

    I get G 7240 ( Avoid using IN OUT parameter as IN or OUT only ) on every procedure/function.

    These dont have IN/OUT specified which should default in “IN”. So the 7240 shouldnt apply.

    • Hi Sebastian,

      Thank you for your feedback. Here’s an excerpt from page 109 of the Trivadis PL/SQL & SQL Coding Guidelines 3.2 regarding G-7240: “(…) Explicitly showing the mode indication of all parameters is a more assertive action than simply taking the default mode. Anyone reviewing the code later will be more confident that you intended the parameter mode to be IN(…)”. Based on that, the guideline is also about defining the mode explicitly. Using the default mode leads therefore to a guideline violation. This is not a bug.

      Cheers,
      Philipp

  2. Friedhold Matz says:

    Hi Philipp,

    thank you for updating the Trivadis PL/SQL & SQL Coding Guidelines 3.2. I agree with this guide and have some small suggestions:

    In the guide on page 8 there are 2 scope types { g_.. , l_.. } I would extend this with m_.. the ‘Modul’ scope of the package body, defined in the body head.
    I would define variables in anonymous blocks with a number of the local level, e.g  ln_ (l2_.. first nested block level) ; it’s not bad for me to define these blocks with own defined variable scopes for secure encapsolation in large PL/SQL procedures or  special error handlings.
    A good practice for me is also the p_.. name declaration for the formal parameters:

    p_..           :  formal parameter, means IN (default)
    p_OUT_ : formal output parameter
    p_IO_      : formal input / output parameter

    For formal cursor parameters I prefer to apply cp_..   .

    Best greetings,

    Friedhold

    • Hello Friedhold,

      Your welcome. First of all the table on page 8 shows a “possible set of naming conventions.”. Think of it as a starting position for your own naming conventions if you do not have them already. However, since we recommend to avoid declaring global variables public (see guideline 7230) it would be inconsistent to distinguish between public and ‘modul’ scope of global variables.

      The current version of PL/SQL Cop comes with two example validators for naming conventions. One implementing the 15 guidelines on page 8 and another one with just 3 simple guidelines:

      • ‘g_’ for global variables
      • ‘l_’ for local variables
      • ‘p_’ for all kind of parameters

      I also see “in”, “out” and “io” used as parameter suffix. So, please continue to use your set of naming conventions as long as it fits your needs.

      Thanks,
      Philipp

  3. Volker Bartuseck says:

    Hi Philipp

    I’m new to PL/SQL Cop and tried it on a short package for testing purpose.

    There are two repeating incidents in the list and one seems to be a mistake:

    On “SET tsw_tag1 = l_testvb_scorewerte(i).tsw_tag1” inside an update statement I get the error “G-7110: Try to use named notation when calling program units.” which is definitely wrong, because it is an index access to a collection.

    The second error reported “G-1050: Avoid using literals in your code.” happens on two different occasions:

    WHEN 2 THEN
    tsp_exception.melde_debug(p_text => ‘close cur_testvb_scorewerte’);

    The error is ok in principle, but within the context it should be ignored. Is there an easy way not to ignore the whole error, but only within a certain context?

     

    Regards

    Volker

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.