Solved

Help with PL/SQL  Encountered the symbol "EXCEPTION" when expecting one of the following:

Posted on 2010-11-09
4
1,099 Views
Last Modified: 2012-06-22
Hello,

I have a stored procedure and just added a new data validation check.  However, now when I try to compile I get the following error code.  Any help is much appreciated.  Thanks

"Error(103,6): PLS-00103: Encountered the symbol "EXCEPTION" when expecting one of the following:     begin case declare else elsif end exit for goto if loop mod    null pragma raise return select update while with    <an identifier> <a double-quoted delimited-identifier>    <a bind variable> << close current delete fetch lock insert    open rollback savepoint set sql execute commit forall merge    pipe "

Here is the SQL


   pErrorCode := 0;
   
   SELECT PROJECT_OWNER INTO pClarityProjectOwner FROM CBW_PROJECT_NUMBERS WHERE PROJECT_OWNER = pClarityProjectOwner AND STATUS = 'Open';
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
      pClarityProjectOwner := 0;
      pErrorCode := 1;
   
   IF pErrorCode = 0 THEN
   
     SELECT NVL(CBW_PROJECT_NUMBERS.PROJECT_GROUP,0) INTO pClarityProjectGroup FROM CBW_PROJECT_NUMBERS,CBW_PROJECT_GROUP
     WHERE (CBW_PROJECT_NUMBERS.PROJECT_GROUP = CBW_PROJECT_GROUP.PROJECT_GROUP)
     AND (CBW_PROJECT_NUMBERS.PROJECT_OWNER         = pClarityProjectOwner)
     AND (LOWER(CBW_PROJECT_NUMBERS.PROJECT_TYPE)   = 'bcn')
     AND (LOWER(CBW_PROJECT_GROUP.PROJECT_CATEGORY) = 's')
     AND (LOWER(CBW_PROJECT_NUMBERS.STATUS)         ='open'); (error is right here)     EXCEPTION
        WHEN NO_DATA_FOUND THEN
        pClarityProjectGroup := 0;
        pErrorCode := 2;
       
    END IF;
0
Comment
Question by:jmpatton
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 28

Accepted Solution

by:
Naveen Kumar earned 500 total points
ID: 34098402
try with the below.. If this does not work,can you pls provide the complete code and that can be fixed easily.

   pErrorCode := 0;
   
   SELECT PROJECT_OWNER INTO pClarityProjectOwner FROM CBW_PROJECT_NUMBERS WHERE PROJECT_OWNER = pClarityProjectOwner AND STATUS = 'Open';
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
      pClarityProjectOwner := 0;
      pErrorCode := 1;
   
   IF pErrorCode = 0 THEN
     Begin
     SELECT NVL(CBW_PROJECT_NUMBERS.PROJECT_GROUP,0) INTO pClarityProjectGroup FROM CBW_PROJECT_NUMBERS,CBW_PROJECT_GROUP
     WHERE (CBW_PROJECT_NUMBERS.PROJECT_GROUP = CBW_PROJECT_GROUP.PROJECT_GROUP)
     AND (CBW_PROJECT_NUMBERS.PROJECT_OWNER         = pClarityProjectOwner)
     AND (LOWER(CBW_PROJECT_NUMBERS.PROJECT_TYPE)   = 'bcn')
     AND (LOWER(CBW_PROJECT_GROUP.PROJECT_CATEGORY) = 's')
     AND (LOWER(CBW_PROJECT_NUMBERS.STATUS)         ='open');
     EXCEPTION
        WHEN NO_DATA_FOUND THEN
        pClarityProjectGroup := 0;
        pErrorCode := 2;
      END;
    END IF;
0
 
LVL 14

Expert Comment

by:ajexpert
ID: 34098977
nav_kum_v said it right, you have to encapsulate EXCEPTION in BEGIN -- END block
Here is e.g.

IF ... THEN

  BEGIN

      SELECT ...

  EXCEPTION 

     WHEN NO_DATA_FOUND THEN

           .............................

  END;

END IF;

Open in new window

0
 

Author Closing Comment

by:jmpatton
ID: 34101724
Thanks for the help.  If you get time and you comment back as to why this has to be wrapped in a BEGIN / END statement.  So I know what to look for next time.

Thanks
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 34107990
Your code is of the below form :

select ...

EXCEPTION
 when .....
   
    Begin
--->  if here you want to have some select statements and again handle exceptions, then then need to come in a inner block starting with BEGIN/END as exceptions cannot be nested at the same level.
    EXCEPTION
       when .....
    end;
end;

0

Featured Post

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question