[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

raise_application error encountered issue

Posted on 2009-05-20
3
Medium Priority
?
956 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
2 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 2000 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
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
This video shows how to recover a database from a user managed backup
Suggested Courses

591 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