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:[EMAIL PROTECTED]] > 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:[EMAIL PROTECTED] > http://Gennick.com * http://MichiganWaterfalls.com * > http://ValleySpur.com > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Jonathan Gennick > INET: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
