Q: "Autorollback" after raise_application_error()
Gombos Bertalan
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:
--
.---, G o m b o s B e r t a l a n
: :
:---'-. ,---. ,--. ,-.-. rendszerfejleszto, Oracle mernok
: : :-- : ; mailto:bgo...@freemail.c3.hu
,' '---' '---' ' `-'
-- : ------------------------- Az elet megis egy habostorta? --- '10^2-1
Thomas Kyte
(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'.
--
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 tk...@us.oracle.com
Oracle Service Industries Reston, VA USA
Opinions are mine and do not necessarily reflect those of Oracle Corporation
Rudi Demeulenaere
> 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?
You can do something like
(...)
exception
when your_exception then
commit;
raise_application_error(your_exception);
end;
(...)
I'm not pronouncing myself if this makes any sense for your logical
environment though.
Another possibility is to execute a separate transaction (supposing you want
*something* done but not what you did so far). Facility only available from 8i
if I'm not mistaking. Maybe this complies also to your wishes:
(...)
exception
when your_exception then
rollback;
do_what_you_want_to_do; /* e.g. logging in some error table */
commit;
raise_application_error(your_exception);
end;
(...)
Regards
</rudi>
Gombos Bertalan
Thomas Kyte wrote:
>
> A copy of this was sent to Gombos Bertalan <bgo...@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).
Thomas Kyte
(if that email address didn't require changing)
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@8.0> create table t ( y int );
Table created.
tkyte@8.0>
tkyte@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@8.0>
tkyte@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@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@8.0> variable err_code number
tkyte@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@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@8.0> select * from t
2 /
Y
----------
1
The client inspects the error code and message....
tkyte@8.0> print err_code
ERR_CODE
----------
-6502
tkyte@8.0> print err_msg
ERR_MSG
---------------------------------------------
ORA-06502: PL/SQL: numeric or value error
and decides to rollback the changes...
tkyte@8.0> rollback to MySavePoint;
Rollback complete.
And they are gone:
tkyte@8.0> select * from t
2 /
no rows selected