Skip to content

Releases: Trivadis/plsql-cop-cli

db* CODECOP v4.5.0

22 Jan 13:31
Compare
Choose a tag to compare

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)

db* CODECOP v4.4.2

15 Dec 13:20
Compare
Choose a tag to compare

Fixed

Grammars (sqlplus)

  • #14 StringIndexOutOfBoundsException when analysing an empty file

db* CODECOP v4.4.0

04 Oct 17:57
Compare
Choose a tag to compare

New / Changed

Common

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

db* CODECOP v4.3.1

20 Jan 09:52
Compare
Choose a tag to compare

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

db* CODECOP v4.3.0

02 Sep 20:12
Compare
Choose a tag to compare

New

Common

Validators

Based on PL/SQL & SQL Coding Guidelines Version 4.2

Grammars

  • PLSQLCOP-435: Allow expression in to_yminterval function

    The SQL Language Reference defines the syntax as follows:

    image

    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

db* CODECOP v4.2.4

18 May 17:23
1ed709e
Compare
Choose a tag to compare

Changed

Common

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

db* CODECOP v4.2.3

14 Dec 17:43
1ed709e
Compare
Choose a tag to compare

Fixed

db* CODECOP v4.2.2

28 Sep 12:30
1ed709e
Compare
Choose a tag to compare

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

db* CODECOP v4.2.1

13 Sep 11:46
1ed709e
Compare
Choose a tag to compare

New

Validators

Providing validator checks for all guidelines introduced in PL/SQL & SQL Coding Guidelines 4.0:

  • PLSQLCOP-329: G-1080: Avoid using the same expression on both sides of a relational comparison operator or a logical operator.
  • PLSQLCOP-330: G-2135: Avoid assigning values to local variables that are not used by a subsequent statement.
  • PLSQLCOP-331: G-2145: Never self-assign a variable.
  • PLSQLCOP-332: G-2610: Never use self-defined weak ref cursor types.
  • PLSQLCOP-333: G-3115: Avoid self-assigning a column.
  • PLSQLCOP-334: G-3185: Never use ROWNUM at the same query level as ORDER BY.
  • PLSQLCOP-335: G-3195: Always use wildcards in a LIKE clause.
  • PLSQLCOP-336: G-3310: Never commit within a cursor loop.
  • PLSQLCOP-337: G-3320: Try to move transactions within a non-cursor loop into procedures.
  • PLSQLCOP-338: G-4250: Avoid using identical conditions in different branches of the same IF or CASE statement.
  • PLSQLCOP-339: G-4260: Avoid inverting boolean conditions with NOT.
  • PLSQLCOP-340: G-4270: Avoid comparing boolean values to boolean literals.
  • PLSQLCOP-341: G-4325: Never reuse labels in inner scopes.
  • PLSQLCOP-342: G-5080: Always use FORMAT_ERROR_BACKTRACE when using FORMAT_ERROR_STACK or SQLERRM.
  • PLSQLCOP-343: G-7125: Always use CREATE OR REPLACE instead of CREATE alone.
  • PLSQLCOP-344: G-7250: Never use RETURN in package initialization block.
  • PLSQLCOP-345: G-7330: Always assign values to OUT parameters.
  • PLSQLCOP-346: G-7720: Never use multiple UPDATE OF in trigger event clause.
  • PLSQLCOP-347: G-7730: Avoid multiple DML events per trigger if primary key is assigned in trigger.
  • PLSQLCOP-348: G-9010: Always use a format model in string to date/time conversion functions.
  • PLSQLCOP-349: G-9020: Try to use a format model and NLS_NUMERIC_CHARACTERS in string to number conversion functions.
  • PLSQLCOP-350: G-9030: Try to define a default value on conversion errors.

Fixed

Validators

  • G-7130: False positive when using locally defined types

PL/SQL Grammar - Common Issues

  • PLSQLCOP-397: Parse error with anonymous PL/SQL block starting with a label
    • A PlsqlBlock starts now with an optional LabelStatement.
    • This affects existing PlsqlBlock in a Body. A LabelStatement is now always part of a PlsqlBlock and not a standalone statement anymore.
    • This grammar change is only relevant to those customers that are using db* CODECOP as a library or write own validators.

