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