Wednesday, February 26, 2025

Enhanced simple CASE statement syntax in Oracle 23ai

Oracle 23ai extends the syntax of the so-called "simple" CASE statement in PL/SQL, that is the version of CASE where an expression appears right after the CASE keyword.
The list of WHEN blocks now allows dangling expressions (in red below), that is conditional expressions where the left operand is missing.
DECLARE
    x number;
BEGIN
    case x
    when is null THEN
      dbms_output.put_line('null');
    when 1 then 
        dbms_output.put_line('1');
        return;
    when > 1 then 
        dbms_output.put_line('greater than 1');
        return;
    else 
        dbms_output.put_line('else');
        return;
    end case;
END;
/

The PL/SQL block above would raise PL/SQL parsing errors in earlier versions (PLS-00103 on lines 5 and 10).

It is worth noting that in earlier versions the only way to catch WHEN x IS NULL, would be to replace NULL with some other value or, better, use the "searched" CASE statement instead.

For more information, please refer to the official Oracle 23ai documentation.

No comments:

yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles