RE: CASE under Oracle8i

From: Larry Elkins <elkinsl_at_flash.net>
Date: Sun, 24 Mar 2002 20:53:19 -0800
Message-ID: <F001.00431B3E.20020324205319@fatcity.com>

Jonathan,

Prior to 9i, PL/SQL has typically lagged a bit behind new SQL features and constructs. So yes, things like the CASE statement, the analytical functions introduced in 8.1.6, and other things aren't *directly* supported in PL/SQL in 8i (or at least up through the latest patch sets I have, I hate to so never/always ;-)).

The easy way around it is to use Native Dynamic SQL.

  1 DECLARE
  2 dual_message VARCHAR2(20);
  3 BEGIN

  4     Execute Immediate
  5     'SELECT CASE
  6      WHEN DUMMY=''X'' THEN ''Dual is OK''
  7     ELSE ''Dual is messed up''
  8     END
  9     FROM DUAL' into dual_message;
 10     DBMS_OUTPUT.PUT_LINE(dual_message);

 11* END;
SQL> /
Dual is OK

PL/SQL procedure successfully completed.

So, back to your question, if someone wants to be picky, they could say the CASE statement *is* available in PL/SQL. But, we have to bypass PL/SQL via NDS, it's not natively supported directly in PL/SQL.

Regards,

Larry G. Elkins
elkinsl_at_flash.net
214.954.1781

> -----Original Message-----
> From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Jonathan
> Gennick
> Sent: Sunday, March 24, 2002 9:18 PM
> 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: Larry Elkins
  INET: elkinsl_at_flash.net

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:53:19 CST