Error handling and procedures [message #1767] |
Tue, 28 May 2002 08:16 |
Kjell
Messages: 7 Registered: February 2002
|
Junior Member |
|
|
Hi
I have a question about error handling and procedures.
In my procedure I'm calling 5 other procedures. If one of these procedures fail, I want to do a rollback. In the procedures beeing called, a commit is performed in each of the procedures.
I was thinking about using "exception whent others then rollback", but this only rollbacks the last commit, doesn't it? How can I solve this? Can I have an exception for each procedure beeing called?
All the best
Kjell
|
|
|
Re: Error handling and procedures [message #1776 is a reply to message #1767] |
Tue, 28 May 2002 22:19 |
|
Maaher
Messages: 7065 Registered: December 2001
|
Senior Member |
|
|
A commit (or rollback) puts an end to your transaction, so once commited, it cannot be rolled back again (except with use of flashback queries in Oracle 9i, but that's a different story).
I suggest you only commit once the last procedure you call in your "main" has been completed successfully.
I often make use of functions instead of procedures. I let all these called functions return an error code, and in my exception handling I can treat the error properly.
e.g.:
you have something like:
begin
procedure_1; -- with a commit at the end
procedure_2; -- with a commit at the end
procedure_3; -- with a commit at the end
exception
when ....
end;
I suggest you do no transaction processing in your called procedures:
begin
procedure_1; -- without a commit at the end
procedure_2; -- without a commit at the end
procedure_3; -- without a commit at the end
COMMIT; -- everything successful;
exception
when ....
ROLLBACK;
end;
or convert the procedures to functions which return an error code, so you'll have more possibilities in treating the errors.
HTH,
MHE
|
|
|