Solved

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

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Stay Alert! 13 62
Format Number Field 10 39
Extract the first word (before the , ) 2 21
sql query 9 21
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…
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
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

706 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now