Re: RAISE_APPLICATION_ERROR & ROLLBACK

From: fumi <fumi_at_tpts5.seed.net.tw>
Date: Tue, 8 May 2001 23:10:44 +0800
Message-ID: <9d9332$2ed$4@news.seed.net.tw>

"Gordon" <nospam_at_newsranger.com> ¼¶¼g©ó¶l¥ó 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. We set to work upgrading all of our stored
> procedures by placing a ROLLBACK immediately before any RAISE_APPLICATION_ERROR
> where it was appropriate.

Who told you?

> 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.
>
> 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?

The behavior of RAISE_APPLICATION_ERROR is not changed. When an error occurs in a statement, Oracle do a statement level rollback.

If your PL/SQL block does not contain exception handling, then the PL/SQL statement is rollbacked. If your PL/SQL block contains a exception part, you must rollback explicitly if you wish.

You can try the following?

    delete from emp;
    begin

      delete from dept;
      raise_application_error(-20001, 'blabla');

    end;

and then

    delete from emp;
    begin

      delete from dept;
      raise_application_error(-20001, 'blabla');
    exception
      when others then
        null;

    end;

Then, check what is in v$lock.
In the first case, you would see 1 TM lock record, it shows that the PL/SQL statement is rollbacked. In the second case, you would see 2 TM lock redord, it means nothing is rollbacked. Received on Tue May 08 2001 - 10:10:44 CDT