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

Rules and Languages New rules / language support
8

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!

1 Like