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.
Related articles:
- SQL PLUS Gotcha
- What you Ought to Know About CASE in Oracle PL/SQL
- When The Combination of an Oracle Sequence and a PLS_INTEGER is Deadly
(native) dynamic sql – you can use that to work around this.
Good point Eddie. I was well aware of that (it was very frustrating until Oracle sorted out) but thought that it was clear enough that the focus in the paper was on SQL.
I’ll think about mentioning it, though.
Cheers,
Doug
Wow! what a coincidence, both Tom and Doug submitted their comments at the same time, but looks like Tom’s “transaction” was “committed” before Doug’s
Thanks Tom for the trick of using dynamic SQL to work around using CASE in PL/SQL prior to 9.0.1. Here is a quick example:
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> declare 2 l_var number; 3 sql_string varchar2(100); 4 begin 5 sql_string := 'select case when 1=1 then 1 else 2 end ' || 6 'from dual'; 7 execute immediate sql_string into l_var; 8 dbms_output.put_line('l_var='||to_char(l_var)); 9 end; 10 / l_var=1 PL/SQL procedure successfully completed.“but looks like Tom’s “transaction†was “committed†before Doug’s”
Surprise, surprise … how could anyone keep up!
What version did 8i start with? 8.1.5? I’m new to the Oracle world, and the version numbers confuse me.
Here is the Oracle DB version history:
source
Ahhh.. wikipedia… didn’t think of that.
Hi! I’m using Oracle’s version 8.1.7. There’s a cursor query in a PL/SQL package like the following one:
select “fields”, 1 qry_condition from table where to_char(Adate, ‘YYYYMMDD’) = to_char(I_date, ‘YYYYMMDD’) and “more_conditions” union all select “fields”, 2 qry_condition from table where to_char(Adate, ‘YYYYMM’) = to_char(I_date, ‘YYYYMM’) and “more_conditions” union all select “fields”, 3 qry_condition from table where to_char(Adate, ‘YYYY’) = to_char(I_date, ‘YYYY’) and “more_conditions” order by “2″ — qry_condition;
The idea is to execute (only) the first query if results are returned, execute the next query if the first doesn’t returned rows, and execute the third query if none of the previous two return data. But as we can conclude, everytime this cursor is executed it runs all of the three queries that belong to it, which is impacting negatively the cursor perfomance.
One possible solution would be to divide it in three querys and use, something like this:
open cursor1(L_date); fetch cursor1 into … if cursor1.notfound then open cursor2(L_date); fetch cursor2 into … if cursor2.notfound then open cursor3(L_date); fetch cursor3 into … if cursor3.notfound then (…)
end if;
(…)
But personally I dont like this solution. I was thinking in use the CASE keyword to help me solving it, continuing to have only a single cursor. Something like this (I don’t know if this is possible):
select “fields”, 1 qry_condition from table where ( CASE when to_char(Adate, ‘YYYYMMDD’) = to_char(I_date, ‘YYYYMMDD’) and “more_conditions” then 1 —- when to_char(Adate, ‘YYYYMM’) = to_char(I_date, ‘YYYYMM’) and “more_conditions” then 2 —- when to_char(Adate, ‘YYYY’) = to_char(I_date, ‘YYYY’) and “more_conditions” then 3 end IN (1, 2, 3) ;
I know that this Oracle version of the PL/SQL doesn’t support the CASE, so I was thinking in using dynamic Sql to do it. What’s your opinion, does the CASE solves my request or do you have a better solution?
Thanks in advance
SASantos
SASantos, it looks like you are selecting the same fields from the same table in all three queries, So, why don’t you use one query like this:
select “fields” from table where (to_char(Adate, ‘YYYYMMDD’) = to_char(I_date, ‘YYYYMMDD’) and “more_conditions”) OR (to_char(Adate, ‘YYYYMM’) = to_char(I_date, ‘YYYYMM’) and “more_conditions”) OR (to_char(Adate, ‘YYYY’) = to_char(I_date, ‘YYYY’) and “more_conditions”)
Moreover, if Adate and I_date are of a date datatype, why converting to to_char? you can just use TRUNC.
Eddie, the ideal would be to only run the first OR if there weren’t any rows returned for the “first conditions”: (to_char(Adate, ‘YYYYMMDD’) = to_char(I_date, ‘YYYYMMDD’) and “more_conditions”)
And run the second OR if the: (to_char(Adate, ‘YYYYMM’) = to_char(I_date, ‘YYYYMM’) and “more_conditions”) Didn´t returned rows.
But with the OR’s or with the UNION’s the query is to “heavy”. Thats why the ideal would be run the query (based on three small queries) from the start to end, and “stop” at the first that returned rows. I don’t know if thats possible. Besides the optional solution I refered before.
TIA, SaSantos
SaSantos, what do you mean by “heavy”? Have you measured the performance of your query and found it to be slow? Have you run your query through an explain plan? a tkprof?
I suggest you post your question on forums.oracle.com. I also suggest you read:
http://oraclesponge.blogspot.com/2005/04/writing-good-sql.html
so how would you code something that returns a cursor nested in a table based on a case statment in 9i?
select *, (case when tb1.item=100 then cursor(select * from tb2 where id=5 from mytable2 tb2) when tb2.item=200 then cursor(select * from tb3 where id=6 from mytable3 tb3) else null end ) curval from mytable1
or would you have to used “decode”
Steve, Your SQL syntax does not look correct. However, I tried the following in 10gXE:
SELECT (CASE WHEN 1 = 1 THEN CURSOR (SELECT 1 FROM DUAL) WHEN 1 = 2 THEN CURSOR (SELECT 1 FROM DUAL) ELSE NULL END ) x FROM DUAL /It returned an error: ORA-22902: CURSOR expression not allowed Cause: CURSOR on a subquery is allowed only in the top-level SELECT list of a query.
That means you cannot use cursor expressions in a CASE or even DECODE.
Another solution may be the use of Dynamic SQL.
Excuse me, i don’t understand… How i can you DECODE functions?
apps@dev1> declare 2 l_var number; 3 sql_string varchar2(100); 4 begin 5 sql_string := ‘select case when 1=1 then 1 else 2 end ‘ || 6 ‘from dual’; 7 execute immediate sql_string into l_var; 8 dbms_output.put_line(‘l_var=’||to_char(l_var)); 9 end; 10 / l_var=1
The above is applicable, when the sql returns only one value (1 row, 1 column)
How do I implement the same using curser, ie, if the sql statement returns a tabular data (M rows, N columns:: where M & N >2)?
@santhosh: You can try something like the following, or use dbms_sql.
DECLARE l_my_cursor sys_refcursor; l_stmt_str VARCHAR2(32767); l_var NUMBER; BEGIN l_stmt_str := 'SELECT CASE WHEN 1=1 THEN 1 ELSE 2 END FROM dual UNION SELECT CASE WHEN 1=2 THEN 1 ELSE 2 END FROM dual'; OPEN l_my_cursor FOR l_stmt_str; LOOP FETCH l_my_cursor INTO l_var; EXIT WHEN l_my_cursor%NOTFOUND; dbms_output.put_line('l_var='||to_char(l_var)); END LOOP; CLOSE l_my_cursor; END;