The plsql-cop-cli from trivadis

New license

Hi,
How I can get a new license for PL\SQL cop?

Support New Features/Changes in SQLcl Grammar 23.3

The currently supported SQLcl grammar is 21.1c. See supported versions

We should add support for 23.4.

Reference

See

We use a shallow parser for SQL*Plus and SQLcl. As a result, we usually need to adapt the grammar only for new commands.

New Features

Do not report G-8210 violations for views owned by SYS

The following code throws a violation of G-8210:

begin
   <<demo>>
   for r in (
      select synonym_name, table_owner, table_name
        from sys.user_synonyms -- G-8210 should not be thrown in this case
   )
   loop
      sys.dbms_output.put_line(r.synonym_name); -- prefixed according G-7510
   end loop demo;
end;
/

Even if sys objects are not mentioned in G-8210 it would make sense to allow the prefix for views and tables owned by SYS for example to ensure that the original object is used.

Support New Features/Changes in SQL Grammar 23c

The currently supported SQL grammar is 21c. See supported versions

We should add support for 23c.

Reference

See

New Features in SQL Language Reference 23c

New SQL Features not mentioned in SQL Language Reference 23c

Parse error in searched case expression

Here's an example:

select case
          when length(dummy) > 1 then
             'yes'
          else
             'no'
       end case
  from dual;

Highlighting the area of G-7460 violations is too extensive for standalone functions

Violations of G-7460 are reported in the following cases:

create or replace package body pkg is
   function f return integer is
   begin
      return 1;
   end f;
end pkg;
/

create or replace function f return integer is
begin
   return 1;
end f;
/

That's ok. However, the highlighted area which becomes visible in the "Code Excerpt" in the db* CODECOP reports or in the highlighted area in the SQL Developer extension or in SonarQube is too extensive.

Here is the screenshot for the violation in the package function:

And here is the screenshot for the violation in the standalone function:

The reported scope should be the same for standalone functions as for package functions.

False postive for G-3120 when using star `*`

A violation of G-3120 is reported in the following case:

select count(*) as synonym_count -- G-3120
  from all_synonyms s
  join user_objects o
    on o.object_name = s.table_name
 where s.owner = 'PUBLIC';

A prefix for * is not possible in this case. In other cases, it's not possible to use * without an alias prefix. We should therefore exclude the check for all column wildcard expressions.

False postive for G-7430 in functions when declare section contains other functions

Violations of G-7430 is reported in the following case:

create or replace function f1 return integer is
   function f2 return integer is
      function f3 return integer is
      begin
         return 3; -- G-7430 false positive
      end;
   begin
      return f3; -- G-7430 false positive
   end;
begin
   return f2; -- G-7430 false positive
end;
/

On line 5, 8 and 11. These are false positives. The scope of a function is not considered. Every function has in fact just one return statement.

✨ FR: VSCode extension

Since there is a plugin for Atom, it would be nice if there would be an extension for VSCode also :D

TREAT keyword stops PL/SQL Cop parsing

Hello,

When using the TREAT keyword in a PL/SQL package the PL/SQL Cop doesn't validate anything anymore. Instead error messages from the previous result are shown.
E.g.

DECLARE
l_result poc_api.groeptabellen_one_of_ot;
a poc_api.patient_adres_tabel_ot;
BEGIN
a := treat( l_result AS poc_api.patient_adres_tabel_ot );
END;

We also did not found any workaround to overcome this behavior.

Substitution variables

How can i check files with substitution variables in my scripts?
For example:

CREATE TABLE &&emp..locations (
   location_id   NUMBER(10)        NOT NULL,
   location_name VARCHAR2(60 CHAR) NOT NULL,
   city          VARCHAR2(30 CHAR) NOT NULL,
   CONSTRAINT locations_pk PRIMARY KEY (location_id)
);

CREATE SEQUENCE &&emp..location_seq START WITH 1 CACHE 20;

CREATE OR REPLACE TRIGGER &&emp..location_br_i
   BEFORE INSERT ON &&emp..locations
   FOR EACH ROW
BEGIN
   :new.location_id := location_seq.nextval;
