OraFAQ Forum: SQL & PL/SQL » Package Case Statement

Home » SQL & PL/SQL » SQL & PL/SQL » Package Case Statement (8i)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Package Case Statement [message #322122]

Thu, 22 May 2008 09:52

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

Report message to a moderator

Re: Package Case Statement [message #322123 is a reply to message #322122]

Thu, 22 May 2008 09:58

Frank
Messages: 7901
Registered: March 2000

Senior Member

Case can not be used in 8i's pl/sql

Report message to a moderator

Re: Package Case Statement [message #322127 is a reply to message #322123]

Thu, 22 May 2008 10:01

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.

Report message to a moderator

Re: Package Case Statement [message #322139 is a reply to message #322127]

Thu, 22 May 2008 10:28

Littlefoot
Messages: 21826
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

Report message to a moderator

Re: Package Case Statement [message #322142 is a reply to message #322139]

Thu, 22 May 2008 10:39

jramya
Messages: 42
Registered: April 2008

Member

I did specify TOAD editor.
Your links were helpful,Got the point.
Thank You

Report message to a moderator

Re: Package Case Statement [message #322152 is a reply to message #322142]

Thu, 22 May 2008 11:22

Littlefoot
Messages: 21826
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Quote:

I did specify TOAD editor.

You did? Where, exactly?

Report message to a moderator

Re: Package Case Statement [message #322342 is a reply to message #322152]

Fri, 23 May 2008 03:16

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

Report message to a moderator

Re: Package Case Statement [message #322343 is a reply to message #322342]

Fri, 23 May 2008 03:21

Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Package Case Statement [message #322353 is a reply to message #322343]

Fri, 23 May 2008 03:44

jramya
Messages: 42
Registered: April 2008

Member

Thanks for your reply.
Sounds Interesting.
After using Orafaq I am learning alot than just Syntax.

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Oct 06 14:22:26 CDT 2025