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 (include and exclude lists).
Validator Plugins
Would you like to create your own guidelines? Or extend the existing guidelines? Then validators are your friends. A validator is a Java class implementing the PLSQLCopValidator interface. Validators may be used in the command-line utility, in SonarQube and the SQL Developer extension.
A complete example is provided with source code as a Maven project. This example extends the default validator by 15 additional guidelines to check naming conventions according to 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 a 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 an outline view, syntax colouring, bracket matching, code formatting and error integration into the Eclipse workbench.
8 Comments
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
Then it would be good if this information is added to the message. Because like that it is really misleading.
:)
I agree that the title of this guideline does not imply to cover IN parameters as well. It’s a good idea to fix that.
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:
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
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
Hi Volker,
Regarding the G-7110 issue, there was already a question in the forum. See https://www.salvis.com/blog/forum/plsql-cop/initializing-nested-table-g-7110/
Regarding the G-1050 issue. 2 and ‘close cur_testvg_scorewerte’ are both literals. According to to the guideline you should consider using constants instead of literals. However, I really understand that it is too much to 100% avoid literals. So either user the NOSONAR marker to disable the check within the code ore disable this guideline check completely.
HTH
Philipp