Solved

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

Posted on 2010-11-09
4
1,091 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Via a live example, show how to take different types of Oracle backups using RMAN.

759 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