CeDeT: OIF (Oracle is funny (that way)) #1
CASE expressions are not recognized by PL/SQL. This is demonstrated below using SQL*Plus:
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;
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;