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 this processing

• PLSQLCOP-383: Keyword TO_DATE leads to parse errors when used as identifier
  See PLSQLCOP-374