db* CODECOP for SQL Developer v4.0.4

New

• Based on db* CODECOP v4.0.3
  • see Release Notes

Changed

• PLSQLCOP-312: Rebrand "PL/SQL Cop for SQL Developer" to "db* CODECOP for SQL Developer"

PL/SQL Cop for SQL Developer v3.0.1

New

• Common

  • PLSQLCOP-271: JDK 8 is required, JDK 11 is supported
  • PLSQLCOP-275: Xtext and Xtend updated to v2.23.0 (important for custom validators)
  • Custom validators for PL/SQL Cop v2 are not supported (need to be updated to v3)

• 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 for SQL Developer v2.3.2

• Fixed
  • Serverity and characteristics are not honored in Issues tab. Default values are shown when using an own validator with a different guideline naming scheme (works in Reports tab).

PL/SQL Cop for SQL Developer v2.3.1

• 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)

  • 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 for SQL Developer 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 for SQL Developer 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.
  • HTML report refers to the Trivadis PL/SQL & SQL Coding Guidelines Version 3.3 hosted on GitHub.

PL/SQL Cop for SQL Developer v2.1.3

• Released on 2017-09-22
• Fixed:
  • Using quote delimiter character ~ (tilde) leads to parse errors
  • Parse error when using COUNT in pivot_clause

PL/SQL Cop for SQL Developer v2.1.2

• Released on 2017-08-15
• Fixed:
  • Using concatenation operator || with whitespace between the vertical bars leads to parse errors
• Changed
  • Documented limitation regarding error_logging_clause, see Parser Limitations for examples

PL/SQL Cop for SQL Developer v2.1.1

• Released on 2017-05-14
• New:
  • Oracle 12.2.0.1 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
• Changed:
  • Halstead based metrics may produce slightly higher values, since all data types are processed as operators
• Fixed:
  • Parsing errors in XMLELEMENT and other functions when using certain non-standard function parameters

PL/SQL Cop for SQL Developer v2.0.1

• Released on 2017-02-05
• New:
  • Supporting Trivadis PL/SQL & SQL Coding Guidelines Version 3.2
    • New guideline numbering scheme
    • 13 new guidelines
      • Check implemented:
        • G-2230: 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.
        • G–3170: 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
  • Validators are plug-ins now, the following validators are included:
    • com.trivadis.tvdcc.validators.TrivadisGuidelines3 – Trivadis PL/SQL & SQL Coding Guidelines Version 3.2 (default)
    • com.trivadis.tvdcc.validators.TrivadisGuidelines2 – Trivadis PL/SQL & SQL Coding Guidelines Version 2.0
    • com.trivadis.oracle.plsql.validation.EmptyPLSQLJavaValidator – validator without checks, useful to produce code metrics only
  • New preferences validator and plugin path to configure provided and custom validators
• 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 (<)