Re: CASE under Oracle8i

I think the case expression is supported in PL/SQL in 9i. prior to 9i, the pl/sql and sqlplus engine are separated. CASE just supported in sqlplus engine not in pl/sql engine.

Joan

"CHAN Chor Ling Catherine (CSC)" wrote:
>
> Hi Jonathan,
>
> I am using Oracle8i Enterprise Edition Release 8.1.6.1.0
>
> SQL> SET SERVEROUTPUT ON
> SQL> EDIT
> Wrote file afiedt.buf
>
> 1 DECLARE
> 2 dual_message VARCHAR2(20);
> 3 BEGIN
> 4 SELECT CASE
> 5 WHEN DUMMY='X' THEN 'Dual is OK'
> 6 ELSE 'Dual is messed up'
> 7 END INTO dual_message
> 8 FROM DUAL;
> 9 DBMS_OUTPUT.PUT_LINE(dual_message);
> 10* END;
> SQL> /
> SELECT CASE
> *
> ERROR at line 4:
> ORA-06550: line 4, column 11:
> 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>
>
> Hope it helps.
>
> Regds,
> Catherine
> -----Original Message-----
> From: Jonathan Gennick [mailto:listmail_at_gennick.com]
> Sent: Monday, March 25, 2002 11:18 AM
> To: Multiple recipients of list ORACLE-L
> Subject: CASE under Oracle8i
>
> If you run Oracle8i, and could conveniently test a couple of
> statements for me, I'd appreciate it.
>
> First, I believe the following should work under Oracle8i:
>
> SELECT CASE
> WHEN DUMMY='X' THEN 'Dual is OK'
> ELSE 'Dual is messed up'
> END
> FROM DUAL;
>
> I'm less certain about the following, which I vagualy recall
> hearing might not work under Oracle8i, but which does work
> under Oracle9i:
>
> DECLARE
> dual_message VARCHAR2(20);
> BEGIN
> SELECT CASE
> WHEN DUMMY='X' THEN 'Dual is OK'
> ELSE 'Dual is messed up'
> END INTO dual_message
> FROM DUAL;
> DBMS_OUTPUT.PUT_LINE(dual_message);
> END;
>
> Be sure to SET SERVEROUTPUT ON before executing the above.
> Otherwise you won't see the results.
>
> The point of all this is that I seem to recall hearing that,
> while SQL in 8i supported the CASE statement, that SQL
> within PL/SQL did not. I'm trying to verify the truth or
> falsity if that statement.
>
> Jonathan Gennick --- Brighten the corner where you are
> mailto:jonathan_at_gennick.com
> http://Gennick.com * http://MichiganWaterfalls.com *
> http://ValleySpur.com
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Jonathan Gennick
> INET: listmail_at_gennick.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858)
> 538-5051
> San Diego, California -- Public Internet access /
> Mailing Lists
>
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail
> message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru')
> and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).
> You may
> also send the HELP command for other information (like
> subscribing).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: CHAN Chor Ling Catherine (CSC)
> INET: clchan_at_nie.edu.sg
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Joan Hsieh
  INET: joan.hsieh_at_tufts.edu

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).