PL/SQL Cop – Changelog


General Availability (GA) Releases of Trivadis PL/SQL Cop (tvdcc)

Version 2.1.6


  • Changed
    • Support files with .pck extension and include pck in the default filter
    • Included preview/trial license is valid thru 2019-03-31

Version 2.1.5


  • Fixed
    • Use default license location when an empty license parameter is passed. This enables the use of the SonarQube plugin without specifying a license file.

Version 2.1.4


  • Changed
    • Included preview/trial license is valid thru 2018-09-30

Version 2.1.3


  • Fixed:
    • Using quote delimiter character ~ (tilde) leads to parse errors
    • Parse error when using COUNT in pivot_clause
  • Changed
    • Updated PLSQL Editor for Eclipse to version 2.1.8

Version 2.1.2


  • Fixed:
    • Using concatenation operator || with whitespace between the vertical bars leads to parse errors
  • Changed
    • Updated PLSQL Editor for Eclipse to version 2.1.7
    • Documented limitation regarding error_logging_clause, see FAQ for examples

Version 2.1.1


  • New:
    • Oracle grammar support
      • SQL*Plus
        • History command
      • SQL
        • Analytic views
        • Join groups
        • Oracle sharding
        • PDB lockdown profiles
        • ADMINISTER KEY MANAGEMENT Enhancements
        • FLASHBACK DATABASE Enhancement
        • SELECT Enhancement
        • New COLLATE Operator
        • New or Enhanced Expressions
        • Enhanced Condition
        • New or Enhanced Functions
      • PL/SQL
        • ACCESSIBLE BY clause Enhancements
        • Data-Bound Collation
        • PL/SQL Expressions Enhancements
        • Support for SQL JSON operators in PL/SQL (based on SQL changes)
        • PL/SQL Coverage Pragma
        • PL/SQL Deprecation Pragma
        • Sharing Metadata-Linked Application Common Objects
    • All guideline examples have been extended by a reason. This reason is extracted into the rules.xml when creating the genmodel for the SonarQube plugin
  • Changed:
    • Halstead based metrics may produce slightly higher values, since all data types are processed as operators
    • Requires at least a Java 7 runtime environment
  • Fixed:
    • Parsing errors in XMLELEMENT and other functions when using certain non-standard function parameters

Version 2.0.3


  • New:
    • New simple example validator com.trivadis.tvdcc.validators.GLP to check naming of global variables (g_), local variables (l_) and parameters (p_) only 
    • Using new AbstractValidatorTest class for all validator unit tests
  • Fixed:
    • Wrong message for guideline 9003, use c_ instead of co_ prefix
    • Distinguish between markers and code excerpts in example validator com.trivadis.tvdcc.validators.TrivadisGuidelines3Plus
    • Use dedicated JVM for each validator test to enforce instantiation of validator singleton