PLSQL Grammar - Eclipse Dependencies

  • PLSQLCOP-395: class "org.eclipse.core.runtime.OperationCanceledException"'s signer information does not match signer information of other classes in the same package
    • Eclipse Xtext 2.25.0 uses dependencies based on version ranges.
    • When using db* CODECOP as a library building was not possible anymore due to the release of incompatible new Eclipse libraries within the defined version range
    • All Xtext 2.25.0 runtime dependencies are now references with an explicit version, so the release of updated Eclipse libraries should not affect future builds anymore

db* CODECOP v4.1.3

15 May 18:50
504980b
Compare
Choose a tag to compare

New

  • PLSQLCOP-375: Support for JDK16 and JDK17

    • The warning regarding "An illegal reflective access operation has occurred" is not thrown in JDK11 and JDK15 anymore
    • The JVM parameter --illegal-access=warn is no longer required, when using JDK16 or JDK17
    • JDK8 is still supported
  • PLSQLCOP-380: Support for SQL*Plus substitution variables

    In pervious version substitution variables were supported in the SQL*Plus grammar only. This worked well when the substitution variables were used in SQL*Plus commands. However, when substitution variables are used in commands supported by the PL/SQL grammar (e.g. the SELECT statement), then the PL/SQL parser failed to process the substitution parameter.

    In this version we added limited support for substitution variables. This means the parser can process the code when the substitution variable is used for a SQL expression or condition. The following statement is supported:

    select &&column_name 
      from emp
     where &&where_condition;

    However, the following example is not supported:

    select empno, ename 
      from emp
     &after_from_clause;

    In this case the parser would expect a table alias and a table alias cannot be an expression. As a result the parser will return an error.

Changed

  • PLSQLCOP-375: Updated 3rd Party Libraries

    • Eclipse Xtext 2.25.0
    • Eclipse Xtend 2.25.0
    • Google Guice 5.0.1
  • PLSQLCOP-387: Update lists of keywords, SYS packages and predefined exceptions

    • Lists are based on objects available within an Oracle Database 21c always-free ATP instance as of 2021-05-14
    • The following guideline checks use these lists
      • G-5030: Never assign predefined exception names to user defined exceptions.
      • G-5070: Avoid using Oracle predefined exceptions.
      • G-7110: Try to use named notation when calling program units.
      • G-7510: Always prefix ORACLE supplied packages with owner schema name.
  • PLSQLCOP-374: Desupport of unquoted keywords used as identifiers for functions with non-standard parameters

    As mentioned in Parser Limitations it is difficult to support keywords as identifiers in our Xtext / Antlr 3 based grammar. To simplify the grammar we decided to desupport some special treatment of keywords. Namely those that represent a SQL function with non-standard parameters.

    Standard parameters follow the notation defined in the Database PL/SQL Language Reference. Examples are add_months, greatest or instr. An example of a function using non-standard parameters is to_date. The non-standard part is highlighted in the following syntax diagram:

    image

    Due to these non-standard parameters the to_date function has been added to the grammar. As a result the parser assumes that every use of to_date must be a function. This leads to parse errors if to_date is used as an identifier. For example as column name, column alias, table name, table alias, variable, parameter name, etc. The workaround is to write to_date in double quotes like this "TO_DATE".

    The following function names cannot be used as unquoted identifiers anymore:

    cast, collect, feature_compare, json_array, json_arrayagg, json_mergepatch, json_object, json_objectagg, json_query, json_scalar, json_serialize, json_transform, json_value, listagg, to_binary_double, to_binary_float, to_date, to_dsinterval, to_number, to_timestamp, to_timestamp_tz, to_yminterval, treat, validate_conversion, xmlagg, xmlcast, xmlcolattval, xmlelement, xmlparse, xmlpi.

Fixed

Common

  • PLSQLCOP-388: Wrong "processing time in seconds" reported

Validators

  • PLSQLCOP-309, Trivadis/plsql-cop-sqldev#7: G-3120: False positive reported when using USER function

    Added pseudocolumn connect_by_iscycle and parameterless functions current_date, current_timestamp, dbtimezone, iteration_number, localtimestamp, ora_invoking_user, ora_invoking_userid, sessiontimezone, uid, user.

  • PLSQLCOP-311, Trivadis/plsql-cop-sqldev#10: G-4350: False positive when using subquery in for loop with COUNT or FIRST or LAST token

SQL*Plus Grammar

  • PLSQLCOP-368: Use of single line comment or line continuation character in REMARK or PROMPT command leads to parse errors or missing PL/SQL commands

