Oracle CASE – PL/SQL vs. SQL #JoelKallmanDay

CASE without ELSE at SQL and PL/SQL

Unfortunately, I never met Joel Kallman in person, so I can’t share any memories with you.
But I would like to use the #JoelKallmanDay for my first little blog entry.
Thank you Joel! This day in your honor has given me the push to finally fill this blog with life.
And thank you for your great contribution to the community over the years with sharing your knowledge and experiences.

Oracle SQL CASE without ELSE

select case trunc(sysdate)
           when to_date('2022/10/11', 'YYYY/MM/DD') 
           then '#JoelKallmanDay'
       end as hashtag
  from dual;

There is no problem here, on October 11, 2022 the CASE condition applies and we get #JoelKallmanDay as a result.
On any other day we get back NULL.

Oracle PL/SQL Case without ELSE

Anonymous Block

declare
  l_hashtag varchar2(100);
  l_date    date;  
begin
    l_date := trunc(sysdate);
    --
    case l_date 
        when to_date('2022/10/11', 'YYYY/MM/DD') 
        then l_hashtag := '#JoelKallmanDay';
    end case;
    --
    dbms_output.put_line(l_hashtag);
end;
/

When executing this anonymous block, #JoelKallmanDay is displayed as DBMS_OUTPUT on October 11, 2022.
On any other day, however, the Oracle error “ORA-06592: CASE not found while executing CASE statement” occurs.
So here Oracle expects either an ELSE condition or alternatively that there is logic for all cases.

With a CASE expression with direct value assignment, we do not need an ELSE branch, analogous to the SQL implementation, our variable is assigned NULL in this case. Short note: “end case” is not allowed as syntax here, “end” is expected as termination, otherwise there will be a compile error.

declare
  l_hashtag varchar2(100);
  l_date    date;  
begin
    l_date := trunc(sysdate);
    --
    l_hashtag := case l_date 
                     when to_date('2022/10/11', 'YYYY/MM/DD') 
                     then '#JoelKallmanDay'
                 end;
    --
    dbms_output.put_line(l_hashtag);
end;
/

Stored Function / Procedure

create function f_case_expression_test (p_date in date) 
       return varchar2 is
   l_hashtag varchar2(100);
begin
    case p_date 
        when to_date('2022/10/11', 'YYYY/MM/DD') 
        then l_hashtag := '#JoelKallmanDay';
    end case;
    --
    return l_hashtag;
end f_case_expression_test;
/

select f_case_expression_test(trunc(sysdate)) from dual;

When compiling a function, for example, it should now be noted that no error occurs here initially.
Only when calling the function and an unhandled case in the CASE expression, the error “ORA-06592: CASE not found while executing CASE statement” occurs.

LiveSQL