Oracle PL/SQL - Using NVL in CASE statement

  1. HOME
  2. Oracle PL/SQL
  3. PL SQL Statement
  4. Simple CASE Expression

Introduction

When creating selector CASE statements, you cannot have NULL in the list of possible values.

In PL/SQL the Boolean expression NULL=NULL evaluates to FALSE.

To fix it, wrap the selector in an NVL expression to be sure that it could never be NULL:

Demo

SQL>
SQL> create or replace function f_getDateType_tx (in_dt DATE)
  2  return VARCHAR2-- from  w  w w .j a v  a  2s.  com
  3  is
  4       v_out_tx VARCHAR2(10);
  5  begin
  6        case nvl(to_char(in_dt,'d') , 0)
  7            when 0 then
  8                  -- value will be null if in_dt is null
  9                  v_out_tx:='<NULL>';
 10            when 1 then
 11                  v_out_tx:='SUNDAY';
 12            when 7 then
 13                  v_out_tx:='SATURDAY';
 14              else
 15                  v_out_tx:='WEEKDAY';
 16         end case;
 17         return v_out_tx;
 18  end;
 19  /

Function created.

SQL>
SQL> begin
  2     DBMS_OUTPUT.put_line(f_getDateType_tx(SYSDATE));
  3  end;
  4  /
SATURDAY

PL/SQL procedure successfully completed.

SQL>
SQL>

Related Topic