8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | 24ai | 26ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

CASE Statement and CASE Expression Enhancements in Oracle Database 23ai/26ai

In Oracle database 23ai/26ai the simple CASE statement and expression are more flexible, allowing dangling predicates and multiple choices in a single WHEN clause.

This brings the PL/SQL simple CASE statement and expression in line with the SQL:2003 Standard [ISO03a, ISO03b] standard.

Setup
The Problem
Dangling Predicates
Multiple Choices in a Single WHEN Clause
SQL CASE Expressions
Warning : Stricter Data Type Selection

Setup

The examples in this article use the following table.

drop table if exists t1 purge;

create table t1 (
  pct number
);

insert into t1 (pct)
values (-1), (0), (10), (40), (70), (80), (90), (100), (101);
commit;

The Problem

In previous releases simple CASE statements and expressions were only capable of performing equality checks. If we needed comparisons other than equality checks we would have to use a searched case statement or expression.

In the following example we use a searched CASE statement to evaluate a threshold. We are using a searched CASE statement because most of the comparisons are not simple equality checks.

set serveroutput on
declare
  l_threshold varchar2(20);
begin
  for cur_rec in (select pct from t1) loop
    case 
      when cur_rec.pct  = 40 then l_threshold := 'Optimal';
      when cur_rec.pct <= 70 then l_threshold := 'Safe';
      when cur_rec.pct <= 80 then l_threshold := 'Check';
      when cur_rec.pct <= 90 then l_threshold := 'Warning';
      when cur_rec.pct  > 90 then l_threshold := 'Critical';
    end case;

    dbms_output.put_line(cur_rec.pct || ' : ' || l_threshold);
  end loop;
end;
/
-1 : Safe
0 : Safe
10 : Safe
40 : Optimal
70 : Safe
80 : Check
90 : Warning
100 : Critical
101 : Critical

PL/SQL procedure successfully completed.

SQL>

This example uses a searched CASE expression to do the same thing.

set serveroutput on
declare
  l_threshold varchar2(20);
begin
  for cur_rec in (select pct from t1) loop
    l_threshold := case 
                     when cur_rec.pct  = 40 then 'Optimal'
                     when cur_rec.pct <= 70 then 'Safe'
                     when cur_rec.pct <= 80 then 'Check'
                     when cur_rec.pct <= 90 then 'Warning'
                     when cur_rec.pct  > 90 then 'Critical'
                   end;

    dbms_output.put_line(cur_rec.pct || ' : ' || l_threshold);
  end loop;
end;
/
-1 : Safe
0 : Safe
10 : Safe
40 : Optimal
70 : Safe
80 : Check
90 : Warning
100 : Critical
101 : Critical

PL/SQL procedure successfully completed.

SQL>

Dangling Predicates

In Oracle 23ai/26ai we can do the same thing using a simple CASE statement or expression using dangling predicates. A dangling predicate is an expression with its left operand missing.

This example uses a simple CASE statement with dangling predicates to achieve the same result.

set serveroutput on
declare
  l_threshold varchar2(20);
begin
  for cur_rec in (select pct from t1) loop
    case cur_rec.pct
      when 40    then l_threshold := 'Optimal';
      when <= 70 then l_threshold := 'Safe';
      when <= 80 then l_threshold := 'Check';
      when <= 90 then l_threshold := 'Warning';
      when  > 90 then l_threshold := 'Critical';
    end case;

    dbms_output.put_line(cur_rec.pct || ' : ' || l_threshold);
  end loop;
end;
/
-1 : Safe
0 : Safe
10 : Safe
40 : Optimal
70 : Safe
80 : Check
90 : Warning
100 : Critical
101 : Critical

PL/SQL procedure successfully completed.

SQL>

Here is the simple CASE expression equivalent.

set serveroutput on
declare
  l_threshold varchar2(20);
begin
  for cur_rec in (select pct from t1) loop
    l_threshold := case cur_rec.pct
                     when 40    then 'Optimal'
                     when <= 70 then 'Safe'
                     when <= 80 then 'Check'
                     when <= 90 then 'Warning'
                     when  > 90 then 'Critical'
                   end;

    dbms_output.put_line(cur_rec.pct || ' : ' || l_threshold);
  end loop;
