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;
jmpattonAsked:
Who is Participating?
 
Naveen KumarConnect With a Mentor Production Manager / Application Support ManagerCommented:
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
 
ajexpertCommented:
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
 
jmpattonAuthor Commented:
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
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.