Oracle 8i : Using CASE in PL/SQL on Oracle 8i

CASE statements do work on Oracle 8.1.7, but not in pl/sql.

Let us see a work around to make them work in pl/sql.

Let's see an example :

Connected to Oracle8i Enterprise Edition Release 8.1.7.4.0
Connected as idc_sage

SQL> select case when 1=1 then 1 else 2 end from dual;
CASEWHEN1=1THEN1ELSE2END
------------------------
1

Let's try the same SQL query in pl/sql :

SQL> declare
2 var number;
3 begin
4 select case when 1=1 then 1 else 2 end
5 into var
6 from dual;
7 dbms_output.put_line('var='||to_char(var));
8 end;
9 /
ORA-06550: line 4, column 16:
PLS-00103: Encountered the symbol "CASE" when expecting one of the following:
( * - + all mod null

table avg count current distinct max min prior sql stddev sum
unique variance execute the forall time timestamp interval
date


So how do we get this working in pl/sql ?
Use
[edit]
"Execute Immediate"
.

Her you go :

Connected to Oracle8i Enterprise Edition Release 8.1.7.4.0
Connected as idc_sage

SQL> set serveroutput on
SQL> declare
2 var number;
3 sql_str varchar2(100);
4 begin
5 sql_str := 'select case when 1=1 then 1 else 2 end from dual';
6 execute immediate sql_str into var;
7 dbms_output.put_line('var='||to_char(var));
8 end;
9 /

var=1

PL/SQL procedure successfully completed
SQL>

[edit]
Voila !!!

ps : If you are working on 9i or above you will not see this issue.