Releases: Trivadis/plsql-cop-cli
db* CODECOP v4.5.0
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
: Default2
. Defines the threshold (less than) before a G-1050 violation is reported.cop.2185.threshold
: Default4
. 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
: Default20000
. Defines the lower bound of an error number (greater or equal than, positive value) in guideline G-5050cop.5050.threshold.to
: Default20999
. Defines the upper bound of an error number (less or equal than, positive value) in guideline G-5050cop.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 nowusingClause.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
db* CODECOP v4.4.0
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 thetype
used in preceding versions. -
Changed error message for
E-0002
/E-02
toSyntax 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:
- 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.
- Violations are reported only when the parameter type contains
- Updated severity (
blocker
,critical
,major
,minor
,info
) of most guidelines.- Assessing the maintenance cost leads to a severity between
info
andcritical
, but neverblocker
. - 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.
- Assessing the maintenance cost leads to a severity between
- 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 abulk collect into
clause (Trivadis/plsql-cop-sqldev#19).
db* CODECOP v4.3.1
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
db* CODECOP v4.3.0
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
- New option
-
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. Forcom.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
- When the
-
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
- The following additional file extensions are now considered for code analysis by default:
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
functionThe 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
db* CODECOP v4.2.4
Changed
Common
- Included preview/trial license is valid thru 2022-12-31
db* CODECOP v4.2.3
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
-
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.
- The
-
PLSQLCOP-414/PLSQLCOP-416: CVE-2021-44228 – Log4j 2 vulnerability
- Updated log4j library to version 2.16.0.
- For more information see:
db* CODECOP v4.2.2
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
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 optionalLabelStatement
. - This affects existing
PlsqlBlock
in aBody
. ALabelStatement
is now always part of aPlsqlBlock
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.
- A
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
-
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
orinstr
. An example of a function using non-standard parameters isto_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 ofto_date
must be a function. This leads to parse errors ifto_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 writeto_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 functionscurrent_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...