Version 2.0.2


  • New:
    • Supporting Trivadis PL/SQL & SQL Coding Guidelines Version 3.2
      • New guideline numbering scheme
      • 13 new guidelines
        • Check implemented:
          • G2230: Try to use SIMPLE_INTEGER datatype when appropriate.
          • G-3150: Try to use identity columns for surrogate keys.
          • G-3180: Always specify column names instead of positional references in ORDER BY clauses.
          • G-3190: Avoid using NATURAL JOIN.
          • G-7460: Try to define your packaged/standalone function to be deterministic if appropriate.
          • G-7810: Do not use SQL inside PL/SQL to read sequence numbers (or SYSDATE)
          • G-8120: Never check existence of a row to decide whether to create it or not.
          • G-8310: Always validate input parameter size by assigning the parameter to a size limited variable in the declaration section of program unit.
        • Check not yet implemented (requires CREATE TABLE and ALTER TABLE parser support, see ticket PLSQLCOP-212):
          • G-3160: Avoid virtual columns to be visible.
          • G3170: Always use DEFAULT ON NULL declarations to assign default values to table columns if you refuse to store NULL values.
        • Check not planned to be implemented (checks per source file are not suited for this kind of guidelines):
          • G-5010: Try to use a error/logging framework for your application.
          • G-8410: Always use application locks to ensure a program unit only running once at a given time.
          • G-8420: Always use dbms_application_info to track program process transiently
      • Guidelines categorised by Severity: Blocker (2), Critical (7), Major (46), Minor (37), Info (1)
      • Guidelines assigned to one or more SQALE characteristics: Changeability (10), Efficiency (20), Maintainability (48), Portability (7), Reliability (34), Reusability (3), Security (1), Testability (11)
      • Guidelines assigned to one of the 31 SQALE subcharacteristics supported by SonarQube.
      • Defined effort to solve for every guidelines using a remediation functions supported by SonarQube.
      • Severity and SQALE characteristics are included in HTML and Excel outputs, issues are ordered by severity (Blocker, Critical, Major, Minor, Info)
      • Provided guideline example files include the new bad and good examples according Trivadis PL/SQL & SQL Coding Guidelines Version 3.2 and have been renamed to include the version 3 and version 2 guideline identifiers, e.g. guideline_2150_12.sql
    • Extended valid values for check and skip command line options
      • Severities (blocker, critical, major, minor, info) may be used in check and skip lists
      • SQALE characteristics (changeability, efficiency, maintainability, portability, reliability, reusability, security, testability) may used in check and skip list
      • Guideline numbers, severities and SQALE characteristics may be combined in check and skip lists
    • New Command-line options
      • transonly={true|false} – transform temporary XML file only, default is false
      • validator=<name> – decendent of PLSQLJavaValidator, default is com.trivadis.tvdcc.validators.TrivadisGuidelines3
    • Validators are plug-ins now, the following validators are included:
    • Custom validator as example
      • com.trivadis.tvdcc.validators.TrivadisGuidelines3Plus to be installed in plugin directory
      • Maven project, source code including unit tests for each guideline (bad and good cases)
      • Example extends the validator com.trivadis.tvdcc.validators.TrivadisGuidelines3 by 15 guidelines covering chapter 2.2 Naming Conventions for PL/SQL of Trivadis PL/SQL & SQL Coding Guidelines Version 3.2
    • PL/SQL Editor for Eclipse
      • Outline view matching PL/SQL model
      • Syntax colouring
      • Bracket matching
      • Code formatting
      • Error integration into Eclipse workbench
    • Ecore model for PL/SQL grammar
      • Classes with their Attributes
      • Superclasses
  • Improved:
    • Reduced parsing time for SQL files containing statements passed to the PL/SQL parser. Leads to an overall analysis time of 70% or less compared to version 1.0.21.
    • JDK detection
  • Fixed:
    • McCabe’s cyclomatic complexity metric increased wrongly by ELSE in IF/CASE branches, by PL/SQL blocks and by GOTO statements
    • Guideline 3120 (27) does not detect correlated subqueries without alias
    • Guideline 4340 (42) does not detect collection method COUNT in basic loop statements when used with empty parenthesis ()
    • Guideline 4350 (43) and guideline 4360 (44) are not handled as mutually exclusive, but they are
    • Guideline 4395 (50) false negatives when using “..” operator without leading space in for loops
    • Guideline 5060 (56) false positives if more than one exception is defined in an exception handler
    • Guideline 7110 (60) does not detect missing named notation when calling program units with a single parameter
    • Guideline 7130 (62) false positives and false negatives in various cases
    • single_table_insert with values_clause leads to parse errors in SQL scripts if terminated by slash instead of semicolon
    • greater equal (>=) and less equal (<=) are reported as less than (<)
  • Notes:

Version 1.0.21


  • Extended grammar to support additional keyword PERIOD, e.g. as variable name
  • Extended grammar to support keywords as  measure_column of a cell_assignment in the model_clause

Version 1.0.20


  • Fixed grammar to support inline views as target of a MERGE statement (undocumented)
  • Fixed grammar to support ASC|DESC and NULLS FIRST|LAST in row_pattern_order_by clause (undocumented)

Version 1.0.19


  • Guideline 04 (avoid dead code in your programs): fixed false positive when using subquery with false condition in insert_into_clause
  • Guideline 56 (avoid unhandled exceptions): fixed false positives when using aggregation functions AVG, MAX or MIN in SELECT INTO Statement
  • Fixed grammar to support default namespace at any position within XMLnamespaces_clause
  • Fixed grammar to support Temporal Validity in flashback_query_clause
  • Fixed grammar to support VARCHAR with BYTE and CHAR semantics (as VARCHAR2)
  • Extended grammar to support multiple flashback_query_clauses
  • Extended grammar to support “~” as quote delimiter character
  • Extended grammar to support additional keywords, e.g. as variable name
    • body
    • show
    • subtype
    • versions

Version 1.0.18


  • Fixed parse errors when using database link in merge statement.

Version 1.0.17


  • Fixed parse errors when conditional compilation blocks contain non PL/SQL code.
  • Changed default Java and startup parameters within tvdcc.cmd.

Version 1.0.16


  • Guideline 16 (Avoid using overly short names for declared or implicitly declared identifiers) does not report variables used as index in basic loops, while loops and for loops. Variable names i and j are not ignored anymore.
  • Reduced false negatives for guideline 24 (Try to use boolean data type for values with dual meaning). See also FAQ.
  • Extending grammar to support the “IS OF type Condition” fully