END;
/

CREATE TABLE &&emp..locations (
   location_id   NUMBER(10) GENERATED ALWAYS AS IDENTITY,
   location_name VARCHAR2(60 CHAR) NOT NULL,
   city          VARCHAR2(30 CHAR) NOT NULL,
   CONSTRAINT locations_pk PRIMARY KEY (location_id)
);

tvdcc.cmd path="sample\sample"

Result:

E-0002: Syntax error. Please contact the author if the code compiles successfully in your environment.

StringIndexOutOfBoundsException when analysing an empty file

db* CODECOP Version 4.4.0 (2023-10-04 16:38:00 +0200) crashes with the following error stack when an empty file is processed (test.sql has a size of 0 bytes):

processing file 'test.sql'... 0    [main] ERROR      com.trivadis.tvdcc.CodeChecker  - java.util.concurrent.ExecutionException: java.lang.StringIndexOutOfBoundsException: index 0, length 0
java.lang.RuntimeException: java.util.concurrent.ExecutionException: java.lang.StringIndexOutOfBoundsException: index 0, length 0
	at com.trivadis.tvdcc.process.TimingOutResourceValidator.validate(TimingOutResourceValidator.java:43)
	at com.trivadis.tvdcc.process.ValidationTask.analyze(ValidationTask.java:146)
	at com.trivadis.tvdcc.process.ValidationTask.process(ValidationTask.java:126)
	at com.trivadis.tvdcc.CodeChecker.validateFiles(CodeChecker.java:253)
	at com.trivadis.tvdcc.CodeChecker.doCodeCheck(CodeChecker.java:155)
	at com.trivadis.tvdcc.CodeChecker.mainFunction(CodeChecker.java:67)
	at com.trivadis.tvdcc.CodeChecker.main(CodeChecker.java:46)
Caused by: java.util.concurrent.ExecutionException: java.lang.StringIndexOutOfBoundsException: index 0, length 0
	at java.base/java.util.concurrent.FutureTask.report(FutureTask.java:122)
	at java.base/java.util.concurrent.FutureTask.get(FutureTask.java:191)
	at com.trivadis.tvdcc.process.TimingOutResourceValidator.validate(TimingOutResourceValidator.java:41)
	... 6 more
Caused by: java.lang.StringIndexOutOfBoundsException: index 0, length 0
	at java.base/java.lang.String.checkIndex(String.java:4565)
	at java.base/java.lang.AbstractStringBuilder.charAt(AbstractStringBuilder.java:351)
	at java.base/java.lang.StringBuilder.charAt(StringBuilder.java:91)
	at com.trivadis.oracle.sqlplus.SqlPlusResource.isMarkdown(SqlPlusResource.java:77)
	at com.trivadis.oracle.sqlplus.SqlPlusResource.doLoad(SqlPlusResource.java:123)
	at org.eclipse.emf.ecore.resource.impl.ResourceImpl.load(ResourceImpl.java:1563)
	at org.eclipse.emf.ecore.resource.impl.ResourceImpl.load(ResourceImpl.java:1342)
	at com.trivadis.tvdcc.process.ResourceHandler$1.load(ResourceHandler.java:21)
	at com.trivadis.tvdcc.process.ResourceValidatorImpl.performValidation(ResourceValidatorImpl.java:49)
	at com.trivadis.tvdcc.process.ResourceValidatorImpl.validate(ResourceValidatorImpl.java:40)
	at com.trivadis.tvdcc.process.TimingOutResourceValidator.lambda$validate$0(TimingOutResourceValidator.java:34)
	at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136)
	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635)
	at java.base/java.lang.Thread.run(Thread.java:840)

processing file 'null'... error with db*CODECOP 4.0.4 command-line

Hi,
I installed db*CODECOP 4.0.4 on Windows 10, but I'm always getting the following error when trying to analyse a file:
processing file 'null'...

Here's the full output:
Parameters:

