Home » SQL & PL/SQL » SQL & PL/SQL » handling transaction in procedures
handling transaction in procedures [message #39548] Tue, 23 July 2002 05:52 Go to next message
thirumaran
Messages: 4
Registered: June 2002
Junior Member
dear experts

when ever an exception raise the entire procedure transaction should be rollbacked.

example

create proc test ....

begin

insert ...
commit;

delete...
commit;

exception
rollback... ;

end procedure;

here the (2)commits should not happen when an exception raises.

thanks in advance
g.thirumaran
Re: handling transaction in procedures [message #39552 is a reply to message #39548] Tue, 23 July 2002 06:33 Go to previous messageGo to next message
Saga
Messages: 51
Registered: April 2002
Member

Try reading about Autonomous transaction.
Re: handling transaction in procedures [message #39559 is a reply to message #39548] Tue, 23 July 2002 08:27 Go to previous message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
You are really compromising your transaction control by including multiple commits in your procedure. You should really have only one commit that executes at the end of the procedure if no exceptions have been raised:

begin
  insert...;
  delete...;
  update...;
  commit;
exception
  when others then
    rollback;
end;
Previous Topic: Re: Scheduling of procedures
Next Topic: group by question
Goto Forum:
  


Current Time: Fri Mar 29 06:05:22 CDT 2024