Solved

raise_application error encountered issue

Posted on 2009-05-20
3
907 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 34

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 34

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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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

705 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

19 Experts available now in Live!

Get 1:1 Help Now