Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Using CASE WHEN expression in Package Procedure is not allowed?

Using CASE WHEN expression in Package Procedure is not allowed?

From: Roel Schreurs <schreurs_roel_at_hotmail.com>
Date: 4 Oct 2004 06:17:27 -0700
Message-ID: <321ebdef.0410040517.1f9b5d5c@posting.google.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US