• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 958
  • Last Modified:

raise_application error encountered issue

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
sunilbains
Asked:
sunilbains
  • 2
1 Solution
 
johnsoneSenior Oracle DBACommented:
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
 
johnsoneSenior Oracle DBACommented:
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
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.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now