Re: Rollback and Commit

did not look in detail at your code, but here's a quick bunch of suggestions:

1. use standard oracle pl/sql exception handling to indicate success or failure instead of returning 0 or 1 -- also realize that if you handle an exception in your pl/sql code, work done in that procedure up to the exception is NOT rolled back, it's only rolled back if you do not handle the exception
2. rollback in linear -- it will rollback all work performed since the prior rollback or commit
3. look into rollback to savepoint for more control or the transaction
4. should you check the return code? depends on what your functionality requires

read up on transaction processing in the oracle concepts manual and exception handling in the PL/SQL manual

• mcs

<anders_tung_at_hotmail.com> wrote in message news:da2e9ee1.0406070700.40d26356_at_posting.google.com...
| Hi,
|
| I have a procedure which will call 3 functions.
| First function will update a record.
| Second function will delete a record.
| Third function will insert a record.
|
| Each function will return a zero if successful, otherwise return 1.
|
| My questions are
| 1) If one or more of the function fail, and I will call a rollback in
| the procudure, would it rollback all 3 functions?
| 2) Or I should check the return code for each function before I call
| the next function?
|
| Feel free to tell me your suggestion!
|
| Thank you!
|
| Anders
|
|
|
|
| Procedure my_proc ()
| ret1 number;
| ret2 number;
| ret3 number;
| total := number;
| Begin
| ret1 := function_one();
| ret2 := function_two();
| ret3 := function_three();
| total := ret1 + ret2 +ret3;
| if total > 0 then
| rollback;
| else
| commit;
| end if;
| End my_proc;
|
| function_one()
| return number is
| ret_one number := 0;
| begin
| update dummy1 set dummy_col ='dummy';
| return ret_one;
| EXCEPTION
| WHEN OTHERS
| THEN
| ret_one:=1;
| RETURN ret_one;
| end function_one();
|
| function_two()
| return number is
| ret_two number := 0;
| begin
| delete from dummy2 where dummy_col ='dummy';
| return ret_two;
| EXCEPTION
| WHEN OTHERS
| THEN
| ret_two:=1;
| RETURN ret_two;
| end function_two();
|
| function_three()
| return number is
| ret_three number := 0;
| begin
| insert into dummy3 (dummy_col) values ('dummy');
| return ret_three;
| EXCEPTION
| WHEN OTHERS
| THEN
| ret_three:=1;
| RETURN ret_three;
| end function_three();
Received on Mon Jun 07 2004 - 10:14:08 CDT