Rule plsql:S131 concerns only PL/SQL code not SQL but hit in the both cases

Hello,

The rule plsql:S131 is perfect for the PL/SQL code. Else statement is mandatory to avoid exception.
But this rule also applies on SQL code (or select in cursor) where the else is optional.
If the case does not cover the tested values then a NULL value is returned in SQL.

So in SQL code, this rule force the developer to put “Else Null;”. This is useless.

Snippet of non compliant code should refer only PL/SQL code like

declare
    l_dummy integer;
    begin
       l_dummy := 0;
       case l_dummy
          when 1 then dbms_output.put_line('One');
          when 2 then dbms_output.put_line('Two');
          else dbms_output.put_line(' Unknown');
       end case;
    end;

Non compliant code because raises ORA-6592-“CASE not found while executing CASE statement” exception.

Snippet of compliant code should refer only PL/SQL code like

declare
l_dummy integer;
begin
   l_dummy := 0;
   case l_dummy
      when 1 then dbms_output.put_line('One');
      when 2 then dbms_output.put_line('Two');
      else dbms_output.put_line(' Unknown');
   end case;
end;

or in Select, else is optional

select case count(1)
          when 1 then
           'one'
          when 2 then
           'two'
       end eval
  from dual
 where 1 = 2;

Above select returns null

Hello @SebG,