end;
/
-1 : Safe
0 : Safe
10 : Safe
40 : Optimal
70 : Safe
80 : Check
90 : Warning
100 : Critical
101 : Critical

PL/SQL procedure successfully completed.

SQL>

Multiple Choices in a Single WHEN Clause

A single WHEN clause can include multiple equality checks or dangling predicates as a comma-separated list.

In the following example we use a simple CASE statement to show an error if a value is below 0, exactly 0.5 or greater than 100. We also add values 41 and 42 to the optimal threshold.

set serveroutput on
declare
  l_threshold varchar2(20);
begin
  for cur_rec in (select pct from t1) loop
    case cur_rec.pct
      when < 0, 0.5, > 100 then l_threshold := 'Error';
      when 40, 41, 42      then l_threshold := 'Optimal';
      when <= 70           then l_threshold := 'Safe';
      when <= 80           then l_threshold := 'Check';
      when <= 90           then l_threshold := 'Warning';
      when  > 90           then l_threshold := 'Critical';
    end case;

    dbms_output.put_line(cur_rec.pct || ' : ' || l_threshold);
  end loop;
end;
/
-1 : Error
0 : Safe
10 : Safe
40 : Optimal
70 : Safe
80 : Check
90 : Warning
100 : Critical
101 : Error

PL/SQL procedure successfully completed.

SQL>

This is the simple CASE expression equivalent of the previous example.

set serveroutput on
declare
  l_threshold varchar2(20);
begin
  for cur_rec in (select pct from t1) loop
    l_threshold := case cur_rec.pct
                     when < 0, 0.5, > 100 then 'Error'
                     when 40, 41, 42      then 'Optimal'
                     when <= 70           then 'Safe'
                     when <= 80           then 'Check'
                     when <= 90           then 'Warning'
                     when  > 90           then 'Critical'
                   end;

    dbms_output.put_line(cur_rec.pct || ' : ' || l_threshold);
  end loop;
end;
/
-1 : Error
0 : Safe
10 : Safe
40 : Optimal
70 : Safe
80 : Check
90 : Warning
100 : Critical
101 : Error

PL/SQL procedure successfully completed.

SQL>

SQL CASE Expressions

This functionality is not supported directly by SQL CASE expressions.

select pct,
       case pct
         when 0.5, < 0, > 100 then 'Error'
         when 40, 41, 42      then 'Optimal'
         when <= 70           then 'Safe'
         when <= 80           then 'Check'
         when <= 90           then 'Warning'
         when  > 90           then 'Critical'
       end as status
from   t1;

when 0.5, < 0, > 100 then 'Error'
                 *
ERROR at line 3:
ORA-02000: missing THEN keyword

SQL>

We can achieve a similar result by defining a function in the WITH clause to perform the CASE expression.

with
  function get_status(p_pct in number) return varchar2 is
  begin
    return case p_pct
             when < 0, 0.5, > 100 then 'Error'
             when 40, 41, 42      then 'Optimal'
             when <= 70           then 'Safe'
             when <= 80           then 'Check'
             when <= 90           then 'Warning'
             when  > 90           then 'Critical'
           end;
  end;
select pct, get_status(pct) as status
from   t1
/

       PCT STATUS
---------- ----------------------------------------
        -1 Error
         0 Safe
        10 Safe
        40 Optimal
        70 Safe
        80 Check
        90 Warning
       100 Critical
       101 Error

9 rows selected.

SQL>

Warning : Stricter Data Type Selection

If you are not careful about your datatype handling, a move to 23ai/26ai may result in you seeing the following errors when using CASE.

ORA-00932: expression (:1) is of data type CHAR, which is incompatible with expected data type NUMBER

The reason is explained here.

Datatype Discipline – Even more so in 26ai

If you can't easily correct your code, you may need to switch off the feature that causes this at the system or session level.

alter system set "_fix_control" = "33898336:0";

alter session set "_fix_control" = "33898336:0";

For more information see:

Hope this helps. Regards Tim...

Back to the Top.