stored procedure, cursor, and transaction
Posted in 2012
Forgot the version info: IDS 11.10 FC3 O/S RHEL 5 We have a scenario that I am not sure works by design or not, couldn't find any info on it. We have a stored procedure that acts as a wrapper for several other stored procedures. It uses foreach loop to process those several stored procedures on a record by record basis. One of the stored procedures also calls stored procedures that also calls stored procedures so the entire process is probably 4-5 layers deep. At a bottom layer, we started using a stored procedure that has a transaction (begin work/commit work) defined. That routine runs without error, but when it all returns back up to the wrapper stored procedure, when it hits the end of the loop, it drops out instead of moving to the next record. Is creating a transaction at a lower level supposed to kill a cursor at a higher level? If we are coding this wrong, is there a proper way to make this work? TIA, Randy