path: C:/dbCODECOPv4/tvdcc-4.0.4/example
filter: (sql|prc|fnc|pks|pkb|trg|vw|tps|tpb|tbp|plb|pls|rcv|spc|typ|aqt|aqp|ctx|dbl|tab|dim|snp|con|collt|seq|syn|grt|sp|spb|sps|pck)$
timeout: 10
complexity: 10
output: ./tvdcc_report.html
template: html.xsl
excel: true
html: true
transonly: false
cleanup: true
check:
skip:
nosonar: true
license:
validator: com.trivadis.tvdcc.validators.TrivadisGuidelines3
genmodel: false

processing file 'null'...
Summary:

Total files: 0
Total bytes: 0
Total lines: 0
Total comment lines: 0
Total blank lines: 0
Total net lines: 0
Total commands: 0
Total statements (PL/SQL): 0
Max. cyclomatic complexity: 0
Max. Halstead volume: 0.0
Min. maintainability index (MI): 221.0
Total issues: 0
Total warnings: 0
Total errors: 0
Total processing time in seconds: 0.0

Any idea what could be wrong? I changed the following in tvdcc.cmd:
set TVDCC_HOME=C:\dbCODECOPv4\tvdcc-4.0.4\
set JAVA_HOME=C:\Program Files\Java\jdk-12.0.1
I also tried with jdk1.8.0_211, but the same issue occurs. I really don't see what I'm doing wrong?

Thanks,
Matthias

propertyfile argument not working in Win10

My test folder structure looks like

C:\temp\tvdcc-2.3.1*.*
C:\temp\files*.pks

I've copied validators.jar (v1.5.0) into C:\temp\tvdcc-2.3.1\plugin and copied the default properties file content to C:\temp\tvdcc-2.3.1\plugin\TrivadisPlsqlNaming.properties.

When I run
C:\temp\tvdcc-2.3.1\tvdcc.cmd path=C:\temp\files validator=com.trivadis.tvdcc.validators.TrivadisGuidelines3Plus
everything works fine.
But if I add the propertyfile argument like
C:\temp\tvdcc-2.3.1\tvdcc.cmd path=C:\temp\files validator=com.trivadis.tvdcc.validators.TrivadisGuidelines3Plus propertyfile=C:\temp\tvdcc-2.3.1\plugin\TrivadisPlsqlNaming.properties
I get the following error:
Unknown option "PREFIX_RECORD_TYPE_NAME". Cannot set value "r_". Use -? to list valid options.

What is wrong with the command???

Default filter considers partial matching file extensions

The default filter in the CLI is

(sql|prc|fnc|pks|pkb|trg|vw|tps|tpb|tbp|plb|pls|rcv|spc|typ|aqt|aqp|ctx|dbl|tab|dim|snp|con|collt|seq|syn|grt|sp|spb|sps|pck|md|mdown)$

This is a regular expression. As a result, files ending on dmd are also analyzed, because md is considered without a leading ..

The default regular expression should be changed to include the period (.). For example:

\.(sql|prc|fnc|pks|pkb|trg|vw|tps|tpb|tbp|plb|pls|rcv|spc|typ|aqt|aqp|ctx|dbl|tab|dim|snp|con|collt|seq|syn|grt|sp|spb|sps|pck|md|mdown)$

Commercial use license question

Hello,

I'm a little confused about licensing.

The trial/preview version provides full functionality but is limited in time and volume.
For production use a separate software license agreement is required.

I'd like to use this in a commercial setting by integrating it into some of my GitHub Workflows. What sort of license do I need in order to do this? I've tried https://www.trivadis.com/en/plsql-cop but that URL seems to no longer be valid.

File Extensions

Hi,

is it possible to analyze files with other extensions than the defaults, ie. pkg ?
I tried using the filter argument any which way as shown in the help output (filter="(pks|pkb|pkg)$"), but pkg file are being ignored.
FYI, pkg files are analyzed when renamed to any default extension.

Do not report G-7440 when `in out` is used for `self` as part of a fluent API

Type OBJECT syntax requires use of IN OUT, (I think) in this case syntax should not be flagged with
G-7440: Never use OUT parameters to return values from a function.

