Re: RAISE_APPLICATION_ERROR & ROLLBACK

Comments embedded

"Gordon" <nospam_at_newsranger.com> wrote in message news:wPzJ6.4594$vg1.359247_at_www.newsranger.com...
> Hello,
>
> We just upgraded to Oracle 8i and have been told that
 RAISE_APPLICATION_ERROR no
> longer does an implicit rollback.

By whom?
raise_application_error (nor any raise) no longer does a full rollback, it stil performs a statement level rollback. We set to work upgrading all of our stored
> procedures by placing a ROLLBACK immediately before any
 RAISE_APPLICATION_ERROR
> where it was appropriate.

A better solution is to trap the error in your exception section, conduct a rollback and reraise the error.
No influence on current code

>
> Here is the problem. We have many stored procedures that are sometimes
 (but not
> always) called by a trigger. This is a problem since you can't issue a
 rollback
> from a procedure called by a trigger.
>

Why should you rollback. Just let the exception propagate to the outermost level, and it will be trapped by the outermost exception handler, ie result in a rollback

> I did some searching on MetaLink, etc., but have not found a solution to
 this
> problem. What have others done to deal with this problem?
>
>

Regards,

Sybrand Bakker, Oracle DBA Received on Mon May 07 2001 - 11:27:40 CDT