Home » SQL & PL/SQL » SQL & PL/SQL » Error handling and procedures
Error handling and procedures [message #1767] Tue, 28 May 2002 08:16 Go to next message
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 Go to previous message
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
Previous Topic: triggers
Next Topic: Urgent...Listener problem with PowerBuilder connecting to ORACLE8i. PLEASE HELP
Goto Forum:
  


Current Time: Fri Mar 29 08:21:15 CDT 2024