create or replace TYPE shape as object
(
name varchar2(30 char)
, area number

, constructor function shape(self in out nocopy shape
, in_name varchar2
, in_area Number) return self as result

, member function Updatee(self in out nocopy shape
, in_name in varchar2) return boolean
);
/
create or replace type body shape
as
constructor function shape (in_name varchar2
, in_area number) return self as result is
begin
self.name := in_name;
self.area := in_area;
return;
end;

member function Updatee(self in out nocopy shape
, in_name in varchar2) return boolean
is
begin
self.name := in_name;
return true;
end;
end;

Parse error when using PL/SQL mod operator

Example:

declare
   l_var integer;
begin
   l_var := 11 mod 2;
end;
/

Parse error using self instead of table name in %type declaration

In the body of an object type you can have code similar to the following:

declare
   l_sum self.column_name%type;
begin
   null;
end;
/

self causes a parse error since it is a keyword. The workaround is to use "SELF".

False positive for G-6020 when dynamic SQL is not a INSERT, UPDATE or DELETE statement

A violation of G-6020 is reported in the following case:

declare
   co_sql constant user_tab_comments.comment%type := 'begin :ret := 100; end;';
   l_ret  integer;
begin
   execute immediate co_sql using out l_ret; -- G-6020 false positive
   sys.dbms_output.put_line(l_ret);
end;
/

The returning clause cannot be used in this case.

To reduce false positives it could be an option to scan the statement for insert, update, and delete. Only if one of these words is found a violation should be thrown. In this case, a returning clause should be applicable. However, even then false positives are possible, e.g. when a dynamic PL/SQL block contains an insert statement, but it is less likely. A side effect of this approach is, that there will be false negatives, for example when the statement to be executed cannot be evaluated. Trying to find out if the statement contains such a keyword can be costly and should be done only when an out parameter is defined.

In any case, this is a limitation and should be documented.

False positive for G-3183 when using table alias or table

The following query reports a G-3183. In this case it would be possible to omit the dept reference.

select dept.dname, sum(emp.sal) as sal
  from dept
  join emp
    on emp.deptno = dept.deptno
 group by dept.dname;

However, in this case it's mandatory to use the table alias for deptno.

select d.deptno, d.dname, sum(e.sal) as sal
  from dept d
  join emp e
    on e.deptno = d.deptno
 group by d.deptno, d.dname;

So a qualified name should be allowed in the group by clause and not cause a G-3183.

Parse error in json_object with pretty, ascii options

Example:

SELECT JSON_OBJECT('a' value 42 returning varchar2(30) pretty);

SELECT JSON_OBJECT('a' value 42 returning varchar2(30) ascii);

SELECT JSON_OBJECT('a' value 42 returning varchar2(30) pretty ascii);

SELECT JSON_OBJECT('a' value 42 returning varchar2(30) ascii pretty);

Support New Features/Changes in SQL*Plus Grammar 23c

The currently supported SQL*Plus grammar is 21c. See supported versions

We should add support for 23c.

Reference

See https://docs.oracle.com/en/database/oracle/oracle-database/23/sqpug/changes-this-release-sqpug.html#GUID-27199FF0-F72D-4817-BB54-55A44BCA0866

We use a shallow parser for SQL*Plus and SQLcl. As a result, we usually need to adapt the grammar only for new commands.

New Features

New command argument
New command ping
New command oerr
New command set errordetails

Missing </blockquote> in SQL Script of G-3330: Avoid autonomous transaction

The rule in SonarQube 10 looks like this:

The complete description is shown as blockquote. The quote should be only for the first two paragraphs in "Reason".

The culprit is guideline_3330_na.sql that is used as source for the transformation to the SonarQube rule. It does not contain a closing blockquote tag.

-- G-3330: Avoid autonomous transactions.

-- Reason
/*<blockquote><p>Before we take a look at how autonomous transactions work, 
I’d like to emphasize that this type of transaction is a powerful and therefore
dangerous tool when used improperly. The true need for an autonomous transaction is very
rare indeed. I would be very suspicious of any code that makes use of
them — that code would get extra examination.
It is far too easy to accidentally introduce logical
data integrity issues into a system using them. (page 300)</p>

<p>In my experience, that is the only truly valid use of an autonomous transaction - 
to log errors or informational messages in a manner that can be committed independently
of the parent transaction. (page 305)</p>

<footer>- Kyte, Thomas (2013), <cite>_Expert Oracle Database Architecture. Third Edition_. Apress.</cite></footer>

<p>It is most likely not possible to distinguish legitimate uses of autonomous transactions from
illegitimate ones via static code analysis. However, since we expect exactly one autonomous
transaction per application, the number of false positives is manageable.</p>*/

