Re: RAISE_APPLICATION_ERROR & ROLLBACK

declare
user_raised_exception exception;
pragma exception_init(-<your error number>, user_raised_exception); begin
...
if <some_error_condition> then

   raise_application_error(-....)
end if;
exception
when user_raised_exception then

   rollback;
  raise;
end;

of course you could also use
when others then

   rollback;
   raise;

Hth,

Sybrand Bakker, Oracle DBA

"Gordon" <nospam_at_newsranger.com> wrote in message news:3RBJ6.4869$vg1.379058_at_www.newsranger.com...
> I'm afraid that I am confused.
>
> Can someone give an elementary example to demonstrate this?:
>
> >raise_application_error (nor any raise) no longer does a full rollback,
 it
> >stil performs a statement level rollback.
>
> I'm trying to write code that works one way in Oracle 8 and another way in
 8i.
>
> Thanks.
>
> In article <tfdj3rguco4v96_at_beta-news.demon.nl>, Sybrand Bakker says...
> >
> >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 - 15:50:44 CDT