Home » SQL & PL/SQL » SQL & PL/SQL » Package Case Statement (8i)
Package Case Statement [message #322122] Thu, 22 May 2008 09:52 Go to next message
jramya
Messages: 42
Registered: April 2008
Member
Dear All,
I have created a package with some functionality.
I have a case statement in that as below
INSERT INTO test1(
SELECT connum,(CASE WHEN
(atr.VALUE = '00' OR
atr.VALUE = '01' OR
atr.VALUE = '02' OR
atr.VALUE = '12' ) THEN 'PP_LESS50'
ELSE 'PP_OVER50'
END)VAR1
FROM
[.... Tables..]
)

I seem no error with my case statement but while compiling the
package body I get the following error

PLS-00103: Encountered the symbol "CASE" when expecting one of the following: ( - + mod not null others <an identifier>
TO make more strange it works fine when i run it in sql editor substituting vaiable values.
Can you get me a clue on the issue please...

Re: Package Case Statement [message #322123 is a reply to message #322122] Thu, 22 May 2008 09:58 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Case can not be used in 8i's pl/sql
Re: Package Case Statement [message #322127 is a reply to message #322123] Thu, 22 May 2008 10:01 Go to previous messageGo to next message
jramya
Messages: 42
Registered: April 2008
Member
Hi,
Thank you for your response.
I can use the CASE statement in the editor,its not working only inside the package.
My apologies if you did meant the same.
Re: Package Case Statement [message #322139 is a reply to message #322127] Thu, 22 May 2008 10:28 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Which editor are you talking about?

Once again: CASE is available in 8i's SQL, but not in PL/SQL
Re: Package Case Statement [message #322142 is a reply to message #322139] Thu, 22 May 2008 10:39 Go to previous messageGo to next message
jramya
Messages: 42
Registered: April 2008
Member
I did specify TOAD editor.
Your links were helpful,Got the point.
Thank You
Re: Package Case Statement [message #322152 is a reply to message #322142] Thu, 22 May 2008 11:22 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
I did specify TOAD editor.

You did? Where, exactly?
Re: Package Case Statement [message #322342 is a reply to message #322152] Fri, 23 May 2008 03:16 Go to previous messageGo to next message
jramya
Messages: 42
Registered: April 2008
Member
Hi LittleFoot,
I can execute the CASE statement in a normal SQL query
[a select statement] in TOAD.
When I tried to put the same coding into a package[in TOAD 9.6,Oracle 8i] it prompted me the error.
The link provided by you says clearly CASE works only in SQL and not in PLSQL in Oracle 8i.
Hope I have answered your question.
Why is that its work in SQL and not in PLSQL,both being ORACLE Sad
Re: Package Case Statement [message #322343 is a reply to message #322342] Fri, 23 May 2008 03:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Why is that its work in SQL and not in PLSQL,both being ORACLE

Because before 9i each engine (SQL and PL/SQL) had its own SQL syntax analyzer and one progressed more rapidly than the other one.
This is no more the case now and both support the same SQL syntax.

Regards
Michel
Re: Package Case Statement [message #322353 is a reply to message #322343] Fri, 23 May 2008 03:44 Go to previous message
jramya
Messages: 42
Registered: April 2008
Member
Thanks for your reply.
Sounds Interesting.
After using Orafaq I am learning alot than just Syntax.


Previous Topic: Comma seperated value
Next Topic: losing diacritic data after exporting
Goto Forum:
  


Current Time: Fri Dec 09 13:48:31 CST 2016

Total time taken to generate the page: 0.27039 seconds