Avatar of jmpatton
jmpatton asked on

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

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;
Oracle Database

Avatar of undefined
Last Comment
Naveen Kumar

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Naveen Kumar

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ajexpert

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

ASKER
jmpatton

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
Naveen Kumar

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;

This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23