-- Bad
create or replace package body dept_api is
   procedure ins_dept(in_dept_row in dept%rowtype) is
      pragma autonomous_transaction;
   begin
      insert into dept
      values in_dept_row;
      commit; -- required by autonomous transaction
   end ins_dept;
end dept_api;
/

-- Good
create or replace package body dept_api is
   procedure ins_dept(in_dept_row in dept%rowtype) is
   begin
      insert into dept
      values in_dept_row;
      -- transaction is commited in calling module
      -- after the completion of the unit of work
   end ins_dept;
end dept_api;
/

Support New Features/Changes in PL/SQL Grammar 23c

The currently supported PL/SQL grammar is 21c. See supported versions

We should add support for 23c.

Reference

See

New Features in PL/SQL Language Reference

Multiple G-7430 emitted when parent function has nested functions.

Seems as if zero (0) G-7430 should be emitted, not three (3) of them.

14 10 2-Major Maintainability, Testability G-7430: Try to use no more than one RETURN statement within a function.
26 10 2-Major Maintainability, Testability G-7430: Try to use no more than one RETURN statement within a function.
30 4 2-Major Maintainability, Testability G-7430: Try to use no more than one RETURN statement within a function.

create or replace function fn_test_rule (
b_switch_in in boolean
) return varchar2
is
function f_check_positive
return varchar2
is
v_string1 varchar2(8 char);
begin
if b_switch_in
then
v_string1 := 'TRUE';
end if;
return v_string1;
end;
-- --------------------------------
function f_check_negative
return varchar2
is
v_string2 varchar2(8 char);
begin
if NOT b_switch_in
then
v_string2 := 'FALSE';
end if;
return v_string2;
end;

begin -- Parent
return f_check_positive() || f_check_negative();
end fn_test_rule;

Rationale for exempting INTEGER from G-2110?

I was fiddling around with the command-line version of PL/SQL Cop and some older code, and surprised to not see G-2110 raised for several variables. After further experimentation I worked out that it just doesn't seem to apply to variables declared as INTEGER and I was wondering why. No obvious answer is coming to mind or presenting itself through Google searches on what might be "special" about Oracle's integer type.

Invalid JSON format produced in tvdcc_report.json when message contains double quotes

When running the CLI using the Hint validator for a SQL file with the following content:

select /*+ fast */ /*+ leading(emp dep) */ *
  from emp e
  join dept d on d.deptno = e.deptno;
select * from dbms_xplan.display_cursor(format => 'basic +hint_report');

when get the following JSON output (excerpt):

    {
      "engineId": "db* CODECOP",
      "ruleId": "G-9601",
      "severity": "CRITICAL",
      "type": "CODE_SMELL",
      "primaryLocation": {
        "message": "G-9601: Never use unknown hints. "fast" is unknown.",
        "filePath": "src/demo/demo9600.sql",
        "textRange": {
          "startLine": 7,
          "endLine": 7,
          "startColumn": 11,
          "endColumn": 15
        }
      },
      "effortMinutes": 5
    },

You see that "fast" should be escaped to \"fast\" to make the JSON document valid.

Even if this bug is caused by the Hint validator the code producing the JSON file is responsible to produce a valid JSON format. So it's a bug of the CLI.

trivadis / plsql-cop-cli Goto Github PK

plsql-cop-cli's People

Contributors

Stargazers

Watchers

plsql-cop-cli's Issues

Reference

New Features

Reference

New Features in SQL Language Reference 23c

New SQL Features not mentioned in SQL Language Reference 23c

Reference

New Features

Reference

New Features in PL/SQL Language Reference

Recommend Projects

Recommend Topics

Recommend Org