Re: Q: "Autorollback" after raise_application

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Fri, 29 Oct 1999 08:17:32 -0400
Message-ID: <iY0ZOAlaUSseb9FKJ=LjWJJTiHKd@4ax.com>

A copy of this was sent to Gombos Bertalan <bgombos_at_freemail.c3.hu> (if that email address didn't require changing) On Fri, 29 Oct 1999 14:04:36 +0200, you wrote:

>Hi,
>
>Thomas Kyte wrote:
>>
>> A copy of this was sent to Gombos Bertalan <bgombos_at_freemail.c3.hu>
>> (if that email address didn't require changing)
>> On Thu, 28 Oct 1999 16:48:37 +0200, you wrote:
>>
>> >Hi all,
>> >
>> > When I call a stored procedure from OCI, Oracle generates a savepoint to
>> >begin of the procedure. If I issue a raise_application_error() and this
>> >error reaches the caller environment (Power Builder), Oracle gives a
>> >rollback to the former savepoint.
>> > Is it possible to prevent Oracle from rollbacking?
>> >
>> >Bye:
>>
>> catch the exception in the stored procedure and handle it there. If the
>> exception is allowed to propagate to the top level -- the work is (correctly)
>> rolled back. If you catch and handle the exception (eg: Ignoring it is one way
>> of 'handleing' it), then the client will not rollback to savepoint since the
>> procedure was 'successful'.
>My question is misunderstood. I do want to keep the updates performed
>within the scope of the procedure by which the exception is raised. I want
>to make it the caller's (PowerBuilder) responsibility to decide, whether to
>commit or rollback. I do not want to use any other communication method
>between PL/SQL and PowerBuilder, because the concerned stored procedures are
>also invoked by other system components (out of my control).
>
>Bye:

Sorry -- it doesn't work that way. All SQL statements executed by a client are atomic -- they either succeed totally or not at all. If the error is allowed to propagate back to the client, the statement has failed and all work performed on behalf of the statement is rolled back. If the error does not propagate back to the client, all work performed by the statement is not rolled back.

If you modify how you call the procedure, perhaps you can achieve what you want. Consider this example. The first call I make is the way you are doing it now. You just execute the procedure. This one results in all changed being rolled back since the error propagated back to the client (sqlplus). The second one shows how the client can control this themselves (by catching the error in the database when executing the procedure and deciding whether to rollback to some savepoint or not.

tkyte_at_8.0> create table t ( y int );

Table created.

tkyte_at_8.0>
tkyte_at_8.0> create or replace procedure p   2 as
  3 x varchar2(1);
  4 begin

  5          insert into t values (1);
  6          x := 'Hello World';
  7          insert into t values (2);

8 end;
9 /

Procedure created.

tkyte_at_8.0>
tkyte_at_8.0> begin
  2 p;
  3 end;
  4 /
begin
*
ERROR at line 1:

ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "TKYTE.P", line 6
ORA-06512: at line 2

tkyte_at_8.0> select * from t
2 /

no rows selected

The above shows the behaviour you are experiencing now....

Below is the way to code it to get your desired effect:

The client will declare 2 host variables to get the SQLcode and Error message

tkyte_at_8.0> variable err_code number
tkyte_at_8.0> variable err_msg varchar2(512)

Instead of just executing "P", you will execute the following anonymous block. It will catch and ignore all errors, just sets the error code and error message for us. Note is sets a savepoint YOU control.

tkyte_at_8.0> begin

  2          :err_code := 0;
  3          :err_msg  := null;
  4          savepoint MySavepoint;
  5          p;
  6  exception
  7          when others then
  8                  :err_code := sqlcode;
  9                  :err_msg  := sqlerrm;

10 end;
11 /

PL/SQL procedure successfully completed.

Now, when this executes -- the first insert is in the table (but the second is not since the assignment to X always raises an error)

tkyte_at_8.0> select * from t
2 /

The client inspects the error code and message....

tkyte_at_8.0> print err_code

ERR_CODE

-6502

tkyte_at_8.0> print err_msg

ERR_MSG

ORA-06502: PL/SQL: numeric or value error

and decides to rollback the changes...

tkyte_at_8.0> rollback to MySavePoint;

Rollback complete.

And they are gone:

tkyte_at_8.0> select * from t
2 /

no rows selected

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Oct 29 1999 - 07:17:32 CDT

Re: Q: "Autorollback" after raise_application_error()