Solved

raise_application error encountered issue

Posted on 2009-05-20
3
918 Views
Last Modified: 2013-12-07
Please advise
I am encountering error when I try to run following procedure--

CREATE OR REPLACE PROCEDURE REMOVEINDEX
AS
BEGIN
--Altering Indexes
EXECUTE IMMEDIATE 'alter table T1 drop constraint PK_T1';
exception
raise_application_error(-20024,'fail at PK_T1');
end;
EXECUTE IMMEDIATE 'alter table T2 drop constraint PK_T2';
exception
raise_application_error(-20025,'fail at PK_T2');
end;

Error is as follows--

7/1      PLS-00103: Encountered the symbol "RAISE_APPLICATION_ERROR" when      
         expecting one of the following:                                        
         pragma when                                                            
         The symbol "pragma" was substituted for "RAISE_APPLICATION_ERROR"      
         to continue.                                                          
                                                                               
8/1      PLS-00103: Encountered the symbol "END" when expecting one of the      
         following:                                                            
         pragma when                                                            
                                                                               
15/1     PLS-00103: Encountered the symbol "RAISE_APPLICATION_ERROR" when      
Thanks
0
Comment
Question by:sunilbains
  • 2
3 Comments
 
LVL 35

Expert Comment

by:johnsone
ID: 24433322
You are missing a when clause to the exception.  Assuming you want to raise the exception for all errors, this should be what you are looking for.
CREATE OR REPLACE PROCEDURE REMOVEINDEX
AS
BEGIN
--Altering Indexes
EXECUTE IMMEDIATE 'alter table T1 drop constraint PK_T1';
exception
when others then
raise_application_error(-20024,'fail at PK_T1');
end;
EXECUTE IMMEDIATE 'alter table T2 drop constraint PK_T2';
exception
when others then
raise_application_error(-20025,'fail at PK_T2');
end;

Open in new window

0
 
LVL 35

Accepted Solution

by:
johnsone earned 500 total points
ID: 24433338
That won't work either.  You are missing some begins as well.
CREATE OR REPLACE PROCEDURE REMOVEINDEX
AS
BEGIN
--Altering Indexes
  begin
    EXECUTE IMMEDIATE 'alter table T1 drop constraint PK_T1';
  exception
    when others then
      raise_application_error(-20024,'fail at PK_T1');
  end;
  begin
    EXECUTE IMMEDIATE 'alter table T2 drop constraint PK_T2';
  exception
    when others then
      raise_application_error(-20025,'fail at PK_T2');
  end;
end;
/

Open in new window

0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
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.

697 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