Correct use of ROLLBACK [message #37317] |
Wed, 30 January 2002 14:32 |
ron
Messages: 50 Registered: July 1999
|
Member |
|
|
I have code something like this
==============================================
begin
delete tab1;
loop
insert into tab1(....) values(.....)
end loop;
exception
when others then
rollback; ( I can use rollback to savepoint, if I define savepoint after begin)
=======================================================
my problem is if I add commit after 'delete tab1',
then only it deletes records from tab1, and if any error occurs while insert, its not rolling back. meaning there 0(zero) records.
, if I dont use commit, it is not deleting records from tab, which I need insert goes perfectly.
-=============================================
Oracle gurus please help.
Thanks
commit;
|
|
|
Re: Correct use of ROLLBACK [message #37320 is a reply to message #37317] |
Wed, 30 January 2002 16:17 |
Todd Barry
Messages: 4819 Registered: August 2001
|
Senior Member |
|
|
Wouldn't just putting the commit after the loop accomplish what you want - that is, the delete and all the inserts have to all be successful, or else rollback the entire transaction?
begin
delete tab1;
loop...
insert into tab1(....) values(.....);
end loop;
commit;
exception
when others then
rollback;
end;
|
|
|