“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:
- What you Ought to Know About CASE in Oracle PL/SQL
- LOBs Gotcha in ColdFusion
- CASE used in CREATE INDEX
- “Between” CASE and DECODE
- Found or Not Found, That is The Question. Do You Have The Answer?
Tagged 8i, case, expression, pl/sql, sql | Post a Comment
Hope everything is working out...
(native) dynamic sql - you can use that to work around this.
October 20th, 2005, at 11:27 am #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
October 20th, 2005, at 11:27 am #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.October 20th, 2005, at 11:46 am #“but looks like Tom’s “transaction†was “committed†before Doug’s”
Surprise, surprise … how could anyone keep up!
October 20th, 2005, at 3:48 pm #What version did 8i start with? 8.1.5? I’m new to the Oracle world, and the version numbers confuse me.
October 21st, 2005, at 9:14 am #Here is the Oracle DB version history:
source
October 21st, 2005, at 9:46 am #Ahhh.. wikipedia… didn’t think of that.
October 21st, 2005, at 11:58 am #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
January 21st, 2006, at 6:39 am #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.
January 22nd, 2006, at 6:33 pm #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,
January 23rd, 2006, at 1:42 pm #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
January 23rd, 2006, at 3:44 pm #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”
May 30th, 2006, at 4:18 pm #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.
June 2nd, 2006, at 9:34 pm #Excuse me, i don’t understand…
March 31st, 2007, at 12:11 am #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,
March 10th, 2008, at 4:14 am #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;March 10th, 2008, at 9:49 am #