Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> Using CASE WHEN expression in Package Procedure is not allowed?
I try to use a CASE WHEN expression in the select list of a query. The
query result should be returned by a ref cursor. I get a compile time
error.
PLS-00103: Encountered the symbol "CASE" when expecting one of the
following:
( - + mod null <an identifier>
<a double-quoted delimited-identifier> <a bind variable>
table avg count current
Why is this not allowed? I can use other types of expressions, like
DECODE. Also, I can executed the query as a plain SQL statement. I am
just curious to know the cause, I have implemented a work-around, so
no help is needed there.
My database version 8.1.7.
Below an example that will show the compile error.
CREATE OR REPLACE PACKAGE Test
AS
TYPE MyCursorType IS REF CURSOR;
PROCEDURE TestCase(MyCursor OUT MyCursorType);
END;
/
CREATE OR REPLACE PACKAGE BODY Test
AS
PROCEDURE TestCase(MyCursor OUT MyCursorType)
IS
BEGIN
OPEN MyCursor FOR
SELECT SYSDATE
, CASE WHEN MOD(TO_NUMBER(TO_CHAR(SYSDATE, 'ss')), 2) = 0 THEN
'Even' ELSE 'Odd' END AS EvenOdd
FROM DUAL;
END;
END;
/
Received on Mon Oct 04 2004 - 08:17:27 CDT
![]() |
![]() |