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:
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 */
ROLLBACK; /* rollback the update to table 1 */
when others then
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.