PL/SQL Grammar - Common Issues

  • PLSQLCOP-310: Parse error when using NATURAL join without alias
  • PLSQLCOP-322: Table alias not recognized when parenthesis are used around table expression
  • PLSQLCOP-355: Parse error when using a placeholder for delimiter in LISTAGG (undocumented)
  • PLSQLCOP-356: Parser error when defining a VARRAY size with a constant (undocumented)
  • PLSQLCOP-369: Use of §§ as quote delimiter character pair (q'§...§') is not supported
  • PLSQLCOP-377: Parse error when NLS parameter in TO_NUMBER is not a string literal (undocumented)
  • PLSQLCOP-378: Parse error when separator in LISTAGG is an expression (undocumented)
  • PLSQLCOP-379: Parse error when using LISTAGG without an order_by_clause
  • PLSQLCOP-385: Parse error when using NATURAL datatype
  • PLSQLCOP-386: Cannot parse single line comment ending on CR, CR, LF
  • PLSQLCOP-389: Parse errors when using member functions to_date(), to_number(), to_timestamp()

PL/SQL Grammar - Keyword usage as unquoted identifier

  • PLSQLCOP-316: Keyword GROUPS leads to parse errors when used as identifier
  • PLSQLCOP-317: Keyword RULES leads to parse errors when used as identifier
  • PLSQLCOP-318: Keyword PAIRS leads to parse errors when used as identifier
  • PLSQLCOP-319: Keyword REMOVE leads to parse errors when used as identifier
  • PLSQLCOP-320: Keyword EXTRA leads to parse errors when used as identifier
  • PLSQLCOP-321: Keyword FILTER leads to parse errors when used as identifier
  • PLSQLCOP-324, PLSQLCOP-384: Keyword OFFSET leads to parse errors when used as variable/variable name
  • PLSQLCOP-325: Keyword ABS leads to parse errors when used as identifier
  • PLSQLCOP-326: Keyword EXISTING leads to parse errors when used as identifier
  • PLSQLCOP-327: Keyword LOGOFF leads to parse errors when used as identifier
  • PLSQLCOP-328: Keywords such as POSITION lead to parse errors in unpivot clause
  • PLSQLCOP-363: Keyword KEYS leads to parse errors when used as identifier
  • PLSQLCOP-364: Keyword HIERARCHY leads to parse errors when used as identifier
  • PLSQLCOP-365: Keyword ADD leads to parse errors when used as identifier
  • PLSQLCOP-366: Keyword MAPPING leads to parse errors when used as identifier
  • PLSQLCOP-367: Keyword DATA, PATH leads to parse errors when used as identifier
  • PLSQLCOP-371: Keyword OBJECT leads to parse errors when used as identifier
  • PLSQLCOP-382: Keyword ACCESS, ACROSS, ANCESTOR, BEGINNING, DIRECTORY, FACT, MISMATCH, MISSING, PERISTABLE, POLYMORPHIC, SCALAR, SCALARS Lead to parse errors when used as identifier

Won't Fix

  • PLSQLCOP-323: Parse error when line ends on slash within PL/SQL code
    Related/Conflicting to PLSQLCOP-280, where standalone UPDATE or DELETE statement raised error when ending on "/" instead of ";". Fix is not easy. Ending a line with a slash within PL/SQL is less common than ending an SQL statement with a slash.

  • PLSQLCOP-370: Keyword JSON_OBJECT leads to parse errors when used as identifier
    See PLSQLCOP-374

  • PLSQLCOP-372: Missing commands or parse error when "END;" and "/" have trailing spaces
    Limitation of SQL*Plus parser, see https://github.com/Trivadis/plsql-cop-cli/blob/main/parser-limitations.md#sqlplus-parser

  • PLSQLCOP-376: Keyword COLLECT leads to parse errors when used as identifier
    See PLSQLCOP-374

  • PLSQLCOP-381: No parse error when processing code with lines ending on slash in PL/SQL
    This is intentional. There is a pre-processing defined for a SqlPlusResource that fixes some common issues without changing the file size. However, in some cases the number of lines are affected. In this case the following rules are applied:

    • if '/' is not the first character in a line, ensure that it does not end on new line
    • if '/' is the first character in a line, ensure that it does end on new line
    • ensure that comments are excluded from...