Re: Reraising exceptions

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;
> >
> >
> >
Received on Sun Apr 04 1999 - 11:30:32 CDT