RE: CASE under Oracle8i

From: CHAN Chor Ling Catherine (CSC) <clchan_at_nie.edu.sg>
Date: Sun, 24 Mar 2002 20:28:19 -0800
Message-ID: <F001.00431AEB.20020324202819@fatcity.com>

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).

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