I guess I'm having a bit of a logic problem. My procedure should update table1, and then call another proc to update table2. However, if the update to table2 is unsuccessful for any reason (the proc sends back a success code), I need to rollback the update to table1. The problem is, my rollback is not working; the update is happening anyway. Here is the gist of the code:
begin
UPDATE TABLE1
set ....... where....;
success_flag := 'N';
update_table2(arg 1, arg 1, success_flag);
if success_flag = 'Y' THEN
COMMIT; /* commit the update to table 1 */
else
ROLLBACK; /* rollback the update to table 1 */
end if;
EXCEPTION
when others then
etc. etc.
ROLLBACK;
end;
I could update table2 first, and if succesful update table1, but then I Have the same problem if the update to table1 is unsuccessful (less likely), I would need to rollback the update to table2.
Any help is appreciated.
