Solved

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

Posted on 2006-11-10
8
575 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
  • 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 76

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
 
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 76

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 76

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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Join & Write a Comment

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
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.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
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…

744 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

11 Experts available now in Live!

Get 1:1 Help Now