Home » SQL & PL/SQL » SQL & PL/SQL » Can't use CASE within a cursor in a package??
Can't use CASE within a cursor in a package?? [message #37095] Thu, 17 January 2002 04:54 Go to next message
Jon
Messages: 483
Registered: May 2001
Senior Member
I have the strangest situation. When I try to compile a function inside a package with a cursor in it that uses the CASE construct, the system gives me a compile error. If I cut and paste the query into SQL*PLUS, it runs fine. If I eliminate the CASE statement, the package compiles. This is not the real world case below, but shows what is happening in the smallest space. If anyone can explain this behavior, please do. I would love to hear that I am doing something wrong, and the real world case could have a huge performance benefit. Thanks in advance.

09:39:39 ==> create or replace package case_test is
09:40:20 2 function returnFirstState return varchar2;
09:40:20 3 end case_test;
09:40:21 4 /

Package created.

09:40:21 ==> create or replace package body case_test is
09:40:21 2 function returnFirstState return varchar2
09:40:21 3 is
09:40:21 4 st_cd varchar2(2);
09:40:21 5 cursor c1 is
09:40:21 6 select st_cd from state
09:40:21 7 where
09:40:21 8 (case when 1 = 1 then 1 else 0 end) = 1
09:40:21 9 and rownum < 2;
09:40:21 10 begin
09:40:21 11 for c_rec in c1 loop
09:40:21 12 st_cd := c_rec.st_cd;
09:40:21 13 end loop;
09:40:21 14 return(st_cd);
09:40:21 15 end;
09:40:21 16 end case_test;
09:40:21 17 /

Warning: Package Body created with compilation errors.

09:40:23 ==> show errors
Errors for PACKAGE BODY CASE_TEST:

LINE/COL ERROR
-------- -----------------------------------------------------------------
8/2 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>

[[IN SQL*PLUS, the query works]]

09:40:27 ==> select st_cd from state
09:40:39 2 where
09:40:39 3 (case when 1 = 1 then 1 else 0 end) = 1
09:40:39 4 and rownum < 2;

ST
--
AK

[[I remove the CASE and the system is happy again]]

09:42:02 ==> create or replace package body case_test is
09:42:02 2 function returnFirstState return varchar2
09:42:02 3 is
09:42:02 4 st_cd varchar2(2);
09:42:02 5 cursor c1 is
09:42:02 6 select st_cd from state
09:42:02 7 where
09:42:02 8 rownum < 2;
09:42:02 9 begin
09:42:02 10 for c_rec in c1 loop
09:42:02 11 st_cd := c_rec.st_cd;
09:42:02 12 end loop;
09:42:02 13 return(st_cd);
09:42:02 14 end;
09:42:02 15 end case_test;
09:42:02 16 /

Package body created.

[[and it works]]

09:42:03 ==> select case_test.returnFirstState from dual;

RETURNFIRSTSTATE
-----------------------------------------------------------------------------
AK
Re: Can't use CASE within a cursor in a package?? [message #37097 is a reply to message #37095] Thu, 17 January 2002 06:01 Go to previous messageGo to next message
pratap kumar tripathy
Messages: 660
Registered: January 2002
Senior Member
Hi Jon,

I think you are using oracle 8.x... database.But, PL/SQL version 8.1.x does not support CASE expression.This is a limitation of the PL/SQL parser. SQL engine uses another parser, that accept CASE clause. PL/SQL engine use the same parser as SQL in Oracle9i.

Starting with Oracle9i, PL/SQL supports the use of case statements and case expressions.

please change your query to eliminate CASE statement.

Hope it helps!!!.

if you are not still clear,please feel free to discuss.

cheers
pratap
Re: Can't use CASE within a cursor in a package?? [message #37739 is a reply to message #37095] Tue, 26 February 2002 03:36 Go to previous message
Milind H. Oke
Messages: 1
Registered: February 2002
Junior Member
well.. as pratap has already clarified, i say that this CASE statement cannot be used in un named PL/SQL blocks... however my question is that is the decode the only work around in these situations ???

awaiting reply... desperately
Previous Topic: no matching unique or primary key for this column-list
Next Topic: List of values to detect available tables
Goto Forum:
  


Current Time: Thu Mar 28 21:15:44 CDT 2024