Mega Code Archive

 

Categories / Oracle PLSQL Tutorial / PL SQL Data Types

 

In PLSQL the Boolean expression NULL=NULL evaluates to FALSE

You need to wrap the selector in an NVL expression to be sure that it could never be NULL: SQL> create or replace function f_getDateType (in_dt DATE)   2  return VARCHAR2   3  is   4      v_out 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:='<NULL>';  10          when 1 then  11              v_out:='SUNDAY';  12          when 7 then  13              v_out:='SATURDAY';  14          else  15              v_out:='WEEKDAY';  16      end case;  17      return v_out;  18  end;  19  / Function created. SQL>