CASE gotcha in Oracle 8i

“CASE works with all modern versions…”, that’s what Doug Burns wrote in his excellent article about CASE expressions. I believe that a clarification is needed here, especially for those of you who still deal with Oracle 8i. In Oracle 8i CASE statements and expressions are supported only in SQL and not in PL/SQL. In 8i you cannot use CASE in PL/SQL. Starting with Oracle database release 9.0.1, the SQL and PL/SQL parsers were integrated and, as a result, in version 9.0.1 and above CASE works in both SQL and PL/SQL. Here is an example:

In 8.1.7:

Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 - Production

apps@dev1> select case when 1=1 then 1 else 2 end from dual
  2  /

CASEWHEN1=1THEN1ELSE2END
------------------------
                       1

apps@dev1> declare
  2      l_var number;
  3  begin
  4      select case when 1=1 then 1 else 2 end
  5      into l_var
  6      from dual;
  7      dbms_output.put_line('l_var='||to_char(l_var));
  8  end;
  9  /
    select case when 1=1 then 1 else 2 end
           *
ERROR at line 4:
ORA-06550: line 4, column 12:
PLS-00103: Encountered the symbol "CASE" 
when expecting one of the following:
( * - + all mod null <an identifier>
<a double-quoted delimited-identifier> <a bind variable>
table avg count current distinct max min prior sql stddev sum
unique variance execute the forall time timestamp interval
date <a string literal with character set specification>
<a number> <a single-quoted SQL string>

In 9.2.0:

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

scott@EDDEV> select case when 1=1 then 1 else 2 end from dual
  2  /

CASEWHEN1=1THEN1ELSE2END
------------------------
                       1

scott@EDDEV> declare
  2      l_var number;
  3  begin
  4      select case when 1=1 then 1 else 2 end
  5      into l_var
  6      from dual;
  7      dbms_output.put_line('l_var='||to_char(l_var));
  8  end;
  9  /
l_var=1

PL/SQL procedure successfully completed.

So, if you are one of the few left who use Oracle 8i, this is one of the gotchas that got me one time.


Possibly related:


Tagged , , , , | Post a Comment