Rollback and Commit

[anders_tung@hotmail.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( );

[Mark C. Stock]

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@ho tmail.comwrote in message
news:da2e9ee1.0 406070700.40d26 356@posting.goo gle.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( );