CeDeT: OIF (Oracle is funny (that way)) #1

CASE not recognized by PL/SQL

CASE expressions are not recognized by PL/SQL. This is demonstrated below using SQL*Plus:

The script

set echo on;
spool CASE_not_recognized_by_PLSQL.lst;
select * from v$version where rownum<=2;
select to_char(sysdate,'yyyy.mm.dd hh24:mi:ss') "now" from dual;
select (case when to_char(sysdate,'HH24') between '00' and '04' then 'It''s late at night'
             else 'Don''t know - but it''s not late at night'
        end) "What time is it?"
 from  dual;
set serveroutput on size 2000
declare -- start anonymous PL/SQL block
  cursor c is
    select (case when to_char(sysdate,'HH24') between '00' and '04' then 'It''s late at night'
                 else 'Don''t know - but it''s not late at night'
            end) "What time is it?"
     from  dual;
  s varchar2(255);
begin
  open c;
  fetch c into s;
  dbms_output.put_line('What time is it?');
  dbms_output.put_line(rpad('-',length(s),'-'));
  dbms_output.put_line(s);
  close c;
end;
/
spool off;
  

The output

SQL> select * from v$version where rownum<=2;

BANNER                                                                          
----------------------------------------------------------------                
Oracle8i Enterprise Edition Release 8.1.7.2.0 - Production                      
PL/SQL Release 8.1.7.2.0 - Production                                           

SQL> select to_char(sysdate,'yyyy.mm.dd hh24:mi:ss') "now" from dual;

now                                                                             
-------------------                                                             
2002.01.27 21:42:03                                                             

SQL> select (case when to_char(sysdate,'HH24') between '00' and '04' then 'It''s late at night'
  2  		  else 'Don''t know - but it''s not late at night'
  3  	     end) "What time is it?"
  4   from  dual;

What time is it?                                                                
---------------------------------------                                         
Don't know - but it's not late at night                                         

SQL> set serveroutput on size 2000
SQL> declare -- start anonymous PL/SQL block
  2    cursor c is
  3  	 select (case when to_char(sysdate,'HH24') between '00' and '04' then 'It''s late at night'
  4  		      else 'Don''t know - but it''s not late at night'
  5  		 end) "What time is it?"
  6  	  from	dual;
  7    s varchar2(255);
  8  begin
  9    open c;
 10    fetch c into s;
 11    dbms_output.put_line('What time is it?');
 12    dbms_output.put_line(rpad('-',length(s),'-'));
 13    dbms_output.put_line(s);
 14    close c;
 15  end;
 16  /
    select (case when to_char(sysdate,'HH24') between '00' and '04' then 'It''s late at night'
            *
FEJL i linie 3:
ORA-06550: line 3, column 13: 
PLS-00103: Encountered the symbol "CASE" when expecting one of the following: 
( - + mod not null others <an identifier>
<a double-quoted delimited-identifier> <a bind variable> avg 
count current exists max min prior sql stddev sum variance 
execute forall time timestamp interval date 
<a string literal with character set specification> 
<a number> <a single-quoted SQL string> 

SQL> spool off;