Solved

URGENT :: Abrupt Program Termination --- RAISE_APPLICATION_ERROR not working ...

Posted on 2006-11-10
8
585 Views
Last Modified: 2008-02-01
Hi,

I have a driver procedure that calls various other procedure which in turn calls other procedures.
I want to raise a highest level fatal exception when any DMl fails. This can happen in any of the inner or outer procedures i.e. i want to display an error message and stop all execution for this situation.
I am using the RAISE_APPLICATION_ERROR (-20102, 'Fatal Exception Occurred. Abormal Program Termination.'); for this purpose.
But since all my procedures handle exceptions, if this fatal exception is encountered in the inner procedures, it shows the application error and is then handled by the exception block of the calling procedure, which i dont want.
I want it to terminate everything the moment RAISE_APPLICATION_ERROR is called.

Is there any other way i can do it so that all processing is stopped once that function/procedure is called?
Also please let me know how to exit from the application by calling EXIT / QUIT from the procedure.

Quick responses will be highly appreciated.

Thanks in Advance,

JAK
0
Comment
Question by:Jinesh Kamdar
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
8 Comments
 
LVL 18

Author Comment

by:Jinesh Kamdar
ID: 17916846
Page Editor,

Can you move this question to the more specific TA Oracle?

Thanks,
JAK
0
 
LVL 18

Author Comment

by:Jinesh Kamdar
ID: 17919738
Hello, anybody there to help me out ??? I thot this wud be a pretty simple question to answer for the Experts .....
0
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 17932769
There's no need to move it to the Oracle TA.  Many of the Oracle experts look here as well.

I'm not sure I really follow what you are wanting.  If properly handled, using RAISE_APPLICATION_ERROR should bubble up to the calling procedure.

Based on what I think you are after, here's a little test case that demonstrates this.  proc1 will always generate an error.  proc2 calls proc1.  proc3 calls proc1 first then trys to call proc2.  In proc3, since the first call to proc1 fails, proc3 terminates (never calls proc2) and I get the error from proc3.

I'm sure I'm missing something here so can you explain where I'm missing something (hopefully you can build on my example script).
---------------
create or replace procedure proc1 as
      someVar      number;
begin
      dbms_output.put_line('In proc1');

      -- SQL to generate a simple NO_DATA_FOUND error
      select 1 into someVar from dual where 1=2;

      exception
            when others then
                  RAISE_APPLICATION_ERROR (-20102, 'Fatal Exception Occurred (in proc1). Abormal Program Termination.');
end;
/

show errors

create or replace procedure proc2 as
begin
      dbms_output.put_line('In proc2');
      proc1;

      exception
            when others then
                  RAISE_APPLICATION_ERROR (-20102, 'Fatal Exception Occurred (in proc2). Abormal Program Termination.');
end;
/

show errors


create or replace procedure proc3 as
begin
      dbms_output.put_line('In proc3');
      proc1;
      proc2;

      exception
            when others then
                  RAISE_APPLICATION_ERROR (-20102, 'Fatal Exception Occurred (in proc3). Abormal Program Termination.');
end;
/

show errors

exec proc3;
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 18

Author Comment

by:Jinesh Kamdar
ID: 17933268
slightwv,

Ur solution is not exactly but pretty similar to wat i already implemented the day i posted this question.
My scenario is smthng like this :-

pk1.proc ---> pk2.proc ---> pk3.proc1 ---> pk3.proc2

Now, i cud have a fatal exception at any of these 4 levels. So if there is a fatal exception (like a DML failure), then i wanna exit from the entire sequence watever level it may be.
However, i dont wanna exit the entire sequence if i encounter a non-fatal exception like a NO_DATA_FOUND since i process them differently from fatal exceptions.

The notion that i had in my mind was that if i call RAISE_APPLICATION_ERROR from any of the above procs, then it wud exit the entire sequence. But i was wrong. If RAISE_APPLICATION_ERROR is called from the exception block of pk3.proc2 and caught in the exception block of pk3.proc1, then pk2.proc wud stil continue as normal.

For this i set a global variable (g_is_fatal_set) in the spec of pk3 and check its status in the exception block of the entire call sequence. If its set, i call RAISE_APPLICATION_ERROR again and so the fatal exception propagates thru the entire sequence to the starting call. I guess this is the logical way to do it instead of terminating abruptly.

However, wat i wanted (at the time of posting the question) was to know a way to directly exit from the place wer the fatal exception occurred and terminate abruptly. Something similar to EXIT or QUIT that wud stop all execution right from pk3 to pk1. If u have any idea, please let me know. I wont change my real code since it has already been frozen now, but i wud stil like to kno abt it for my info.

