Home » SQL & PL/SQL » SQL & PL/SQL » EXCEPTION WHEN OTHERS (Oracle 10.2.0.5.0 )
EXCEPTION WHEN OTHERS [message #621798] Mon, 18 August 2014 08:12 Go to next message
Buchas
Messages: 81
Registered: March 2006
Member
Hello.

I have a procedure SET_PAYMENT_FOR_POLICY which is called by 3 systems: Legacy System (which is ~14 years old, but still being developed), new Java module and external financial system.
There is a requirement, that an insurance policy should be locked when this procedure is called.
Due to the reasons unknown java module implementers insisted on making an active locking mechanism (Before we used SELECT <..> FOR UPDATE NOWAIT, now we moved to inserting and commiting a policy's ID in autonomous transaction, [so that java module could know the context?]).
In order to implement active locking, I need to unlock the policy in case any exception is raised.
So I ended up with such a code:
    EXCEPTION WHEN OTHERS THEN
SETTINGS.ACTIVE_LOCKING_PACK.UNLOCK_ALL_SESSION_OBJECTS(SYS_CONTEXT('USERENV', 'SESSIONID'));
SETTINGS.ERRORS.ADD_ERROR_WITH_NUMBER_AND_TEXT(SQLCODE, SQLERRM);
            RAISE;

Which has a drawback, that "RAISE;" hides (shortens) the original error stack (this is especially annoying when external financial system reports the error messaging us the error stack it gets).
Now I am not sure of what question should I ask:
How do I avoid hiding error stack in "EXCEPTION WHEN OTHERS"?
or
How do I persuade java developers, that active locking is evil?

Help please.

[Updated on: Mon, 18 August 2014 08:28]

Report message to a moderator

Re: EXCEPTION WHEN OTHERS [message #621801 is a reply to message #621798] Mon, 18 August 2014 08:58 Go to previous messageGo to next message
Michel Cadot
Messages: 59143
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

* WHEN OTHERS
* http://www.orafaq.com/forum/mv/msg/174329/521460/#msg_521460

Re: EXCEPTION WHEN OTHERS [message #621803 is a reply to message #621798] Mon, 18 August 2014 09:04 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2341
Registered: May 2013
Location: World Wide on the Web
Senior Member
You have many questions. Let's take them one by one.

Firstly, you have misconception that RAISE inside exception will hide the error. No, raise is used in the exception handler to re-raise the exception and give it to the caller since the WHEN OTHERS hides the error. When an error occurs, and if you have an exception block, the handle moves to the exception block. The caller will remain unaware untill you re-raise it using RAISE.
Re: EXCEPTION WHEN OTHERS [message #621804 is a reply to message #621801] Mon, 18 August 2014 09:07 Go to previous messageGo to next message
Buchas
Messages: 81
Registered: March 2006
Member
If I get you right, you mean that as my case falls under
Quote:
When to use WHEN OTHERS?

Actually, the only cases you have to use WHEN OTHERS are the following ones:

You opened some resources (cursor, file...) and have to close them before leaving
You want to log all errors

In both cases, the WHEN OTHERS THEN part MUST be ended with a "RAISE;" statement.


I should be logging the format_error_stack and format_error_backtrace into some log_table (as your 2nd link suggests).

OK, looks reasonable, thanks Michael!
Re: EXCEPTION WHEN OTHERS [message #621813 is a reply to message #621804] Mon, 18 August 2014 10:41 Go to previous messageGo to next message
Buchas
Messages: 81
Registered: March 2006
Member
Quote:
Firstly, you have misconception that RAISE inside exception will hide the error.


Lalit Kumar B, as you can see in http://www.orafaq.com/wiki/WHEN_OTHERS example:
Quote:
Note: Even if you add a "RAISE;" statement in your exception block it is not correct as you will still hide the actual origin of the error. See the following simple case:

SQL> declare a pls_integer := 1; b pls_integer := 0;
2 begin
3 b := a/b;
4 exception when others then raise;
5 end;
6 /
declare a pls_integer := 1; b pls_integer := 0;
*
ERROR at line 1:
ORA-01476: divisor is equal to zero
ORA-06512: at line 4


"RAISE;" hides the error (or to be more precise- "the actual origin of the error").
Why can't Oracle show the actual origin of the error together with the error stack? You can imagine a bunch of procedures, analogical to SET_PAYMENT_FOR_POLICY, and calling each other. Then each procedure should have an insert into a log table, which might become a nightmare to analyse... All this extra code is completely unnecessary in other programming languages. E.g. java (I hope I remember it correctly, I am no java developer) can "throw();" in an exception block and the original stack doesn't get cut off. It seems like Oracle is forcing to write some extra code that can be avoided. Why? Is it "manufacturing demand" - creates a problem in a simple place and offers a clumsy solution (format_error_stack+format_error_backtrace)?
Re: EXCEPTION WHEN OTHERS [message #621818 is a reply to message #621813] Mon, 18 August 2014 11:03 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2341
Registered: May 2013
Location: World Wide on the Web
Senior Member
No, you didn't properly understand the link Michel shared with you. It's not the RAISE which hides the error. Raise is used in the exception handler to re-raise the error which "When Others" exception block did hide.

And regarding your question about "bunch of processes". You tell me, how would you look into the issues which are a part of production batch process, and you cannot stop the entire batch process due to one error as they would be scheduled with complex dependencies. So, all you could do is log those errors and later analyze. And that's the reason you need to use backtrace feature to ease your analysis to debug those errors.

P.S. : Michel gave you so much information in the links provided that anybody wouldn't nhave anymore questions. Based on T.Kyte's blog on the same subject, even I wrote my thoughts here http://lalitkumarb.wordpress.com/2014/05/02/when-others-then-null-a-bug/ The wiki link which Michel wrote and shared with you should have answered your doubts.

Can you show where Michel ever said that " Raise" hides the error?

Edit : Wrote more including post scriptum

[Updated on: Mon, 18 August 2014 11:13]

Report message to a moderator

Re: EXCEPTION WHEN OTHERS [message #621837 is a reply to message #621813] Mon, 18 August 2014 23:53 Go to previous messageGo to next message
oralover2006
Messages: 118
Registered: January 2010
Location: India
Senior Member
just replace EXCEPTION part with the example and execute what mentioned in the link by Michel ( the second link ) who given it to you for your understanding, this will show you the actual LINE No. where was the Error... ( Michel helped me for the same, Thanks to him )

read output thoroughly after execution of the statement.

regards.

[Updated on: Mon, 18 August 2014 23:56]

Report message to a moderator

Re: EXCEPTION WHEN OTHERS [message #621845 is a reply to message #621837] Tue, 19 August 2014 02:06 Go to previous messageGo to next message
Buchas
Messages: 81
Registered: March 2006
Member
Quote:

And regarding your question about "bunch of processes". You tell me, how would you look into the issues which are a part of production batch process, and you cannot stop the entire batch process due to one error as they would be scheduled with complex dependencies. So, all you could do is log those errors and later analyze. And that's the reason you need to use backtrace feature to ease your analysis to debug those errors.


I was talking about "bunch of procedures", not "bunch of processes". Therefore most probably I don't understand the rest of this quote, because I assume you are talking about processes there.

Quote:
Can you show where Michel ever said that " Raise" hides the error?

It is in the 1st link Michel Cadot has shared with us, in chapter: "WHEN OTHERS hides where the error comes from". That wiki states:

Quote:
Note: Even if you add a "RAISE;" statement in your exception block it is not correct as you will still hide the actual origin of the error. See the following simple case:

SQL> declare a pls_integer := 1; b pls_integer := 0;
2 begin
3 b := a/b;
4 exception when others then raise;
5 end;
6 /
declare a pls_integer := 1; b pls_integer := 0;
*
ERROR at line 1:
ORA-01476: divisor is equal to zero
ORA-06512: at line 4

Does the error really come from line 4? But there is nothing at line 4 that can raise a "divisor is equal to zero" error! Now if you comment the exception block:

SQL> declare a pls_integer := 1; b pls_integer := 0;
2 begin
3 b := a/b;
4 -- exception when others then raise;
5 end;
6 /
declare a pls_integer := 1; b pls_integer := 0;
*
ERROR at line 1:
ORA-01476: divisor is equal to zero
ORA-06512: at line 3

You have then the actual line that raises the error: line 3.


As you can see the construction "exception when others then raise;" has hidden the actual row where the error had happened.
Re: EXCEPTION WHEN OTHERS [message #621856 is a reply to message #621845] Tue, 19 August 2014 03:27 Go to previous message
Lalit Kumar B
Messages: 2341
Registered: May 2013
Location: World Wide on the Web
Senior Member
Use dbms_utility.format_error_stack and dbms_utility.format_error_backtrace to get the call stack. Your doubts will get clarified. See Michel's second link for example.
Previous Topic: MATERIALIZED VIEW on commit
Next Topic: Retrieve one transaction which has max Net Sales in that day for 1 year and display by date
Goto Forum:
  


Current Time: Fri Sep 19 19:23:17 CDT 2014

Total time taken to generate the page: 0.08961 seconds