RE: raise_application_error and when others

  • From: Chuck Boddy <Chuck.Boddy@xxxxxxxxxxxxxxx>
  • To: "tim@xxxxxxxxx" <tim@xxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
  • Date: Wed, 30 Nov 2011 22:49:17 +0000

1.       The reason I didn't include the code is that it might have raised more 
questions than answered due to the fact that one's lack of understanding to our 
specific application
2.       Again, application specific.  We DO NOT want to do as you suggest 
issue a RAISE or RAISE_APPLICATION_ERROR.  Under our specific circumstances, we 
want to specifically log the error and have process continue with the error 
noted.  We would have some pretty unhappy users if we did as you suggest.

I basically was looking just for acknowledgement of what I believe my 
expectation to be.  That is, when others exception should not be handled if 
RAISE_APPLICATION_ERROR is first met.

In a very roundabout way, I think you have answered that question in the 
affirmative.

From: Tim Gorman [mailto:tim@xxxxxxxxx]
Sent: Wednesday, November 30, 2011 2:21 PM
To: Chuck Boddy; oracle-l@xxxxxxxxxxxxx
Subject: Re: raise_application_error and when others

Just curious why you haven't simply displayed the code in question in the 
email?  It's faster and simpler and less prone to mis-interpretation and 
mis-statements than trying to interpret the problem in your own words.  Code is 
unambiguous, language is infinitely ambiguous.

By the way, your use of WHEN OTHERS sounds inappropriate.  OTHERS covers 
several thousand possible exceptions, including exceptions implying inability 
to insert records into error tables.  An exception handler for OTHERS should 
always be left to RAISE by default, or handled with RAISE_APPLICATION_ERROR, 
and should not assume that the handled exception will permit manipulation of 
tables.

-----Original Message-----
From: Chuck Boddy [mailto:Chuck.Boddy@xxxxxxxxxxxxxxx]
Sent: Wednesday, November 30, 2011 02:56 PM
To: tim@xxxxxxxxx, oracle-l@xxxxxxxxxxxxx
Subject: RE: raise_application_error and when others
Hi,
Yep..had it wrong in my email..but RIGHT in trigger.that is..

    raise_application_error(-20400, 'I tawt I taw a putty tat!'); is what I 
have in the trigger?
To troubleshoot, I remove the when others exception?and raise_application_error 
is hit?and execution stops?.add back the when others exception?and the record 
yet again is written to the error log?it  sure seems to be dropping down to the 
when others exception as well?NOT what I would expect

From: Tim Gorman [mailto:tim@xxxxxxxxx]
Sent: Wednesday, November 30, 2011 1:40 PM
To: Chuck Boddy; oracle-l@xxxxxxxxxxxxx
Subject: Re: raise_application_error and when others

Check your syntax for RAISE_APPLICATION_ERROR, your parameters are probably 
reversed.  Typical call to RAISE_APPLICATION_ERROR runs something like...

    raise_application_error(-20400, 'I tawt I taw a putty tat!');

instead of...

    raise_application_error('I tawt I taw a putty tat!', -20400);

Hope this helps...

-----Original Message-----
From: Chuck Boddy [mailto:Chuck.Boddy@xxxxxxxxxxxxxxx]
Sent: Wednesday, November 30, 2011 02:33 PM
To: tim@xxxxxxxxx, oracle-l@xxxxxxxxxxxxx
Subject: RE: raise_application_error and when others
That?s what I initially thought too.  So I checked the calling procedure, and 
what is logged to the error table is specific to the calling procedure.  What 
is logged to the error table in this case, is only specific to that trigger(it 
writes the object name(the trigger) at the time the exception is hit.

This does seem to be handled differently than what I would expect?

raise_application_error(?sqlerrm populated?,?sqlcode populateted?);(user 
defined)

drops down to When others exeception with user defined sqlerrm and sqlcode.

Am I right to expect that when others SHOULD NOT be hit?

Thanks,
Chuck

From: Tim Gorman [mailto:tim@xxxxxxxxx]
Sent: Wednesday, November 30, 2011 1:19 PM
To: Chuck Boddy; oracle-l@xxxxxxxxxxxxx
Subject: Re: raise_application_error and when others

It's probable that the trigger is raising the ORA-20400 exception as specified, 
and is working just fine, but then the *calling* procedure or code is handling 
the returned exception from the trigger to log to the error table.

-----Original Message-----
From: Chuck Boddy [mailto:Chuck.Boddy@xxxxxxxxxxxxxxx]
Sent: Wednesday, November 30, 2011 02:11 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: raise_application_error and when others

Hi, I have a trigger that under a condition, I call raise_application_error. I 
also have when others defined to write to an error table passing sqlerrm and 
sqlcode. My understanding is that when the condition is met and 
raise_application_error is called, execution ends in the subprogram. What 
"appears" to be happening, is that raise_application_error is handled THEN 
drops down to when others. Because the record that is written to my error table 
has the sqlerrm and sqlcode that is only populated in my user-defined 
raise_application_error handler(-20400). Is this true, that if you call 
raise_application_error AND have when others defined, the call to 
raise_application_error will then drop down to your when others exception? 
Thanks, Chuck -- //www.freelists.org/webpage/oracle-l





--
//www.freelists.org/webpage/oracle-l


Other related posts: