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;
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
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
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;
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.
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 ) 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.
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.
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 WHEN … THEN pair for which expr is equal to comparison_expr and returns return_expr . If none of the WHEN … THEN 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.
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 !!
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.
Merry Christmas 
) that violates the rule because they could be without ELSE.
