Suppose program A is calling program B for some data. Both programs use different tables on different TABLESPACEs.
Program B is doing some database updates and hence requires COMMIT and/or ROLLBACK for its own tables.
But whenever program B is issuing COMMIT/ROLLBACK all the cursors from calling program ‘A’ are also getting closed.
In the cursor definition using WITH HOLD can be helpful when using COMMIT command. WITH HOLD option keeps the cursors open even after COMMIT command.
But the ROLLBACK command closes all the OPEN cursors.
Can anybody suggest some solutions on how both programs can be kept independent with respect to COMMIT and ROLLBACK?
Also feel free to contact me, if you need any clarification on the issue.