Re: Reraising exceptions

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 Received on Tue Apr 06 1999 - 09:38:33 CDT