Oracle-L: Re: CASE under Oracle8i

From: Lyubomir Petrov <lpetrov_at_yahoo.com>
Date: Sun, 24 Mar 2002 20:28:20 -0800
Message-ID: <F001.00431B05.20020324202820@fatcity.com>

Jonathan,

Oracle 8.1.7/Linux:

lpetrov_at_TESTDB> select * from v$version;

BANNER

Oracle8i Enterprise Edition Release 8.1.7.0.1 - Production
PL/SQL Release 8.1.7.0.0 - Production
CORE 8.1.7.0.0 Production
TNS for Linux: Version 8.1.7.0.0 - Development NLSRTL Version 3.4.1.0.0 - Production

lpetrov_at_TESTDB> show serveroutput
serveroutput ON size 2000 format WORD_WRAPPED

lpetrov_at_TESTDB> SELECT CASE
WHEN DUMMY='X' THEN 'Dual is OK'
ELSE 'Dual is messed up'
END
FROM DUAL; CASEWHENDUMMY='X'

Dual is OK

lpetrov_at_TESTDB> lpetrov_at_TESTDB>
lpetrov_at_TESTDB> 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;
12 /
   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>

Regards,
Lyubomir Petrov

Jonathan Gennick <listmail_at_gennick.com> wrote:
> 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).

Do You Yahoo!?
Yahoo! Movies - coverage of the 74th Academy Awards® http://movies.yahoo.com/

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Lyubomir Petrov
  INET: lpetrov_at_yahoo.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).

Received on Sun Mar 24 2002 - 22:28:20 CST