DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: CASE Statement in PL/SQL block

  1. #1
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    I was extremely excited recently to discover that the CASE statement is finally supported in Oracle. I have also been playing with the incredibly powerful analytical functions.

    However, while both of these features seem to come 'for free' in the SQL, they also do not appear to be supported by PL/SQL. A PL/SQL block that contains a SQL statement that includes any analytical function or case statement will fail compilation. I get one of the following errors returned:

    ORA-06550: line 12, column 21:
    PLS-00103: Encountered the symbol "(" when expecting one of the following:

    ORA-06550: line 4, column 9:
    PLS-00103: Encountered the symbol "CASE" when expecting one of the following:

    Does anybody have any idea why this might be? This is going to be a major impedement to the implementation of these new, very powerful features. Of course, wrapping the statement in a string and doing an execute immediate on it works fine, but that is a really poor work-around.

    Is there some package that must be run, or client upgrade, or some other equally obscure thing that must be done to enable these constructs under PL/SQL??

    Thanks,

    - Chris

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Chris, which Oracle release are you runing? Usually when new SQL features are introduced, they are not supported in PL/SQL at least until the next major release. This have been practice with Oracle for quite long time, but I seem to remember I've read somewhere very recently they've made a commitment to support any new SQL feature in PL/SQL simultaneously. But then again, this might have also been nothing else as one of their 9i marketing hypes...

    Anyway, if I remember corectly CASE have been introduced in 8.1.6, so it might be worth trying it on 8.1.7....
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Yeah, just got the info from metalink - the new constructs are not yet supported in the PL/SQL parser. I'm sorry, but that is *so* pathetic! I've only been using Oracle for a few years now, and I'd never really run into this before. I simply can't believe that the development community stood for this practice before. I'm sorry, but it just floors me.
    Yes, supposedly in 9i, they will *finally* share the same parser - what a concept. I'm sorry, this just has me really ticked right now. Unbelievable!


    thanks for the feedback, tho :)


    - Chris


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width