db* CODECOP v4.2.1

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

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:

  

  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...

db* CODECOP v4.0.5

Fixed

• PLSQLCOP-373: No files processed under Windows
  • Based on #8 processing file 'null'... error with db*CODECOP 4.0.4 command-line
  • The bug did not affect non-Windows platforms like macOS and Linux

db* CODECOP v4.0.4

Changed

• PLSQLCOP-352: Set max Java heap size to 4GB (-Xmx4g)

Fixed

• PLSQLCOP-351: Currently processed files are not shown (console output is flushed at the end of processing)
• PLSQLCOP-353: High memory consumption when processing large directories
• PLSQLCOP-354: Do not log error on console when timeout is reached

db* CODECOP v4.0.3

New

• Common

  • PLSQLCOP-246: Lifted limitations for expression in filter option.
    • Any file extension can now be processed.
    • For examples files ending on .xyz or .some_file_containing_sql.

• Grammars

  • PLSQLCOP-300: Add support for SQL*Plus Grammar 18c/19c/21c

    • Changes in 18c
    • Changes in 19c
    • Changes in 21c
    • SQLcl User's Guide

  • PLSQLCOP-302: Add support for SQL Grammar 18c/19c/21c

    • Changes in 18c
    • Changes in 19c
    • Changes in 21c

  • PLSQLCOP-301: Add support for PL/SQL Grammar 18c/19c/21c

    • Changes in 18c
    • Changes in 19c
    • Changes in 21c

• Validator (com.trivadis.tvdcc.validators.TrivadisGuidelines3)

  • PLSQLCOP-296: Implement changes according Trivadis PL/SQL & SQL Coding Guidelines 4.0

    • Updated all titles, reasons, examples (lowercase keywords)
    • Added all 22 new guidelines, however the checks are not yet implemented.

Changed

• Common

  • PLSQLCOP-312: Rebrand "PL/SQL Cop" to "db* CODECOP"
  • Eclipse Xtext and Xtend updated to v2.24.0
  • Apache POI Office Open XML updated to v4.1.2
  • Apache XMLBeans updated to v3.1.0
  • Included preview/trial license is valid thru 2021-10-02

• Validator (com.trivadis.tvdcc.validators.TrivadisGuidelines3)

  • G-7130: Always use parameters or pull in definitions rather than referencing external variables in a local program unit.

    • Ignoring names starting with co_ to reduce false positives.

  • G-7160: Always explicitly state parameter mode.

    • Formally implemented as G-7240
    • Parameters in package specification are now checked too.

  • G-7240 Avoid using an IN OUT parameter as IN or OUT only

    • removed and replaced it with these rules:
      • G-7160: Always explicitly state parameter mode. (implemented).
      • G-7170: Avoid using an IN OUT parameter as IN or OUT only. (not yet implemented).

  • G-9010, G-9020, G-9030

    • These guideline numbers may conflict with custom guidelines.
    • The custom guidelines must be changed and use non-conflicting numbers.

Fixed

• Common

  • PLSQLCOP-304: tvdcc throws NoClassDefFoundError when using JDK > 8
  • PLSQLCOP-314: tvdcc plugin mechanism does not work with JDK > 8
  • PLSQLCOP-305: existing JAVA_HOME environment variable is not considered in tvdcc.sh

• PL/SQL Grammar

  • PLSQLCOP-308: Keyword append cannot be used in procedure name, NullPointException in PlsqlUnitMetric
  • PLSQLCOP-306: Parse error when using json_serialize and json_object with returning blob clause (19c feature)

Open Bugs

• Related to validators:

  • PLSQLCOP-309: G-3120: False positive reported when using USER function
  • PLSQLCOP-311: G-4350: False positive when using cursor variable

• Related to limitations regarding keywords:

  • PLSQLCOP-316: Keyword GROUPS leads to parse errors
  • PLSQLCOP-317: Keyword RULES leads to parse errors
  • PLSQLCOP-318: Keyword PAIRS leads to parse errors
  • PLSQLCOP-319: Keyword REMOVE leads to parse errors
  • PLSQLCOP-320: Keyword EXTRA leads to parse errors
  • PLSQLCOP-321: Keyword FILTER leads to parse errors
  • PLSQLCOP-324: Keyword OFFSET leads to parse errors
  • PLSQLCOP-325: Keyword ABS leads to parse errors
  • PLSQLCOP-326: Keyword EXISTING leads to parse errors
  • PLSQLCOP-327: Keyword LOGOFF leads to parse errors
  • PLSQLCOP-328: Keyword POSITION leads to parse errors

