Parse Error PL/SQL

SonarQube
1
  • Version 7.9.2 (build 30863)
  • Parse Error on fully working create trigger statements

We get parse error on create trigger statements. Those are however fully working. We use RedGate to generate the scripts and deploy them as are. Oracle doesn’t claim a parse error and those are running just fine. Only SonarQube is complaining. Anyone has any idea?

sonar bug
sonar bug998×653 25.6 KB

3

Hi @roman.gerteis,

Welcome to the community! :smiley:

The parse error issue that SonarQube creates is at the file level, so the parse error is somewhere in the file, not necessarily on the CREATE TRIGGER statement itself (I suspect the red underline is slightly misleading here). If you dig into the scanner logs, you should see a more precise indication of where the parser is tripping up. Feel free to post the log snippet when you find it.

Cheers,

Cameron.

4

Hi Cameron,

I went through the logs,but nothing posted there:
ce.log, sonar.log, web.log, se.log

any idea what I’m looking for? Would it post as parse error? Or would I find specific file names?

hmm. stranded. :frowning:

thanks
roman

5

Hi @roman.gerteis, I reproduced your issue with this code:

create or replace trigger x
before insert on y
referencing
for each row
begin
null;
end;

This code compiles correctly on the database, but SonarPLSQL can’t parse it:

com.sonar.sslr.api.RecognitionException: Parse error at line 4 column 1:

1: create or replace trigger x
2: before insert on y
3: referencing
4: for each row
   ^

Changing referencing to referencing old as old fixes the parsing.

create or replace trigger x
before insert on y
referencing old as old
for each row

This situation seems to be an example of difference between the CREATE TRIGGER documentation and the Oracle compiler. The documentation shows that the referencing_clause must contain the “old as x”/“new as x”/“parent as x” after the REFERENCING keyword. So according to it your code is invalid and it should generate a compilation error.

image

But the Oracle compiler accepts the code anyway and compiles it. IMO the SonarPLSQL parser should be changed to follow the behavior of the Oracle compiler, considering the “old as x”/(…) optional.

1 Like
6

Hi Felipe,

this makes total sense. Thanks for finding out about the root cause.
What we will do for now is to update trigger statements so these will pass. We only have a dozen and a half.

How can I motivate SonarSource to update the rules to make that portion optional?

regards
roman

7

Hi @roman.gerteis, @felipebz,

Thanks both for your detailed investigation into this to discover that the Oracle compiler is a bit more permissive than the language standard. Let me ping our team internally and have them take a look at this.

Regards,

Cameron.

11

HI @roman.gerteis, @felipebz,

Our team has created a ticket to change our parsing to follow the Oracle compiler rather than the language standard. You can find/follow it here at SONARPLSQL-740. It’s likely to be fixed in the next release of the PL/SQL analyzer, although we don’t have a timeline yet for that release.

Thanks again for your investigations!

Regards,

Cameron.

13

Hi Cameron,

that’s really amazing. Thank you.
Will the fix be ported to V7.9 LTS version? Or only in V8.x?

thanks again.
roman

14

Hi @roman.gerteis,

Apologies for the late reply; I was on holiday for a couple of weeks. Happy to help :slight_smile:

We don’t generally backport functional changes like this, so the new behaviour will be in an upcoming release, not 7.9 LTS.

Regards,

Cameron.