Fixed

PL/SQL & SQL Grammar

• Parse error in searched case expression (#30)
• Parse error when using PL/SQL mod operator (#31)
• Parse error in json_object with pretty, ascii options (#32)
• Parse error using self instead of table name in %type declaration (#33)

CLI

• Default filter considers partial matching file extensions (#29)

Validators / Examples

• Missing </blockquote> in SQL Script of G-3330: Avoid autonomous transaction #28

New / Changed

PL/SQL & SQL Grammar

• Support New Features/Changes in SQL Grammar 23c (#23)

• Support New Features/Changes in PL/SQL Grammar 23c (#22)

• Support New Features/Changes in SQL*Plus Grammar 23c (#20)

• Support New Features/Changes in SQLcl Grammar 23.4 (#21)

• The use as unquoted identifiers has been restricted for the following keywords:

  • escape (as custom data type)
  • final (as custom data type)
  • member (as column alias, custom data type)
  • running (as custom data type)
  • table (everywhere except data type)

  Please note that a parsing error caused by the use of a keyword as an unquoted identifier is not considered a db* CODECOP bug, see also Use of Keywords

Validators

• All validator checks are based on PL/SQL & SQL Coding Guidelines Version 4.4.
• All validator checks are updated based on changes in the underlying grammar
• Implemented new guideline G-3330: Avoid autonomous transactions.
• Registered new guideline G-4387: Never use a FOR LOOP for a query that should return not more than one row.
• Do not report G-7440 when in out is used for self as part of a fluent API (#27)

License File

• A new license file is required for this version
• Included preview/trial license is valid thru 2025-01-01

New / Changed

Common

• Consider function definitions in package spec and object type spec not considered for "Number of Functions" metric (Azure DevOps 65459)

Validators

• Do not report G-8210 violations for views owned by SYS (#18)
• Configure guidelines via System properties (Azure DevOps 65421)
  • cop.1050.threshold: Default 2. Defines the threshold (less than) before a G-1050 violation is reported.
  • cop.2185.threshold: Default 4. Defines the threshold (less than) before a G-2185 violation is reported.
  • cop.2410.boolean.strings: Default: true, false, t, f, 0, 1, 2, yes, no, y, n, ja, nein, j, si, s, oui, non, o, l_true, l_false, co_true, co_false, co_numeric_true, co_numeric_false. Defines the literals that represent a boolean value to identify violations of G-2410.
  • cop.5050.threshold.from: Default 20000. Defines the lower bound of an error number (greater or equal than, positive value) in guideline G-5050
  • cop.5050.threshold.to: Default 20999. Defines the upper bound of an error number (less or equal than, positive value) in guideline G-5050
  • cop.7210.threshold: Default: 2000. Defines the threshold (less than) before a G-7210 violation is reported.
  • Define constant remediation cost per issue for all guidelines (Azure DevOps 65461)
    • 1 Minute, easy, can be done mechanically e.g. via refactoring support in the IDE (rename local variable)
    • 5 Minutes, local change, but needs some simple other tasks (understanding code, lookup alternatives in the manual, etc.)
    • 10 Minutes, local or distributed change, might need a bit more work/analysis, e.g. rewrite query to ANSI SQL-92 join syntax or identifying loose or dense arrays
    • 60 Minutes, requires logic and or structure change, e.g. storing PK columns instead of ROWIDs

Fixed

Common

• Invalid JSON format produced in tvdcc_report.json when the message contains double quotes (#24)

Grammars (plsql)

• Parse error when using table function in using_clause auf merge statement (Azure DevOps 68619)

  Using a table function as in the following example is not documented

  merge into t
  using f() s
     on (t.id = s.id)
   when matched then
        update
           set t.c1 = s.c1;

  This fix caused a change in the underlying model. Validators using the usingClause.getTable() need to use now usingClause.getQte().getQteName() to access the table name in the using_clause of the merge statement. See also Trivadis/plsql-cop-validators@1cf838f

Validators

• False positive for G-7430 in functions when the declare section contains other functions (#15)
• False positive for G-6020 when dynamic SQL is not an INSERT, UPDATE or DELETE statement (#16)
• Duplicate issues for G-7430 in standalone functions (#15)
• Highlighting the area of G-7460 violations is too extensive for standalone functions (#17)
• False positive for G-3120 when using star * (#19)
• False positive for G-3183 when using table alias or table (#26)

Fixed

Grammars (sqlplus)

• #14 StringIndexOutOfBoundsException when analysing an empty file

New / Changed

Common

• The generic issue import JSON file now contains a type attribute (BUG, CODE_SMELL, VULNERABILITY) which will be considered in SonarQube versions 7, 8 and 9. SonarQube 10 introduced new concepts based on clean code attributes, which are not compatible with the type used in preceding versions.

• Changed error message for E-0002/E-02 to Syntax error. Please check the limitations and contact the author if the code can be compiled successfully in your environment. (Trivadis/plsql-cop-sqldev#19).

• Documented limitations regarding unquoted identifiers (Trivadis/plsql-cop-sqldev#19)

• Included preview/trial license is valid thru 2025-01-01

Validators

• All validator checks are based on PL/SQL & SQL Coding Guidelines Version 4.3.
• New guidelines, applicable in an Oracle Database 23c only:
  • G-3182: Always specify column names instead of positional references in GROUP BY clauses.
  • G-3183: Always specify column aliases instead of expressions in GROUP BY clauses.
• Updated guideline G-1050: Avoid using literals in your code.
  • An issue reported only when the threshold number per literal is reached.
  • The default threshold is 2, this means no issue is reported if a literal is used once within a file.
  • The default threshold can be overridden via the Java system property cop.1050.threshold.
• Updated guideline G-8310: Always validate input parameter size by assigning the parameter to a size limited variable in the declaration section of program unit.
  • Violations are reported only when the parameter type contains char, dec, interval, number, numeric, %type.
  • For these data types the length is not defined and therefore assigning it to a size limited variable makes sense.
  • However, for %type definitions there are still false positives possible when the underlying data type does not contain a size limiting component.
  • %type definitions cannot be resolved with static code analysis based on a single file scope.
• Updated severity (blocker, critical, major, minor, info) of most guidelines.
  • Assessing the maintenance cost leads to a severity between info and critical, but never blocker.
  • If a violation of an issue may impact the resource usage (CPU, memory, runtime performance) the severity is at least critical.
  • If a violation of an issue may lead to an incorrect result or a runtime exception the severity is defined as blocker. The issue is considered a bug.
• Private methods in validators are declared now as protected to simplify overriding them in custom validators.

Grammars

• Updated PL/SQL editor plugin for Eclipse.

Fixed

Grammars

• Parse error when using overriding in a map member function in type body

Validators

• G-7330 False positive when an out parameter is populated in a bulk collect into clause (Trivadis/plsql-cop-sqldev#19).

Fixed

Validators

• PLSQLCOP-441: G-3120: False positive when using local variables in the select list
• PLSQLCOP-440: G-7130:False positives for method calls

SonarQube Plugin Library

• Trivadis/plsql-cop-sonar#7 Not a valid line for pointer

New

Common

• PLSQLCOP-412: Produce additional file in SonarQube's generic issue import format

  • New option json={true|false}
  • The SonarQube generic issue import JSON file is created by default
  • Set property sonar.externalIssuesReportPaths when running sonar-scanner

• PLSQLCOP-429: Update provide SQL examples according PL/SQL & SQL Coding Guidelines Version 4.2

  • Synchronized examples with the PL/SQL & SQL Coding Guidelines repository as of 2022-08-31
  • Reduced the number of unrelated guideline violations

• PLSQLCOP-431: Update guideline version to 4.2.0

  • Links to PL/SQL & SQL Coding Guidelines Version 4.2 updated
  • Updated all checks

• PLSQLCOP-435: By default, disable checks which cause a lot of false positives

  • When the skip parameter is empty the then all checks marked as "disabled" will be skipped. For com.trivadis.tvdcc.validators.TrivadisGuidelines3 these are currently:
    • G-0000: Avoid using the NOSONAR marker.
    • G-1050: Avoid using literals in your code.
    • G-2130: Try to use subtypes for constructs used often in your code.
    • G-3160: Avoid visible virtual columns.
    • G-3170: Always use DEFAULT ON NULL declarations to assign default values to table columns if you refuse to store NULL values.
    • G-5010: Try to use a error/logging framework for your application.
    • G-7170: Avoid using an IN OUT parameter as IN or OUT only.
    • G-7460: Try to define your packaged/standalone function deterministic if appropriate.
    • G-7740: Never handle multiple DML events per trigger if primary key is assigned in trigger.
    • G-8410: Always use application locks to ensure a program unit is only running once at a given time.
    • G-8510: Always use dbms_application_info to track program process transiently.
  • To enable all checks pass skip=none

• PLSQLCOP-437: Support analysis of Markdown files

  • The following additional file extensions are now considered for code analysis by default: .md, .mdown
  • Only the code within SQL code blocks is analyzed
  • If other file extensions than .md and .mdown are used then the content must start with a # to be identified as a Markdown file

Validators

Based on PL/SQL & SQL Coding Guidelines Version 4.2

• PLSQLCOP-313: Implement exception for G-5040 (allow logger calls in when others exception handler)
• PLSQLCOP-420: Check for G-7910: Never use DML within a SQL macro.
• PLSQLCOP-421: Check for G-3220: Always process saved exceptions from a FORALL statement.
• PLSQLCOP-422: Check for G-4365: Never use unconditional CONTINUE or EXIT in a loop.
• PLSQLCOP-423: Check for G-3145: Avoid using SELECT * directly from a table or view.
• PLSQLCOP-424: Check for G-9040: Try using FX in string to date/time conversion format model to avoid fuzzy conversion.

Grammars

• PLSQLCOP-435: Allow expression in to_yminterval function

  The SQL Language Reference defines the syntax as follows:

  

  Based on that only strings can be passed as first argument to the function. However, it's possible to pass an expression, even if it is not documented. The PL/SQL parser has been changed accordingly.

Library Dependencies

• PLSQLCOP-419: Update Eclipse Xtext and Xtend from 2.25.0 to 2.27.0

Fixed

Validators

• PLSQLCOP-418: G-4130 false positive when cursor is closed in if branch
• PLSQLCOP-427: G-7730 false positive when using sql%buik_exceptions as parameter of a procedure/function call
• PLSQLCOP-428: G-7150 false positive for parameters in sql_macro (disable this check for SQL macros)
• PLSQLCOP-435: G-4395 false positive when lower bound is 1
• PLSQLCOP-438: G-1030 false negative when same variable/constant/exception name is defined in multiple contexts

Changed

Common

• Included preview/trial license is valid thru 2022-12-31

Fixed

• PLSQLCOP-403: Possible NPE while checking G-5060/G-56: Avoid unhandled exceptions (CWE 476)

  • There are no known problems, but it is theoretically possible.

• PLSQLCOP-404: Possible NPE while checking G-4250: Avoid using identical conditions in different branches of the same IF or CASE statement (CWE 476)

  • There are no known problems, but it is theoretically possible.

• PLSQLCOP-408: Parse errors when using parallel_enable clause with hash/range/value

  • Only ANY worked. All other variants lead to parser errors.

    

• PLSQLCOP-411: NOSONAR marker not considered within line

  • The NOSONAR marker is now recognized on every position in the line.
  • Previously the NOSONAR marker was recognized only after the column position of the reported violation.

• PLSQLCOP-414/PLSQLCOP-416: CVE-2021-44228 – Log4j 2 vulnerability

  • Updated log4j library to version 2.16.0.
  • For more information see:
    • https://www.lunasec.io/docs/blog/log4j-zero-day/
    • https://www.randori.com/blog/cve-2021-44228/
    • https://github.com/mergebase/log4j-detector
    • https://logging.apache.org/log4j/2.x/changes-report.html#a2.16.0
    • https://community.sonarsource.com/t/sonarqube-sonarcloud-and-the-log4j-vulnerability/54721

Changed

Common

• Included preview/trial license is valid thru 2022-04-01

Fixed

Validators

• PLSQLCOP-400: G-5080: false positive when using format_error_backtrace