• Other parser bugs

  • PLSQLCOP-310: Parse error when using natural join without alias
  • PLSQLCOP-322: Table alias not recognized when parenthesis are used around table expression
  • PLSQLCOP-323: Parse error when line ends on slash

PL/SQL Cop v3.0.0

New

• Common

  • PLSQLCOP-271: JDK 8 is required, JDK 11 is supported (important for custom validators)
  • PLSQLCOP-275: Xtext and Xtend updated to v2.23.0 (important for custom validators)
  • PLSQLJavaValidator renamed to PLSQLValidator (important for custom validators)
  • PL/SQL Editor plugin for Eclipse requires Eclipse IDE 2020-09 for Java and DSL Developers
  • Included preview/trial license is valid thru 2021-04-30 (#4)

• Validator (com.trivadis.tvdcc.validators.TrivadisGuidelines3)

  • PLSQLCOP-284: G-7410: do not throw warning for standalone functions with aggregate_clause
  • PLSQLCOP-285: G-8310: do not throw warning for "OUT" only parameter

Fixed

• SQL*Plus Grammar

  • PLSQLCOP-274: Space slash NL not handled (for files with Unix line separators only)

• PL/SQL Grammar

  • PLSQLCOP-262: Parse error when using a * (star) for precision in number data type
  • PLSQLCOP-276: Parser error when TABLE operator is not used for package function
  • PLSQLCOP-277: Parse error when using expressions without space in for loop
  • PLSQLCOP-280: Parse errors when update statements are terminated with slash instead of semicolon (Trivadis/plsql-cop-sonar#1)
  • PLSQLCOP-287: Parse error when using apex_escape.json due to JSON keyword

• Validator (com.trivadis.tvdcc.validators.TrivadisGuidelines3)

  • PLSQLCOP-283: G-7110 false positives when initializing a collection with user prefix and new operator
  • PLSQLCOP-286: Parse error with when using a variable/constant for error_code in pragma exception_init
  • PLSQLCOP-288: False positive on G-8110 when using "ROWNUM = 1"
  • PLSQLCOP-289: G-6020: False positive with using_clause and in parameter mode

PL/SQL Cop v2.3.1

• Changed

  • ReadMe.html instead of ReadMe.pdf linking to current README.md on GitHub
  • PLSQL-and-SQL-Coding-Guidelines.html instead of PLSQL-and-SQL-Coding-Guidelines.webloc linking to the current Trivadis PL/SQL & SQL Coding Guidelines
  • rules.xml generated by genmodel=true uses a CDATA section in the name node to propertly deal with special characters in custom guidelines
  • Included preview/trial license is valid thru 2020-09-30

• Fixed

  • Common

    • incomplete results when analyzed source code contains TREAT (due to NPE, see #1)
    • When using a own validator with a different guideline naming scheme the configured severity and characteristics per guideline are not honored and the defaults are reported (Blocker for severity and all characteristics)
    • Cannot install Eclipse plugin PLSQLEditor in v2.3.0 (provided archive was incomplete)

  • Guidelines

    • G-7230 should not be thrown for constants in package specifications (see #79)

  • SQL*Plus grammar

    • Wrong line reported after comment lines ending on slash (/) for files using Unix line separators

  • PL/SQL grammar

    • Parse error when JSON_value_return_type is not defined.
      Documentation changed between version 12c and 18c. However, the following code works in Oracle Database 12c Release 1:

      SELECT *
        FROM JSON_TABLE (
                (SELECT '{"col1":"foo"}' doc FROM dual), 
                '$' COLUMNS (col1  PATH '$.col1')
             );

    • Parse error when a row_limiting_clause is used without an order_by_clause

    • Parse error when using expressions for offsetor rowcount in row_limiting_clause

PL/SQL Cop v2.3.0

• Released on 2019-09-28
• 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 example 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 Parser Limitations)

PL/SQL Cop v2.2.1

• Released on 2019-05-31
• Fixed
  • Renamed guideline G-8420 to G-8510 according GitHub issue 7
  • Fixed bad example for G-8410, see also GitHub issue 21
• Changed
  • Support extension .tpb as used in utPLSQL project for type body files
  • Added link file to Trivadis PL/SQL & SQL Coding Guidelines as replacement for PDF file
  • Removed example validators project, it’s provided as dedicated open source project cop-validators on GitHub

PL/SQL Cop v2.2.0

• Released on 2019-04-04
• 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 Parser 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