The SonarPLSQL analysis is meant to only analyze perfectly the PL/SQL code.
If you have some plain SQL code that can be analyzed, that’s nice (and that’s not unexpected) but you run the risk of such corner cases inaccuracies.
I don’t think we can adjust the rule to cope with plain SQL.
I’d rather suggest that you:

  • Either turn off the rule for the code that you know to be plain SQL (Check out the "restrict scope of coding rules section in Issues exclusions

    This can also be done from the command line or config file
sonar.issue.ignore.multicriteria=e1,e2,...
sonar.issue.ignore.multicriteria.e1.ruleKey=plsql:S131
sonar.issue.ignore.multicriteria.e1.resourceKey=**/*.plain.sql
sonar.issue.ignore.multicriteria.e2.ruleKey=...
sonar.issue.ignore.multicriteria.e2.resourceKey=...
...
  • Or mark those issues on plain SQL files as false positive

:christmas_tree: Merry Christmas :christmas_tree:

Olivier

Hello @OlivierK

In my suggestion I meant the SQL Code could be the select statement of a pl/sql cursor or in PL/SQL you can have SQL code using SELECT … INTO … statements.

I don’t talk about analyze of plain SQL code sources.

I hope this clarify my suggestion.
Regards,
Sebastien

Hi @SebG,

I’m not sure I understand what you mean but I did some research.

I understand that Oracle has 2 different constructs which behave differently: CASE expressions and CASE statements.
Examples:

BEGIN
  grade := 'B';

  -- This is a CASE expression
  appraisal := CASE grade   
         WHEN 'A' THEN 'Excellent'
         WHEN 'B' THEN 'Very Good'
         ELSE 'No such grade'
      END;

  -- This is a CASE statement
  CASE grade   
    WHEN 'A' THEN DBMS_OUTPUT.PUT_LINE('Excellent');
    WHEN 'B' THEN DBMS_OUTPUT.PUT_LINE('Very Good');
    ELSE DBMS_OUTPUT.PUT_LINE('No such grade');
  END CASE;
END;

According to Oracle documentation:

For a CASE statement, the default when none of the conditions matches is to raise a CASE_NOT_FOUND exception. For a CASE expression, the default is to return NULL .

RSPEC-131 is a code smell rule we have for many languages. It’s supposed to target switch statements in imperative languages.

It seems that the current implementation of RSPEC-131 for PL/SQL raises issues on CASE expressions and not on CASE statements. However:

  • I don’t see the point in raising an issue on a CASE expression as it has a default value.
  • It would make sense to raise an issue on a CASE statement without ELSE since it should almost always be a mistake.

I think that I come to the same conclusions as you did, but I’m not sure.
I created SONARPLSQL-757 to track this change.

Thanks a lot for your feedback!

Hi @pynicolas,

My remark concerns the code in SQL language used into PL/SQL code (e.g to construct a cursor or to make a select into).

See my example with comment here:

declare
   cursor c_season is
   -- This select use a case of SQL language without ELSE
      select case to_char(sysdate, 'MON')
                when 'JAN' then
                 'Winter'
                when 'FEB' then
                 'Winter'
                when 'JUL' then
                 'Summer'
             end
        from dual;

   l_season varchar2(15);
begin
   open c_season;
   fetch c_season
      into l_season;
   close c_season;

   -- This is a CASE in PL/SQL language where the ELSE is required
   case l_season
      when 'Winter' then
         dbms_output.put_line(' Winter case is managed by cursor');
      when 'Summer' then
         dbms_output.put_line(' Winter case is managed by cursor');
      else
         dbms_output.put_line('Not managed by cursor: ' || to_char(sysdate, 'MON'));
   end case;

   -- This is another example of CASE in SQL language without the ELSE used into a PL/SQL code
   select case to_char(sysdate, 'MON')
             when 'FEB' then
              'Winter'
             when 'APR' then
              'Spring'
             when 'JUL' then
              'Summer'
          end
     into l_season
     from dual;
   dbms_output.put_line(nvl(l_season,'Not managed by select into'));
end;

You can execute the above pl/sql code without any issue. CASE in SQL language returns a NULL value when the value doesn’t match with evaluated values.

The CASE expression and CASE statements you mentioned are from PL/SQL language. These ones must have a ELSE and the rule is perfect (I did not check the CASE expression, but rule must do the same for the both).

My remark is about SQL language CASE (a third case of CASE :stuck_out_tongue_winking_eye:) that violates the rule because they could be without ELSE.

So in conclusion, we scan PL/SQL code that could contains 3 kind of CASE and the rule must not hit on CASE without ELSE when this CASE is in code in SQL language.

Thanks

Hi @SebG,

First, CASE expressions.
I don’t see why we should make a distinction between CASE expressions in PL/SQL and CASE expressions in SQL. They both default to NULL if they cannot match any WHEN clause.
The current behavior of plsql:S131 is the same for all CASE expressions: the rule currently raises an issue for all CASE expressions which don’t have an ELSE clause and I believe the rule should never raise an issue on a CASE expression. If you think otherwise, can you please give an example of a CASE expression where the rule should raise an issue? In your examples, the 1st and the 3rd CASE are expressions without an ELSE and, as you explain, Oracle executes the code without any error.

Then, CASE statements.
According to my tests, the current implementation of plsql:S131 does not raise issues in CASE statements. If you found a CASE statement where the rule raised an issue, can you please share an example? In the example you gave, the 2nd CASE is a statement. It contains an ELSE clause, but the current implementation of plsql:S131 doesn’t raise an issue on it even I remove the ELSE clause.
I believe that the rule should raise an issue for all CASE statements which don’t have an ELSE.

I tried to clarify SONARPLSQL-757.

Hi @pynicolas

See below a recent case in our code for a CASE exp. in SQL that Sonar hit as an error:

Why we must make a distinction between CASE expressions in PL/SQL and CASE expressions in SQL is because in PL/SQL that raise an exception (it is a development issue) and in SQL that return a NULL value (could be an expected behavior).

See below an example where the ELSE in the CASE exp. in PL/SQL was removed.

declare
   cursor c_season is
   -- This select use a CASE expression in SQL language without ELSE
      select case to_char(sysdate, 'MON')
                when 'FEB' then
                 'Winter'
                when 'JUL' then
                 'Summer'
             end
        from dual;

   l_season varchar2(15);
begin
   open c_season;
   fetch c_season
      into l_season;
   close c_season;

   -- This is a CASE expression in PL/SQL language with ELSE removed. 
   -- As l_season is null because cursor retruned NULL (tested in January), an exception is raised during the execution.
   case l_season
      when 'Winter' then
         dbms_output.put_line(' Winter case is managed by cursor');
      when 'Summer' then
         dbms_output.put_line(' Winter case is managed by cursor');
   end case;
end;

When I execute it I have the following exception raised:

So in above example, you have in the cursor a CASE exp. in SQL without ELSE and we want that to return NULL (Sonar detects it with rule plsql:S131, we consider it as a false positive) and you have a CASE exp. in PL/SQL without ELSE and we don’t want that so we expect Sonar detects it.

In the SQL documentation of the CASE expression in SQL you put in your previous reply, they mention that ELSE is optional:

In a simple CASE expression, Oracle Database searches for the first WHENTHEN pair for which expr is equal to comparison_expr and returns return_expr . If none of the WHENTHEN pairs meet this condition, and an ELSE clause exists, then Oracle returns else_expr . Otherwise, Oracle returns null.

You can read at the end of above sentence:
> Otherwise, Oracle returns null.

Regarding the CASE Statement in PL/SQL, I will have a look and make some tests.

Thanks
Sebastien.

@pynicolas

I did a test with sonarlint in VSCode with the following example:

declare
   cursor c_season is
   -- This select use a CASE expression in SQL language without ELSE
      select case to_char(sysdate, 'MON')
                when 'FEB' then
                 'Winter'
                when 'JUL' then
                 'Summer'
             end
        from dual;

   l_season varchar2(15);
   l_result varchar2(15);
begin
   open c_season;
   fetch c_season
      into l_season;
   close c_season;

   -- This is a CASE expression in PL/SQL language without ELSE . 
   -- As l_season is null because cursor retruned NULL (tested in January), an exception is raised during the execution.
   case l_season
      when 'Winter' then
         dbms_output.put_line(' Winter case is managed by cursor');--NOSONAR for dbms_output
      when 'Summer' then
         dbms_output.put_line(' Winter case is managed by cursor');--NOSONAR for dbms_output
   end case;

   -- This is a CASE statement in PL/SQL language without ELSE . 
   l_result := case l_season
      when 'Winter' then
         ' Winter case is managed by cursor'
      when 'Summer' then
         ' Winter case is managed by cursor'
   end case;
end;

and Sonar detects only the issue in CASE expression in SQL !!

Any idea ?
Thanks
Sebastien.

As I wrote, the current implementation of plsql:S131 only considers CASE expressions, not CASE statements. I believe it should do the opposite.
That’s what I described in SONARPLSQL-757 and I believe that all your examples confirm that.