Re: Reraising exceptions

I use a similar action, but I use savepoints. The code that traps the error issues a rollback to a savepoint. Logs the error, issues a new savepoint. Then, dependng on the error, the function reraises the exception or continues. The overall design issues a "commit" at the end of every logical unit of work (for batch type actions). The logical unit of work is defined to leave the database in a consistant state from the business perspective.

This does take some carefull design upfront, but it provides good error capture and tracking.

James

In article <7ed698$vgr$1_at_nnrp1.dejanews.com>,   Rich Goldkamp <rich_goldkamp_at_my-dejanews.com> 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. 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.
>
> Cheers,
> Rich
> --
> Rich Goldkamp, Consultant, OCP RJGoldka_at_dcss.com
> Digital Consulting & Software Services (504) 523-5005 x2736
>
> In article <370793A7.E3C2BC75_at_ix.netcom.com>,
> "Charles R. Conti" <crconti_at_ix.netcom.com> wrote:
> > What I am seeing by testing this is that the INSERT does not take
> > place. If this code is in a trigger, then you can't use a COMMIT. I am
> > using 7.1.* currently. It was recommended by an Oracle employeed that I
> > use an Oracle pipe. I will paste the text of his response below as
> > well.
> >
> > -- 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
>

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Thu Apr 08 1999 - 09:38:44 CDT