| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Reraising exceptions
A copy of this was sent to Rich Goldkamp <rich_goldkamp_at_my-dejanews.com>
(if that email address didn't require changing)
On Tue, 06 Apr 1999 14:38:33 GMT, you wrote:
>I do what the original poster was asking about in production code all the
>time. If an error occurs, log it and raise the error to the next level.
what happens when no one catches your error? The log disappears.
>This
>gives me a nice trace of errors through my PL/SQL hierarchy. As long as a
>COMMIT occurs somewhere downstream of inserting into the error tables, this
>will work fine. If my code lies beneath a trigger, I commit after the
>triggering statment.
>
Then you have made rule "where ever my code goes, a commit must follow every error". Its very easy (unless you are writing all of the code) to not do that. Also, you are assuming that one can catch the error -- if you do this from a trigger and someone in a pro*c code code:
exec sql insert into t values ( 1 );
your 'commit after the triggering' statement won't work in general unless the insert was fired from plsql (only plsql can catch and ignore the exception). The trigger will catch the error, insert the error message, RAISE the exception again but since no one catches it, the error log is gone.
If the commit happens at any level but the 'highest' level, the transaction could be completely botched (half done, half not done). How do you know what procedure is the 'top level'? The top level procedures of today may become the subroutines of tomorrow -- and if they commit, you have a real ugly mess to unwind (gotta remember what routines commit, what ones don't).
Before Oracle8i, release 8.1, the bulletproof way to do this that does not rely on someone committing (committing) would be
In 8.1, you would use an autonomous transaction to do this easily without the 'do i commit or not' problem...
>Cheers,
>Rich
>--
>Rich Goldkamp, Consultant, OCP RJGoldka_at_dcss.com
>Digital Consulting & Software Services (504) 523-5005 x2736
>
[snip]
>> -- Chuck
>>
>> A copy of this was sent to "Charles R. Conti" <crconti_at_ix.netcom.com>
>> (if that email address didn't require changing)
>> On Fri, 02 Apr 1999 07:38:37 -0800, you wrote:
>>
>> >Will the insert take place in this piece of code
>> >
>>
>> yes but it will immediately be rolled back. In 8.0 and below, you
>> cannot do
>> what you are attempting. One possible way to implement it is to use
>> database
>> pipes to write the information to be inserted to another session that is
>> reading
>> that pipe. That other session can perform the insert and commit it
>> autonomously
>> of the transaction you are in.
>>
>> another possible solution would be to log the error messages to a trace
>> file on
>> the server using the UTL_FILE builtin package.
>>
>> In Oracle8i, release 8.1, there is a new feature called an autonomous
>> transactions that will allow you to do what you are trying from within
>> the
>> single session (no pipes needed). It might look like:
>>
>> create or replace procedure audit_err( p_ecode in number, p_emsg in
>> varchar2 )
>> as
>> pragma autonomous_transaction;
>> begin
>> insert into errors values ( p_ecode, p_emsg );
>> commit;
>> end;
>> /
>>
>> declare
>> ..
>> begin
>> ...
>> exception
>> when others then audit_err( sqlcode, sqlerrm );
>> end;
>> /
>>
>> >
>> >DECLARE
>> > err_num NUMBER;
>> > err_msg VARCHAR2(100);
>> >BEGIN
>> > ...
>> >EXCEPTION
>> > ...
>> > WHEN OTHERS THEN
>> > err_num := SQLCODE;
>> > err_msg := SUBSTR(SQLERRM, 1, 100);
>> > INSERT INTO errors VALUES (err_num, err_msg);
>> >
>> > RAISE; /* or RAISE_APPLICATION_ERROR */
>> >END;
>> >
>> >
>> >
>>
>> Thomas Kyte
>> tkyte_at_us.oracle.com
>> Oracle Service Industries
>> Reston, VA USA
>>
>> --
>> http://govt.us.oracle.com/ -- downloadable utilities
>>
>> ----------------------------------------------------------------------------
>>
>> Opinions are mine and do not necessarily reflect those of Oracle
>> Corporation
>>
>> Jonathan Gennick wrote:
>>
>> > Interesting question. I believe the INSERT should take
>> > place, but I also notice that you don't COMMIT. You do raise
>> > the exception to the next level. If the calling routine sees
>> > the error and does a rollback, then your insert will be
>> > undone.
>> >
>> > regards,
>> >
>> > Jonathan
>> >
>> > On Fri, 02 Apr 1999 07:38:37 -0800, "Charles R. Conti"
>> > <crconti_at_ix.netcom.com> wrote:
>> >
>> > >Will the insert take place in this piece of code
>> > >
>> > >
>> > >DECLARE
>> > > err_num NUMBER;
>> > > err_msg VARCHAR2(100);
>> > >BEGIN
>> > > ...
>> > >EXCEPTION
>> > > ...
>> > > WHEN OTHERS THEN
>> > > err_num := SQLCODE;
>> > > err_msg := SUBSTR(SQLERRM, 1, 100);
>> > > INSERT INTO errors VALUES (err_num, err_msg);
>> > >
>> > > RAISE; /* or RAISE_APPLICATION_ERROR */
>> > >END;
>> > >
>> > >
>> > >
>>
>>
>
>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA
--
http://govt.us.oracle.com/ -- downloadable utilities
 |
 |