PL/SQL Cop – Changelog


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

Version 2.3.0


  •  Changed
    • Guidelines
      • Link to Trivadis PL/SQL & SQL Guidelines v3.6 in HTML report.
      • Renamed G-3130 from “Try to use ANSI-join syntax” to “Try to use ANSI SQL-92 join syntax”.
      • Renamed G-3160 from “Avoid virtual columns to be visible” to “Avoid visible virtual columns”.
      • Renamed G-8410 from “Always use application locks to ensure a program unit only running once at a given time” to “Always use application locks to ensure a program unit is only running once at a given time”.
    • PL/SQL grammar
      • Support keyword MOD and other keywords as query_name in the subquery_factoring_clause.
      • Support keyword ONE, e.g. as column alias.
      • Support keyword WELLFORMED, e.g. as parameter name.
      • Support keyword WORK, e.g. as parameter name.
    • Included preview/trial license is valid thru 2020-03-31
  • Fixed
    • Guidelines
      • G-3120: false positive when using SYSDATE, SYSTIMESTAMP, NULL, LEVEL, ROWNUM, CONNECT_BY_ISLEAF in query with joins.
    • PL/SQL grammar
      • Parse error when using udf_pragma.
      • Parse error when using record with dot notation in values_clause of INSERT statement. For example INSERT INTO t VALUES in_param.rec.
      • Parse error when using NOT in expressions with A SET, NAN, INFINITE, PRESENT, EMPTY, JSON, OF TYPE. Fore exmple x IS NOT A SET.
  • Won’t Fix
    • Support of keyword JSON in table name or table alias due to conflicts with JSON_condition.
    • Empty conditional compilation branches (see also “Conditional Compilation” in What are the limitations)

Version 2.2.1


Version 2.2.0


  • Fixed
    • Guidelines
      • G-3120 – false positive when using constants in projection.
      • G-4140 – false positive when using FORALL statement.
      • G-7110 – false positive when using functions/procedures with a single parameter.
    • PL/SQL grammar
      • Parse error when using PRAGMA INLINE with a second string parameter.
  •  Changed
    • PL/SQL grammar
      • Support keyword EXCLUDE, e.g. as procedure name.
      • Support keyword INSTANTIABLE, e.g. as column name.
      • Support quote literal character / (see also “Quote Delimiter Characters” in What are the limitations.)
    • SQL*Plus grammar
      • Support $ as alias for HOST command.
    • Support registered extensions (sql, prc, fnc, pks, pkb, trg, vw, tps, tbp, plb, pls, rcv, spc, typ, aqt, aqp, ctx, dbl, tab, dim, snp, con, collt, seq, syn, grt, sp, spb, sps, pck) in upper case. However, default filter is still in lower case.
    • HTML report refers to the Trivadis PL/SQL SQL Coding Guidelines Version 3.3 hosted on GitHub.
    • Included preview/trial license is valid thru 2019-09-30

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