rollback to savepoint in a package [message #22155] |
Thu, 26 September 2002 00:19 |
Dani
Messages: 15 Registered: October 2000
|
Junior Member |
|
|
Here my question: Is it possible to have a savepoint in one the procedures of a package and to have a rollback to this savepoint in another of these different procedures (some "create tables" have to be rollbackd")
I've tried it with:
savepoint test;
and later with
rollback to savepoint test;
but it didn't work..
Thx so far
|
|
|
Re: rollback to savepoint in a package [message #22165 is a reply to message #22155] |
Thu, 26 September 2002 10:37 |
Skumar
Messages: 17 Registered: April 2002
|
Junior Member |
|
|
There is a feature in 8i called AUTONOMOUS TRANSACTION where in if you declare a procedure say as PRAGMA AUTONOMOUS_TRANSACTION you can control the transaction of this whole process in the calling procedure .i.e whether you wish to commit or rollback in the calling procedure.
say package a has procedure a1
procedure a2
a1 calls a2 and you set a2 pragma AUTONOMOUS_TRANSACTION after you retunr to a1 you can either to a commit or a rollback of the whole transaction.
|
|
|
Re: rollback to savepoint in a package [message #22167 is a reply to message #22165] |
Thu, 26 September 2002 10:50 |
Todd Barry
Messages: 4819 Registered: August 2001
|
Senior Member |
|
|
You should use the DBMS_TRANSACTION package to manage your savepoints if they span procedures.
create or replace package body foo
is
procedure a
is
begin
dbms_transaction.savepoint('sp_1');
insert into ...;
b;
end;
procedure b
is
begin
... more processing
commit;
exception
when others then
dbms_transaction.rollback_savepoint('sp_1');
end;
end;
|
|
|