Version 1.0.15


  • Fixed wrong number of lines and wrong line references. Wrong metrics were reported on the following conditions only
    • file was based on Windows line separators (CR+LF) and
    • file contained lines with trailing spaces
  • Extending grammar to support additional keyword WITHIN e.g. as variable name

Version 1.0.14


  • Fixed broken link to Trivadis PL/SQL & SQL Coding Guidelines Version 2.0
  • Fixed “number of statements (PL/SQL)” metric. Labels are not counted as statements anymore.
  • Fixed calculation of Halstead volume, leading to lower values
    • Percent operators are not counted as slash operators
    • Procedure call operators are not counted additionally as function call operators
    • Strings operands are not counted twice

Version 1.0.12


  • Support undocumented DETERMINISTIC option in standalone procedures, package procedures and type procedures

Version 1.0.11


  • Support all file extensions, e.g. using the parameter filter=”(sql|pb|ps|pck)$” will process now .pb, .ps and .pck files
  • The JAVA_HOME does not need to be defined in tvdcc.cmd anymore, if java.exe is found in the path
  • Support for numeric bind variable names, e.g. :1, :2, :3

Version 1.0.10


  • Fixed error when analyzing code containing CDATA end tags “]]>”

Version 1.0.8


  • Oracle grammar support (SQL*Plus, SQL and PL/SQL), e.g.
    • JSON
      • IS JSON condition
      • JSON_EXISTS condition
      • JSON_TEXTCONTAINS condition
      • JSON_QUERY
      • JSON_TABLE
      • JSON_VALUE
  • Rating system for complexity metrics
    • Green, amber, red for
      • McCabe’s cyclomatic complexity
      • Halstead volume
      • Maintainability index
    • Legend explaining the rating system
  • Additional metrics
    • lines of comments
    • blank lines
    • net lines of code (lines of code without blank lines and comment lines)
  • Grammar fixes (supporting additional keywords such ass RULES, INTERFACE)
  • Suppress warnings if errors are reported
  • Changed context of guideline 60 (Try to use named notation when calling program units)
  • Fixed false positives on guideline 42 (Always use a NUMERIC FOR loop to process a dense array)
  • Fixed false positives on guideline 03 (Avoid defining variables that are not used) for variables or constants used in cursors only
  • Fixed false positives on guideline 62 (Always use parameters or pull in definitions rather than referencing external variables) when sequence pseudocolumns CURRVAL and NEXTVAL are used
  • Fixed non-working NOSONAR marker with a preceding comment

Version 1.0.5


  • Fixed crash when processing source files in “UTF-8 with BOM” format

Version 1.0.3


  • Renamed “Trivadis PL/SQL & SQL CodeChecker” to “Trivadis PL/SQL Cop”

Version 1.0.1


  • Oracle 12c grammar support (SQL*Plus, SQL and PL/SQL), e.g.
    • CREATE VIEW enhancements
      • VISIBLE and INVISIBLE view columns
      • BEQUEATH clause
    • SELECT enhancements
      • row_pattern_clause
      • row_limiting_clause
      • cross_outer_apply_clause
      • LATERAL (lateral inline view)
      • plsql_declarations clause
  • Grammar fixes related to 11.2

Version 0.7.2


  • Grammar fixes
  • Reduced false positives for guideline 62 – Always use parameters or pull in definitions rather than referencing external variables in a local program unit
  • Reduced redundant issues for guideline 30 – Use BULK OPERATIONS (BULK COLLECT, FORALL) whenever you have to
  • Fixed layout issues on some browsers
  • Added link to Trivadis PL/SQL & SQL Coding Guidelines Version 2.0 in HTML report

Version 0.6.7


  • Fixed slash (/) handling in SQL*Plus file preprocessor

Version 0.6.5


  • Grammar fixes

Version 0.6.4


  • Fixed null pointer exception on timeout

Version 0.6.3


  • Grammar fixes
  • New file metrics:
    • McCabe’s cyclomatic complexity (average)
    • Halstead volume (maximum and average)
    • Maintainability index (minimum and average)
  • New PL/SQL Unit metrics:
    • Halstead volume
    • Maintainability index

Version 0.5.17


  • Initial GA Release
  • Checks for compliance of all guidelines in Trivadis PL/SQL & SQL Coding Guidelines Version 2.0
  • File metrics:
    • number of warnings
    • number of errors
    • number of bytes
    • number of lines
    • number of commands
    • number of statements
    • McCabe’s cyclomatic complexity (maximum)
    • processing time
  • PL/SQL Unit metrics
    • number of lines
    • number of statements
    • McCabe’s cyclomatic complexity