Thanks,
JAK
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 17933410
>>The notion that i had in my mind was that if i call RAISE_APPLICATION_ERROR from any of the above procs, then it wud exit the entire sequence.

It should as long as all the procs in the chain have properly handled exceptions.

I guess I'm still not following.  Every procedure should be set up to handle normal exceptions like NO_DATA_FOUND but when you call RAISE_APPLICTION_ERROR, it stops in it's tracks and reports back to the calling procedure.  It should then immediatly fall into the calling procedures exceptions handlers.  This process continues until there are no more exception handlers in a calling procedure and the process exits.

In my example, proc3 is supposed to call proc1 then proc2.  It never makes it to the call to proc2.  I'm not sure where I'm missing your problem.
0
 
LVL 18

Author Comment

by:Jinesh Kamdar
ID: 17938645
>> It should as long as all the procs in the chain have properly handled exceptions.

All procs in the chain do have their properly handled exceptions. But as i mentioned before, i handle fatal and non-fatal exceptions separately. For non-fatal exceptions, i just make an entry into an error table and proceed for further processing. But for fatal-exceptions i have to stop all processing and exit the chain immediately.

RAISE_APPLICATION_ERROR, if raised in the execution block of a proc (say proc3), does go back to the calling proc's (say proc p2) exception block. If we just have a WHEN NO_DATA_FOUND and WHEN OTHERS in this exception block, then control goes to the WHEN OTHERS section. If we do no explicitly have another RAISE_APPLICATION_ERROR in this section, then control goes to the next stmt in the proc (say proc1) that called proc2, instead of going to the exception block of proc1.

E.g.

CREATE OR REPLACE proc3 IS

BEGIN

IF (condition = TRUE) THEN
     RAISE_APPLICATION_ERROR (..., ...);
END IF;

EXCEPTION
WHEN NO_DATA_FOUND THEN
.....;

WHEN OTHERS THEN
...... ;
END;

CREATE OR REPLACE proc2 IS

BEGIN

SELECT TO_DATE('NOV-06', 'MM-YYYY') FROM DUAL;

proc3;

SELECT * FROM DUAL WHERE 1 = 2;

EXCEPTION
WHEN NO_DATA_FOUND THEN
.....;

WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE (.....) ;
END;

CREATE OR REPLACE proc1 IS

BEGIN

proc2;

SELECT TO_DATE('NOV-06', 'MM-YYYY') FROM DUAL;

COMMIT;

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE (.....) ;
END;

In the above scenario, the chain goes proc1 ---> proc2 ---> proc3.
Assuming condition is TRUE, RAISE_APPLICATION_ERROR is called from proc3.
Next, control goes to the exception block of proc2 in the WHEN OTHERS section.
It prints the DBMS output and exits proc2 to resume with the COMMIT in proc1 instead of going to the exception block of proc1. Ideally, it shud not COMMIT since for me, its a fatal exception.

So my point here is that i have to let proc1 and proc2 know that a fatal exception occurred in proc3 and it shud not proceed further. But the catch here is that WHEN OTHERS of proc1 and proc2 cud also be called because of the invalid format in the SELECT stmt which is a non-fatal exception for me (its just an example scenario). So basically i have to differentiate in WHEN OTHERS of proc1 whether it was called bcoz of a fatal exception or a non-fatal one.

For this determination, i m using a public var. which is set in proc3 wen a fatal exception occurs. I check this var. in the WHEN OTHERS sections of proc2 and proc1 to determine the fatality of the exception.

Do u see any other way to do it ??? I hope i have made my case clear now.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 17941566
>>Do u see any other way to do it ???

I think I see the problem now.  Without spending a lot of time of it I think the only way around it is you explicitly handle every possible exception so the only exception that falls into 'OTHERS' is from a previos RAISE_APPLICATION_ERROR.
0
 
LVL 18

Author Comment

by:Jinesh Kamdar
ID: 17941815
>> the only way around it is you explicitly handle every possible exception

Thats the best way to do it but not always the most easy or practical. The Format exception was just a sample and i cud have so many other similar exceptions in my procedures spanning several hundred lines. Plus this code is not completely written by me; i was only asked to add certain functionality in each of these procedures. This situation arose because of the very reason that all exceptions were not being handled explicitly and most of them fall into OTHERS.

I had to implement this change on last Saturday and freeze it for roll-out, hence the URGENT call.
Nevertheless, i vil award u the points since the solution that i implemented on Saturday itself is pretty much similar to wat u already suggested in ur 1st post --- checking the is_global_set var. for fatal exceptions and then calling RAISE_APPLICATION_ERROR in the WHEN OTHERS of every exception block. Just wanted to know if any other technique to achieve the same thing was available or not. Feels good that i did the right thing :-)

Thanks for ur explanations!
0

Featured Post

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

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 blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

691 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